Return array in Spreadsheet Function

Question

The code below returns an array. I would like to use it in a spread sheet as an excel formula to return the array. However, when I do, it only returns the first value to the cell. Is there anyway to return the array in a range of equal size as the array?

Function LoadNumbers(Low As Long, High As Long) As Long()
'''''''''''''''''''''''''''''''''''''''
' Returns an array of Longs, containing
' the numbers from Low to High. The
' number of elements in the returned
' array will vary depending on the
' values of Low and High.
''''''''''''''''''''''''''''''''''''''''

'''''''''''''''''''''''''''''''''''''''''
' Declare ResultArray as a dynamic array
' to be resized based on the values of
' Low and High.
'''''''''''''''''''''''''''''''''''''''''
Dim ResultArray() As Long
Dim Ndx As Long
Dim Val As Long
'''''''''''''''''''''''''''''''''''''''''
' Ensure Low <= High
'''''''''''''''''''''''''''''''''''''''''
If Low > High Then
    Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''
' Resize the array
'''''''''''''''''''''''''''''''''''''''''
ReDim ResultArray(1 To (High - Low + 1))
''''''''''''''''''''''''''''''''''''''''
' Fill the array with values.
''''''''''''''''''''''''''''''''''''''''
Val = Low
For Ndx = LBound(ResultArray) To UBound(ResultArray)
    ResultArray(Ndx) = Val
    Val = Val + 1
Next Ndx
''''''''''''''''''''''''''''''''''''''''
' Return the array.
''''''''''''''''''''''''''''''''''''''''
LoadNumbers = ResultArray()

End Function

You need to preselect the cells and enter the formula as an array formula (by pressing CTRL+SHIFT+ENTER). — assylias
– assylias, Commented Aug 28, 2013 at 17:14

Chel · Accepted Answer · 2013-08-28 19:21:40Z

A UDF can certainly return an array, and your function works fine. Just select, e.g., range B2:D2, put =LoadNumbers(1, 3) into the formula bar, and hit Ctrl+Shift+Enter to tell Excel it's an array function.

Now, you can't have the UDF auto-resize the range it was called from according to its inputs (at least not without some ugly Application.OnTime hack), but you don't need to do that anyways. Just put the function in a 1000-cell-wide range to begin with, and have the UDF fill in the unused space with blank cells, like this:

Function LoadNumbers(ByVal Low As Long, ByVal High As Long) As Variant()
    Dim ResultArray() As Variant
    Dim Ndx As Long
    Dim Val As Long
    Dim SourceCols As Long

    SourceCols = Application.Caller.Columns.Count

    If Low > High Then
        Exit Function
    End If
    If High - Low + 1 > SourceCols Then High = Low + SourceCols - 1

    ReDim ResultArray(1 To SourceCols)

    Val = Low
    For Ndx = LBound(ResultArray) To (High - Low + 1)
        ResultArray(Ndx) = Val
        Val = Val + 1
    Next Ndx
    For Ndx = (High - Low + 2) To UBound(ResultArray)
        ResultArray(Ndx) = vbNullString
    Next Ndx
    LoadNumbers = ResultArray()
End Function

tigeravatar · Accepted Answer · 2013-08-28 17:19:39Z

2

A worksheet formula can only output a value to the same cell the formula was written in. As it stands, the code already produces an array. If you want the values to be shown as you copy the formula down, use a formula like this (in any cell you want) and then copy down:

=INDEX(LoadNumbers(1,10),ROWS($A$1:$A1))

If you copy down too far, you'll get a #REF! error because the LoadNumbers ran out of numbers.

answered Aug 28, 2013 at 17:19

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

2 Comments

cdelsola Over a year ago

What about if it was a 2 dimensional array? Tried $B$1:$B2 but I got the same results

tigeravatar Over a year ago

Based on the code you provided, the result array is always a 1-dimensional array, so I'm not sure why this is relevant. Regardless, Index can handle a 2-dimensional array as well, you just need to include the 3rd argument, like so =INDEX(LoadNumbers(1,10),ROWS($A$1:$A1),COLUMNS($A$1:A$1))

DaleRobichaud · Accepted Answer · 2023-02-19 00:10:49Z

I was looking for something similar (create a function in a macro, take inputs from a sheet, output an multi-dim array), and I hope my use-case below helps to answer. If not, my apologies:

Use-case: Create and apply well-known numerical option valuation function, and output the stock price, valuation, and payoff as a 3-D array (3 columns) of #rows as specified in the function (20 in this case, as NAS variable). The code is copied - but the idea is to get the output into the sheet....

a) These inputs are static in the sheet. b) I called the macro formula 'optval' via the 'fx' function list from an output cell I wanted to start in, and put the starting inputs into the formula. b) The output will propagate to the cells as per the code using the NAS bound of 20 rows. Trivial, but it works. c) you can automate the execution of this and output to the sheet - but anyhow, I hope this way helps anyway.

The module function is below (copied!) - but just put the starter inputs in i.e. Vol=.2, Int rate = 0.05, Strike=120, Exp = 1, P type = C (or P), US?= N, i.e. european, , NAS=20 (or however many rows you want to see, and it affects the granularity of the numerical method)

Function optval(Vol, Intrate, Strike, Expn, Ptype, Etype, NAS)

ReDim S(0 To NAS) As Double
ReDim VOld(0 To NAS) As Double
ReDim VNew(0 To NAS) As Double
ReDim dummy(0 To NAS, 1 To 3)

dS = 2 * Strike / NAS
dt = 0.9 / NAS / NAS / Vol / Vol
NTS = Int(Expn / dt) + 1
dt = Expn / NTS

q = 1
If Ptype = "P" Then q = -1
For i = 0 To NAS
    S(i) = i * dS
    VOld(i) = Application.Max(q * (S(i) - Strike), 0)
    dummy(i, 1) = S(i)
    dummy(i, 2) = VOld(i) 'Payoff
Next i

For k = 1 To NTS
    For i = 1 To NAS - 1
        Delta = (VOld(i + 1) - VOld(i - 1)) / 2 / dS
        Gamma = (VOld(i + 1) - 2 * VOld(i) + VOld(i - 1)) / dS / dS
        Theta = -0.5 * Vol * Vol * S(i) * S(i) * Gamma - _
            Intrate * S(i) * Delta + Intrate * VOld(i)
        VNew(i) = VOld(i) - Theta * dt 'BSE
    Next i

    VNew(0) = VOld(0) * (1 - Intrate * dt) 'S=0
    VNew(NAS) = 2 * VNew(NAS - 1) - VNew(NAS - 2) 'Infty
    
    For i = 0 To NAS
        VOld(i) = VNew(i)
    Next i
    
    If Etype = "Y" Then
        For i = 0 To NAS
            VOld(i) = Application.Max(VOld(i), dummy(i, 2))
        Next i
    End If
    
Next k

For i = 0 To NAS
    dummy(i, 3) = VOld(i)
Next i

optval = dummy
End Function

The question is asking how to return an array in a spreadsheet function. While your function may do that it doesn't address the original question of how that might be done in general.

Bad Wolf · Accepted Answer · 2013-08-28 18:02:48Z

0

=INDEX(LoadNumbers(1,10),ROWS($A$1:$A1),COLUMNS($B$1,B$1))

edited Aug 28, 2013 at 18:02

Bad Wolf

8,3495 gold badges36 silver badges44 bronze badges

answered Aug 28, 2013 at 17:40

cdelsola

4372 gold badges8 silver badges17 bronze badges

Comments

Aly Abdelaal · Accepted Answer · 2023-09-08 23:21:57Z

-1

you can add an argument as index of the output something like the key in aggregate function

Function LoadNumbers(Low As Long, High As Long, index As Integer) As Long()


Dim ResultArray() As Long
Dim Ndx As Long
Dim Val As Long

If Low > High Then
    Exit Function
End If

ReDim ResultArray(1 To (High - Low + 1))

Val = Low
For Ndx = LBound(ResultArray) To UBound(ResultArray)
    ResultArray(Ndx) = Val
    Val = Val + 1
Next Ndx

LoadNumbers = ResultArray(index)

End Function

answered Sep 8, 2023 at 23:21

Aly Abdelaal

3202 silver badges9 bronze badges

Collectives™ on Stack Overflow

Return array in Spreadsheet Function

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related