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, Jul 29 '14 at 01: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, Jul 29 '14 at 01:23
You can start from [this one](http://stackoverflow.com/questions/24220347/unable-to-hide-row-excel-2003-from-function-invoked-from-formula/24222666#24222666), just as an example of UDF limitation work-around. Try to implement your logic in UDF based on that. — omegastripes, Jul 29 '14 at 14:58

score 1 · Answer 1 · edited Jan 14 '16 at 23:12

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

Excel VBA: Custom functions that return a range of data

1 Answers1