Consistently returning the entire array in a VBA UDF?

Question

I've written the following simple VBA function to calculate linear progression between two amounts for some amount of intervals and return the progression as an array:

Public Function applyLinear(startAmount As Double, stopAmount As Double, Intervals As Integer)
    Dim diff As Double
    Dim resultArr() As Double
    Dim counter As Integer    
    diff = stopAmount - startAmount
    ReDim resultArr(Intervals, 0)
    resultArr(0, 0) = startAmount
    For counter = 1 To (Intervals)
        resultArr(counter, 0) = resultArr(counter - 1, 0) + diff / Intervals
    Next ' counter
    counter = 0
    applyLinear = resultArr
End Function

Unfortunately, what actually gets outputted to the worksheet depends entirely on how many cells the above formula is called from. (This means that it becomes laughably easy for a user to apply the function incorrectly!)

How can I ensure that the entire array is outputted to the worksheet under all circumstances?

Edit: Or rather, more specifically, how can I get VBA to output the entire array when calling the above UDF from a single cell?

Edit 2: Some more clarification. The function does work as intended if "intervals+1" rows are used when calling it. A problem arises however, if (for example) the user accidentally calls the function from 4 rows when intervals is > 4. In this case only the first four elements of resultArr are outputted to the worksheet, this is an issue because it doesn't represent the full linear progression.

Did you try setting your variables to zero at the beginning of your udf? — Tom K., Aug 06 '15 at 12:51
@ Tom. I don't quite follow? The function does work as intended, but only if the user calls the function from "intervals+1" cells. — user3615872, Aug 06 '15 at 13:03
Perhaps add a check to see whether the range that the UDF has been entered in = "intervals + 1". Then instead of inputting a value, make the UDF read something like "ERROR - MUST ENTER IN " & "Interval +1" " CELLS INSTEAD OF " & #OF_CELLS_ENTERED — Grade 'Eh' Bacon, Aug 06 '15 at 13:12
@ Grade 'Eh' Bacon - That's definitely an option, at least the user won't be misled! Ideally though I'm holding out for some way to get it to work with one 'input cell'. I suspect there is some kind of mystical VBA workaround to do this, since Bloomberg seems to have managed it in their Excel add-in. — user3615872, Aug 06 '15 at 13:23
Take a look at my suggestion below; I've tried to solve both ways - by changing the value based on number of cells entered, or simply showing an error message when the wrong # of cells are entered. If I've misinterpreted how many cells need to be entered (Intervals? Or Intervals+1?), then that should be an easy fix. — Grade 'Eh' Bacon, Aug 06 '15 at 13:54

score 0 · Accepted Answer · edited May 08 '18 at 21:49

While looking into how to do what I suggested in the comment above, I found some interesting info on the Application.Caller function, which allows you to access the properties of the cell a UDF is entered in. See here: Excel cell from which a Function is called

Here's one way to do this, based on a little bit of further guidance from cpearson [http://www.cpearson.com/excel/returningarraysfromvba.aspx]

Public Function applyLinear(startAmount As Double, stopAmount As Double, Intervals As Integer)

Dim diff As Double
Dim resultArr() As Double
Dim counter As Integer
ReDim resultArr(Intervals, 0)

If Application.Caller.Rows.Count <> Intervals + 1 Then

    applyLinear = "ERROR - MUST ENTER THIS FORMULA IN " & Intervals + 1 & " CELLS"

Else

    diff = stopAmount - startAmount
    resultArr(0, 0) = startAmount

    For counter = 1 To (Intervals)
        resultArr(counter, 0) = resultArr(counter - 1, 0) + diff / Intervals
        TotalityCheck = TotalityCheck + resultArr(counter, 0) 'builds in by the end of the loop the total array value
    Next ' counter


    applyLinear = resultArr
End If

End Function

Although, now that you have the use of the Application.Caller function, you could perhaps do this a little more simply, by removing the need for the user to enter the number of Intervals - instead, define the number of intervals as the application.caller.rows - 1, as follows:

Public Function applyLinear(startAmount As Double, stopAmount As Double)

Dim diff As Double
Dim resultArr() As Double
Dim counter As Integer
Dim Intervals As Integer
ReDim resultArr(Intervals, 0)

Intervals = Application.Caller.Rows.Count - 1

    diff = stopAmount - startAmount
    resultArr(0, 0) = startAmount

    For counter = 1 To (Intervals)
        resultArr(counter, 0) = resultArr(counter - 1, 0) + diff / Intervals
        TotalityCheck = TotalityCheck + resultArr(counter, 0) 'builds in by the end of the loop the total array value
    Next ' counter


    applyLinear = resultArr


End Function

Thanks, I'll probably go use this version without intervals as a parameter. Just had to shift the ReDim down and it works like a charm. — user3615872, Aug 07 '15 at 09:54

Consistently returning the entire array in a VBA UDF?

1 Answers1