Correct way to return an array from VBA function

Question

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?

You have to confirm the second one with Ctrl+Shift+Enter. Exactly as you would have to confirm =SUM(A1:A3+1). — BrakNicku
– BrakNicku, Commented Mar 3, 2020 at 11:27
Thanks! That worked. I am not an avid Excel user. But I find it really strange that =SUM(A1:A3+1) needs to be confirmed with Ctrl+Shift+Enter while =SUM(A1:A3) and =SUM({1,2,3}+1) do not. Is there some rational behind this? — Tohiko
– Tohiko, Commented Mar 3, 2020 at 12:52
This is too complicated for a comment or even an answer, so I will only try to give you some hints about it. Excel often allows you to use implicit intersection. Most basic example: enter =A:A in any other column and it will get you value from the same row and A column. Same result with many built in functions. SUM operates on ranges so in most cases it does not exhibit this behavior - unless there is some other operation on the input range involved. — BrakNicku
– BrakNicku, Commented Mar 3, 2020 at 13:56
You can try it: enter =SUM(A1:A3+1) in rows 1-4 and compare the results. If you pass constant array instead of range - implicit intersection is not possible (array does not have its address), so you get what you expect without CSE. To make things even more complicated: the exact behavior depends on a function. If you replace SUM with SUMPRODUCT in the above example you will see that it behaves as if it was entered with CSE. — BrakNicku
– BrakNicku, Commented Mar 3, 2020 at 14:03

John Alexiou · Accepted Answer · 2020-03-03 19:39:19Z

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

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:

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.

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

Collectives™ on Stack Overflow

Correct way to return an array from VBA function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related