Working with arrays in VBA

Question

I have a problem with arrays in VBA, where the same would be trivial (almost) in any other language:

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
    Set myArray = getArray() 'FAILS with "Cannot assign to array error"'
End Sub

I think maybe I need to determine the length of the array in advance, or ReDim is a dynamic array. But I do not know the length of the returned array ahead of time, and I would like to avoid a double function:

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 look like this:

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
}

Answer 1

When assigning arrays of custom objects to vba, you need to pass them as options . I have included a full working sample.

Class module named MyType:

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

Code in the 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

doSomething , " ". doSomething .

Test sub, doSomething . doSomething .

Answer 2

, ​​:

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


Dim myArr() as MyType
MyFunc myArr

ReDim .

Answer 3

, set Set myArray = getArray()

---

:

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

Answer 4

, VBA. MSDN:

[Y] ou , . [., ] , .

Therefore, you just need to modify the existing code by removing Setfrom the assignment operator:

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