Excel VBA pass array of arrays to a function

Question

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

score 3 · Accepted Answer · 2013-11-05 16:33:51Z

3

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

edited Nov 5, 2013 at 16:33

answered Nov 5, 2013 at 15:58

user2140173

Sign up to request clarification or add additional context in comments.

5 Comments

user429400 Over a year ago

thanks, but can you please explain why is the resulting array a one-dim array? isn't a table of 2 x UBound(dict1.Items)?

user429400 Over a year ago

it's just if arr1 and arr2 contain 24 items each (they always contain the same number of items) I want a table of 2*24 to be written to the spreadsheet, for example to range A1:B24

user429400 Over a year ago

Got it, thanks :) so in my case I actually needed to change: arrSize = UBound(arr, 2) to arrSize = UBound(arr(1))

Blackhawk Over a year ago

@mehow What prog do you use to create the images?

user2140173 Over a year ago

@Blackhawk couldn't guess? it's Paint :)

Collectives™ on Stack Overflow

Excel VBA pass array of arrays to a function

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related