Excel VBA: Custom functions that return a range of data

Question

I want to write a User Defined Function in Excel VBA that can be used as an Excel formula, but instead of returning a value to the cell that called it, it returns a range of values to a range of other cells. This is common in many database add-ins, such as Bloomberg, CapIQ, Morningstar. I have tried different approaches but could not find out a way to accomplish it.

For example, if I put this code in cell A1, it will return 25 to cell A1.

Function AreaOfSquare(length)
  AreaOfSquare = length * length
End Function

What I want, is something like this:

    A     B      C      D      E   
1   *   Eqt1   Eqt1   Eqt2   Eqt2  
2      Price    vol  Price    vol

In cell A1, I put =DBLOOKUP(B1:F1,B2:E2, "One Year") and it produces results like this:

    A     B      C      D      E   
1   *   Eqt1   Eqt1   Eqt2   Eqt2  
2      Price    vol  Price    vol 
3 07/14   13    100     12    200
4 06/14   14    120     13    210
5 05/14   15    140     14    220

Here are a list of approaches I have tried:

Obtain the raw data from the database Excel add-in in the form of formula. For example, I can type =Rawdata(B1:E1, B2:E2) and obtain the raw data. The problem is that I don't know how to call this function in VBA so that I can manipulate these data for the output, as this function is not built-in but comes with a database plugin. Alternatively, I can first obtain the raw data using the formula and then manipulate those later; the problem is that this will require two-step manipulation as I will need to type in the formula twice.
Obtain the data from Java and import the data somehow. This is troublesome as I will need to configure the java application on my boss's computer environment.

Any suggestions?

A function can only return a value to the cell in which it is located. However, you can return an array of values, and enter the UDF as you would an array function, to return the array to the cells in which the function exists. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 29, 2014 at 1:03
There is a workaround to what @RonRosenfeld mentions (I can't find the link but it is here on SO and fairly recent -- last month or two maybe), but as a general rule: do not use UDFs to manipulate the worksheet object or its members, other than the calling cell. My suggestion would be to do this the proper way with a subroutine, and for simplicity/ease of use, you can assign a custom button to the menu bar/ribbon, etc. to invoke it. — David Zemens
– David Zemens, Commented Jul 29, 2014 at 1:23
You can start from this one, just as an example of UDF limitation work-around. Try to implement your logic in UDF based on that. — omegastripes
– omegastripes, Commented Jul 29, 2014 at 14:58

Nathaniel Ford · Accepted Answer · 2016-01-14 23:12:05Z

1

This is how I work around it: first, find the cell that this function is calling. Then you can move it around.

In the following example it is adding up the value of a list of cell until it reaches the bucksize required.

Function bars(bucksize, columnsgap)

Application.Volatile

'this function is to count backward number of bars need to fill up the bucket
'bucksize is the volume of the bucket
'columnsgap= how many rows the volume column away from where this function use

BB = 0

j = 0

'Find the location where this function call

xx = Application.Caller.Column

yy = Application.Caller.Row

x1 = xx - columnsgap

'Add up the volume of bars until it excess or equal to the bucksize

Do

rowshift = j

y1 = yy - rowshift

BB = BB + Cells(y1, x1).Value

j = j + 1

Loop While BB < bucksize

bars = j

End Function

edited Jan 14, 2016 at 23:12

Nathaniel Ford

21.3k20 gold badges98 silver badges112 bronze badges

answered Jan 14, 2016 at 22:49

Sam

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Mark Main · Accepted Answer · 2024-05-18 19:32:17Z

There is a safe and somewhat self-documenting way to return an array of multiple values from a VBA function, and place them into a contiguous series of cells using the CONCATENATE() function coupled with a vba function that returns an array of values. Here is an example:

Excel cell D1: =CONCATENATE(return3(A1:C1))

VBA code example:

Public Function return3(rng As Range) As Variant
    'Example code returns multiple cell range values.
    
    'This code receives input range (rng) of 3 cells from Excel worksheet,
    'adds 1000, 2000, and 3000 from each rng cell's value respectively,
    'then returns the 3 newly calculated values, which are placed into consecutive
    'cells using a Call like this: =CONCATENATE(return3(A1:C3))
    
    'The returned values are clearly designated as calculated because of Excel's
    'CONCATENATE() function protects the output cells from accidental overright use
    'with a #SPILL! error if any of them are overlaid with a value,and it outlines the
    'cells with a blue box when any of the cells are selected.
    
    Dim returnVal() As Long
    ReDim returnVal(1 To rng.Count) As Long
    returnVal(1) = rng(1, 1) + 1000
    returnVal(2) = rng(1, 2) + 2000
    returnVal(3) = rng(1, 3) + 3000
    return3 = returnVal()
End Function

Collectives™ on Stack Overflow

Excel VBA: Custom functions that return a range of data

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related