无法分配给数组，自定义函数

Question

我有一个创建和返回数组的函数：

Function test() 
Dim resultArray(1 To 3, 1 To 2) As Variant
Set resultArray(1, 1) = ThisWorkbook.Worksheets("setup").Range("A1:A1000")
Set resultArray(2, 1) = ThisWorkbook.Worksheets("setup").Range("B1:B1000")
Set resultArray(3, 1) = ThisWorkbook.Worksheets("setup").Range("C1:C1000")
Set resultArray(1, 2) = ThisWorkbook.Worksheets("setup").Range("D1:D1000")
Set resultArray(2, 2) = ThisWorkbook.Worksheets("setup").Range("E1:E1000")
Set resultArray(3, 2) = ThisWorkbook.Worksheets("setup").Range("F1:F1000")

test=resultArray
End Function
Sub testTestFunction()
Dim storedRanges() As Variant 'works fine
'Dim storedRanges(1 To 3, 1 To 2) As Variant 'error on the next line
storedRanges=test()  
MsgBox ("DONE")
End Sub

我尝试初始化方法Dim storedRanges() As Variant和Dim storedRanges(1 To 3, 1 To 2) As Variant，只有前者有效。 为什么会这样？

Answer 1

将要填充的数组作为参数传递给过程ByRef可能符合您的需求：

Sub Test()
Dim storedRanges(1 To 3, 1 To 2) As Object
PutRanges storedRanges
End Sub
Sub PutRanges(q() As Object)
With ThisWorkbook.Worksheets("setup")
Set q(1, 1) = .Range("A1:A1000")
Set q(2, 1) = .Range("B1:B1000")
Set q(3, 1) = .Range("C1:C1000")
Set q(1, 2) = .Range("D1:D1000")
Set q(2, 2) = .Range("E1:E1000")
Set q(3, 2) = .Range("F1:F1000")
End With
End Sub

Answer 2

我使用了一个动态数组，而不是一个静态分配的数组：

Function test() As Variant()
Dim resultArray() As Variant
ReDim resultArray(1 To 3, 1 To 2)
Set resultArray(1, 1) = ThisWorkbook.Worksheets("setup").Range("A1:A1000")
Set resultArray(2, 1) = ThisWorkbook.Worksheets("setup").Range("B1:B1000")
Set resultArray(3, 1) = ThisWorkbook.Worksheets("setup").Range("C1:C1000")
Set resultArray(1, 2) = ThisWorkbook.Worksheets("setup").Range("D1:D1000")
Set resultArray(2, 2) = ThisWorkbook.Worksheets("setup").Range("E1:E1000")
Set resultArray(3, 2) = ThisWorkbook.Worksheets("setup").Range("F1:F1000")

test = resultArray
End Function
Sub testTestFunction()
Dim storedRanges() As Variant 'works fine
'Dim storedRanges(1 To 3, 1 To 2) As Variant 'error on the next line
storedRanges = test()
MsgBox ("DONE")
End Sub

结果是您所期望的。