Excel VBA将数组数组传递给函数

用户429400

我有一个函数创建一个数组数组，一个函数应该获取结果数组并将其写入电子表格。我找不到使我可以将数组的数组传递给第二个函数的语法...您能帮忙吗？

例如，如果arr1和arr2每个包含24个项目（它们始终包含相同数量的项目），我希望结果数组为尺寸为24 x 2的2维数组，并且我希望将结果2 x 24表写入其中电子表格，例如范围A1：B24

这是我的代码：

创建数组数组的函数：

Function GetCellDetails(dict1 As Dictionary, dict2 As Dictionary)   As Variant

Dim arr1, arr2
arr1 = dict1.Items
arr2 = dict2.Items

GetCellDetails = Array(arr1, arr2)

End Function

将其写入电子表格的函数：

Sub WriteCellDataToMemory(arr As Variant, day As Integer, cellId As Integer, nCells As Integer)
row = CellIdToMemRow(cellId, nCells)
col = DayToMemCol(day)

arrSize = UBound(arr, 2) 'compiler error
Range(Cells(row, col), Cells(row + arrSize , col + 2)) = Application.Transpose(arr)

End Sub

调用函数的代码：

Dim CellDetails
CellDetails = GetCellDetails(dict1, dict2)
WriteCellDataToMemory CellDetails, day, cellId, nCells

我在上收到编译器错误：arrSize = UBound(arr, 2)，因为编译器不知道arr是2暗数组...谢谢，李

用户名

创建CellsDetails的实际上是一维数组。Ubound(arr)做这份工作。

Sub Main()

    Cells.ClearContents

    Dim d1 As New Dictionary
    Dim d2 As New Dictionary

    d1.Add 1, "1"
    d1.Add 2, "2"
    d2.Add 3, "3"
    d2.Add 4, "4"

    Dim CellDetails
    CellDetails = GetCellDetails(d1, d2)

    WriteCellDataToMemory CellDetails

End Sub

Function GetCellDetails(dict1 As Dictionary, dict2 As Dictionary) As Variant

Dim arr1, arr2
arr1 = dict1.Items
arr2 = dict2.Items

GetCellDetails = Array(arr1, arr2)

End Function

Sub WriteCellDataToMemory(arr As Variant)

    Dim arrSize As Long
    arrSize = UBound(arr)
    Range(Cells(1, 1), Cells(1 + arrSize, arrSize+1)) = Application.Transpose(arr)

End Sub

也许插图会帮助您理解

在此处输入图片说明