arrays - 在 VBA 中使用数组

Question

我在 VBA 中使用数组时遇到了一些问题，而在(几乎)任何其他语言中，同样的问题都是微不足道的:

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

我想可能是我需要提前定义数组的长度，或者ReDim一个动态数组。但我事先不知道返回数组的长度，并且我想避免调用该函数两次:

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

在 C# 或 Java 中，等效内容是:

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

在 vba 中分配 自定义对象数组时，您需要将它们作为变体传递 我已经提供了完整的工作示例。

名为 MyType 的类模块:

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

标准模块中的代码:

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 子程序，它将调用 doSomething 三次。您将看到一次消息框，并在立即窗口中看到三次 doSomething 的输出。