CODEWITHSUNDEEP
excelvba
user429400
user429400

Reputation: 3325

Excel VBA pass array of arrays to a function

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?

For example, if arr1 and arr2 contains 24 items each (they always contain the same amount of items) I want the resulting array to be a 2 dimentional array of size 24 x 2 and I want the resulting 2 x 24 table to be written to the spreadsheet, for example to range A1:B24

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) 'compiler error
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

I'm getting a compiler error on: arrSize = UBound(arr, 2), since the compiler doesn't know that arr is a 2 dim array... Thanks, Li

Upvotes: 1

Views: 972

Answers (1)

user2140173
user2140173

Reputation:

The created CellsDetails is actually a one dimensional array. Ubound(arr) does the job.

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

Maybe an illustration will help you understand

enter image description here

So you have a multidimensional obj1 to objX

You stick those in a one dimensional array that stores them as objects

Upvotes: 3

Related Questions