Correct way to return an array from VBA function

user_4007564 0
Viewed 824

My school uses Excel 2016 (pre-{=array} era) and so I am using VBA to define some missing function that I need. One function that I tried implementing in VBA is a SEQUENCE-replacement, like so

Function MYSEQ(n As Integer, m As Integer) As Variant
    ReDim seq(m - n) As Variant

    For i = LBound(seq) To UBound(seq)
        seq(i) = i + n
    Next i

    MYSEQ = seq
End Function

This works well, in that if I do in a cell =SUM(MYSEQ(1,3)) I correctly get 6. However, strangely, the return value does not play well with other excel operators. For example, if I do =SUM(MYSEQ(1,3)+1), I get 2 instead of the expected 9. Note that constant arrays play nicely with other operators, i.e., =SUM({1;2;3}+1) outputs 9, correctly.

Is there a way to fix this so that my returned arrays are treated like constant arrays?

vba excel arrays
1 Answers

You return a Variant() and enter the UDF as an array function (with CTRL+SHIFT+ENTER)

Example of a function that doubles the value in an array

scr

Public Function ScaleValues(ByRef r As Range, ByVal factor As Double) As Variant()
    Dim n As Long, m As Long, i As Long, j As Long
    n = r.Rows.Count:   m = r.Columns.Count

    Dim vals() As Variant
    vals = r.Value

    For i = 1 To n
        For j = 1 To m
            vals(i, j) = factor * vals(i, j)
        Next j
    Next i

    ScaleValues = vals
End Function

Specifically a function to fill cells in a sequence would be:

scr2

Public Function MySeq(ByVal start_value As Long, ByVal end_value As Long) As Variant()
    Dim n As Long, i As Long
    n = end_value - start_value + 1

    Dim vals() As Variant
    ReDim vals(1 To n, 1 To 1)

    For i = 1 To n
        vals(i, 1) = start_value + (i - 1)
    Next i

    MySeq = vals

End Function

This plays well as you can test. Enter =SUM(MySeq(1,24)) in a cell and you get 300 which is the correct answer. If you want to do linear algebra, such as scaling or adding arrays, then you have to separate the operation out column by column for each step.

example

Note that you cannot store a whole array in a single cell. Believe me I have tried a myriad ways (like setting the formula to ={1,2,3,4} for example). Even if this succeeds, there is no way to extract values out in any standard way to be used in SUM(), TRANSPOSE() or MMULT().

For posterity, there is a version of the sequence function that handles non-integer values and non unit stride (step)

Public Function MySeq2(ByVal start_value As Double, ByVal end_value As Double, Optional stride As Double = 1) As Variant()
    Dim n As Long, i As Long
    n = (end_value - start_value + stride) / stride

    Dim vals() As Variant
    ReDim vals(1 To n, 1 To 1)

    For i = 1 To n
        vals(i, 1) = start_value + (i - 1) * stride
    Next i

    MySeq2 = vals

End Function
user_3803840
Related