Working with arrays in VBA

I'm having a bit of a problem working with arrays in VBA, where the same would be trivial in (almost) any other language:

Public Function getArray() As MyType()
    'do a lot of work which returns an array开发者_如何学Go of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
End Function

Public Sub doSomething()
    Dim myArray() As MyType
    Set myArray = getArray() 'FAILS with "Cannot assign to array error"'
End Sub

I think it might be that I need to define the length of the array in advance, or ReDim a dynamic array. But I don't know the length of the returned array ahead of time, and I'd like to avoid calling the function twice:

Public Sub doSomething()
    Dim myArray(0 To UBound(getArray()) As MyType  'not entirely sure if this would work, but it involves calling getArray twice which I'd like to avoid
    Set myArray = getArray()
End Sub

In C# or Java the equivalent would be:

public MyType[] getArray(){
    //do some work and return an array of an unknown length
}

public void doSomething(){
    MyType[] myArray;
    myArray = getArray();  //one line and I don't need to define the length of array beforehand
}

When assigning arrays of custom objects in vba you need to pass them around as variants I've included a full working sample.

Class Module named MyType:

Public Once As Integer
Public Twice As Integer
Public Thrice As Integer

Code in standard module:

Public Function getArray() As MyType()
Dim i As Integer, arr() As MyType
    'do a lot of work which returns an array of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
    For i = 0 To Int(Rnd() * 6) + 1
        ReDim Preserve arr(i)
        Set arr(i) = New MyType
        arr(i).Once = i
        arr(i).Twice = i * 2
        arr(i).Thrice = i * 3
    Next i
    getArray = arr
    MsgBox "Long process complete"
End Function

Public Sub doSomething()
Static myArray() As MyType
Dim i As Integer
    If UBound(myArray) = -1 Then
        myArray = getArray()
    End If
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(i).Once & vbTab & _
                    myArray(i).Twice & vbTab & _
                    myArray(i).Thrice
    Next i
End Sub

Public Sub Test()
Dim i As Integer
    For i = 1 To 3
        Debug.Print "Run Number " & i & vbCrLf & String(10, "-")
        doSomething
        Debug.Print
    Next i
End Sub

The first time you run doSomething an array of random length will be generated and you will see a message box that says "Long process complete". Subsequent calls to doSomething will re-use the array created the first time.

If you copy this code and just run the Test sub it will call doSomething three times. You will see the message box once and the output of doSomething in the immediate window three times.

Well, you could pass the array as a reference to the function like this:

Public Sub MyFunc(ByRef arr() As MyType)
  ...
End Sub


Dim myArr() as MyType
MyFunc myArr

Inside the function you can ReDim your array as wanted.

It is indeed possible to return an array from a function in VBA. According to MSDN:

[Y]ou can also call a procedure that returns an array and assign that to another array. [ . . . ] Note that to return an array from a procedure, you simply assign the array to the name of the procedure.

So you just need to modify your existing code by removing Set from the assignment statement:

Public Function getArray() As MyType()
    'do a lot of work which returns an array of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
End Function

Public Sub doSomething()
    Dim myArray() As MyType
    myArray = getArray
End Sub

I think you just need to get rid of the set in Set myArray = getArray()

This behaves properly:

Option Explicit

Public Function getArray() As Integer()
    Dim test(1 To 5) As Integer
    test(1) = 2
    test(2) = 4
    getArray = test
End Function

Public Sub doSomething()
    Dim myArray() As Integer
    myArray = getArray()
    Debug.Print (myArray(2))
End Sub