在 VBA 中使用数组

Working with arrays in VBA

提问by Iain Sproat

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

我在使用 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

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:

我想可能是我需要提前定义数组的长度，或者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

In C# or Java the equivalent would be:

在 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
}

采纳答案by mwolfe02

When assigning arrays of custom objectsin vba you need to pass them around as variantsI've included a full working sample.

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

Class Module named MyType:

名为 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 doSomethingan array of random length will be generated and you will see a message box that says "Long process complete". Subsequent calls to doSomethingwill re-use the array created the first time.

第一次运行时doSomething会生成一个随机长度的数组，您将看到一个消息框，上面写着“Long process complete”。后续调用doSomething将重用第一次创建的数组。

If you copy this code and just run the Testsub it will call doSomethingthree times. You will see the message box once and the output of doSomethingin the immediate window three times.

如果您复制此代码并运行Test子程序，它将调用doSomething3 次。您将doSomething在即时窗口中看到一次消息框和三次输出。

回答by dwo

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 ReDimyour array as wanted.

在函数内部，您可以ReDim根据需要设置数组。

回答by sheepez

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

我认为你只需要去掉setin SetmyArray = 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

回答by Cody Gray

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

确实可以从 VBA 中的函数返回一个数组。根据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.

[Y]您还可以调用返回数组并将其分配给另一个数组的过程。[ . . . ] 请注意，要从过程返回数组，只需将数组分配给过程的名称即可。

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

因此，您只需要通过Set从赋值语句中删除来修改现有代码：

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