Excel VBA pass array of arrays to a function

Posted by user429400 on Stack Overflow See other posts from Stack Overflow or by user429400
Published on 2013-11-05T15:50:24Z Indexed on 2013/11/05 15:53 UTC
Read the original article Hit count: 266

Filed under:

I have one function that creates an array of arrays, and one function that should get the resulting array and write it to the spreadsheet. I don't find the syntax which will allow me to pass the array of arrays to the second function... Could you please help?

Here is my code: The function that creates the array of arrays:

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

the function that writes it to the spreadsheet:

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)
Range(Cells(row, col), Cells(row + arrSize , col + 2)) = Application.Transpose(arr)

End Sub

The code that calls the functions:

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

Thanks, Li

© Stack Overflow or respective owner

Related posts about excel-vba