数组已传递给子过程，但返回的维度大小错误

Question

我对下面的代码有问题。感谢你的帮助。

我创建了一个子("GetListItems"(，它接收一个数组作为参数。该子使用excel文件第一张表中一组非连续范围的数据填充数组。Sub按预期执行，并且在为其指定日期后，子中的数组具有2个维度，第二个维度中的列数正确。

然而，当我通过引用将数组传递给这个子时，返回的数组只有1个维度。我一整天都在为此而挣扎，不明白原因。感谢你的帮助。

Sub GetListItems(ByRef arrArray() As Variant)
Dim WS As Worksheet
Dim LastRow As Integer
Dim SelectedCols As String
' Initialize our variable and get the last row in the database
Set WS = Worksheets("Database")
LastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
' select ranges for copying
SelectedCols = "A1:A" & LastRow & ", C1:C" & LastRow & _
", F1:F" & LastRow & ", H1:H" & LastRow & _
", V1:V" & LastRow & ", W1:W" & LastRow & _
", X1:X" & LastRow & ", Z1:Z" & LastRow
'Copy and paste data from defined range
WS.Range(SelectedCols).Copy
Set WS = Worksheets.Add(, Sheets(Sheets.Count))
With WS
.Visible = xlSheetHidden
.Paste WS.Range("A1")
End With
' Initialize array
arrArray = WS.Range("A1").CurrentRegion
' Delet the hidden sheet
With Application
.CutCopyMode = False
.DisplayAlerts = False
WS.Delete
.DisplayAlerts = True
End With
End Sub

==============================================

Sub test()
Dim MyArray() As Variant
Dim Dimension As Integer, Temp As Integer
Application.Run "GetListItems", MyArray
On Error GoTo Err
Do While True
Dimension = Dimension + 1
Temp = UBound(MyArray, Dimension)
Loop
Err:
MsgBox "MyArray variable has " & Dimension & " dimensions!", vbInformation And vbOKOnly

End Sub

Answer 1

原因是Application.Run总是将变量处理为ByVal。

你必须这样调用你的子：GetListItems MyArray-然后ByRef就会工作。

添加这个答案后，我发现这个SO线程非常值得一读：变量未更新通过ByRef