Multidimensional array in VBA

S

Stevenn

Active Member
Joined
Feb 8, 2012
Messages
259
How can I make a multidimensional array like this:

[ [ "Name 1", [ "A", "B", "C" ] ], [ "Name 2", [ "D", "E", "F", "G", "H" ] ], [ "Name 3", [ "I" ] ] ]

It's a total of three groups which each has a name. I don't know how to create and populate such arrays in VBA and how to finally print it.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Stevenn said:
How can I make a multidimensional array like this:

[ [ "Name 1", [ "A", "B", "C" ] ], [ "Name 2", [ "D", "E", "F", "G", "H" ] ], [ "Name 3", [ "I" ] ] ]

It's a total of three groups which each has a name. I don't know how to create and populate such arrays in VBA and how to finally print it.
Click to expand...


Code: 
Public Type myRecord 'creating your Dimensions like so
   Name As String
   Types As String


End Type




Sub populate_Name()


Dim records() As myRecord


ReDim records(0 To 10)


records(0).Name = "Name1" ' store it like this, you can loop in from cells in a sheet etc here
records(0).Types = "A, B, C, whatever" ' and like this, this could be a value from a cell


Sheets(1).Cells(1, 1).Value = records(0).Name   ' This will set cell A1 in sheet 1 equal to "Name1" which is stored in that record/aray position


End Sub

This is what I do, might be easier ways to do it, but this is easy to understand and works for me :)
 
Upvote 0
You might want deeper layers here, so have a look at this, you can continue on like this for 63 dimensions I think... (its alot)


Code: 
Option Explicit

Public Type TypesOfTypes
   A As String
   B As String
   C As String   'you can go ON and ON here
End Type
 
Public Type myRecord 'creating your Dimensions like so
   Name As String
   Types As TypesOfTypes


End Type




Sub populate_Name()


Dim records() As myRecord


ReDim records(0 To 10)
records(0).Name = "Name1" ' store it like this, you can loop in from cells in a sheet etc here
records(0).Types.A = "A storage" ' and like this, this could be a value from a cell


Sheets(1).Cells(1, 1).Value = records(0).Name
Sheets(1).Cells(1, 2).Value = records(0).Types.A
End Sub
 
Upvote 0
Thank you for your answer. The solution would be fine if the number of stored values was fixed, but I have to make it more dynamically as there sometimes can be many values and other times only a few or none.
 
Upvote 0
Hi

I can't understand well what you need, where the data comes from and in what format, what you need to do with it, etc.

Just from the syntax point of view, there would be many possible answers, this is one using a jagged array:

Code: 
Option Explicit
Option Base 1

Sub Test()
Dim vArr(1 To 3, 1 To 2) As Variant

vArr(1, 1) = "Name 1": vArr(1, 2) = Array("A", "B", "C")
vArr(2, 1) = "Name 2": vArr(2, 2) = Array("D", "E", "F", "G", "H")
vArr(3, 1) = "Name 3": vArr(3, 2) = Array("I")

MsgBox vArr(1, 1)
MsgBox vArr(2, 2)(4)

End Sub

Hope this helps
 
Upvote 0
I see, you could just make the storage big, and add an IF statement, to redim the "second" dimension? You would want the second dimension to contain different "storages", "A" through "I"? but if only "I" exist in example:[ "Name 3", [ "I" ] ], in for example records(5), then records 5 would give you only the information "I"?
 
Upvote 0
pgc01 said:
Hi

I can't understand well what you need, where the data comes from and in what format, what you need to do with it, etc.

Just from the syntax point of view, there would be many possible answers, this is one using a jagged array:

Code: 
Option Explicit
Option Base 1

Sub Test()
Dim vArr(1 To 3, 1 To 2) As Variant

vArr(1, 1) = "Name 1": vArr(1, 2) = Array("A", "B", "C")
vArr(2, 1) = "Name 2": vArr(2, 2) = Array("D", "E", "F", "G", "H")
vArr(3, 1) = "Name 3": vArr(3, 2) = Array("I")

MsgBox vArr(1, 1)
MsgBox vArr(2, 2)(4)

End Sub

Hope this helps
Click to expand...

I think this is exactly what I want.

How can I loop through the array? I've tried

Code: 
Public Sub Test()    Dim vElement1 As Variant
    Dim vElement2 As Variant
    Dim vArr(1 To 3, 1 To 2) As Variant


    vArr(1, 1) = "Group 1"
    vArr(1, 2) = Array("1234", "2345", "3456")


    vArr(2, 1) = "Group 2"
    vArr(2, 2) = Array("4567", "8765", "3214", "4123", "5773")


    vArr(3, 1) = "Group 3"
    vArr(3, 2) = Array("8884")
    
    For Each vElement1 In vArr
        Debug.Print vElement1
        For Each vElement2 In vElement1
            Debug.Print vElement2
        Next vElement2
    Next vElement1
End Sub
 
Last edited:
Upvote 0
Loop information into, or out of the array?


Either way, if you look at the locals window you can see the Array adresses :)

from your array

vArr(2,2)(3) = "4123"

Use that info to create a smart loop/for statement
 
Last edited:
Upvote 0
I made an example of looping "out" the information.

Code: 
Public Sub Test()


    Dim vArr(1 To 3, 1 To 2) As Variant




    vArr(1, 1) = "Group 1"
    vArr(1, 2) = Array("1234", "2345", "3456")




    vArr(2, 1) = "Group 2"
    vArr(2, 2) = Array("4567", "8765", "3214", "4123", "5773")




    vArr(3, 1) = "Group 3"
    vArr(3, 2) = Array("8884")
    
Sheets(1).Select
    
    For i = 1 To 5
    
    Cells(i, 1).Value = vArr(i, 1)


    For j = 0 To 5
        On Error Resume Next
       Cells(i, j + 2) = vArr(i, 2)(j)
        If j = 5 Then GoTo nextvArr
    Next j
    
nextvArr:
 
    
    Next i
    On Error GoTo 0
 
End Sub

You could make your ranges for i and j more to your choice. Though I'm not sure how to get that count from an array. :S
 
Last edited:
Upvote 0
Stevenn said:
I think this is exactly what I want.

How can I loop through the array? I've tried

Code: 
Public Sub Test()    Dim vElement1 As Variant
    Dim vElement2 As Variant
    Dim vArr(1 To 3, 1 To 2) As Variant


    vArr(1, 1) = "Group 1"
    vArr(1, 2) = Array("1234", "2345", "3456")


    vArr(2, 1) = "Group 2"
    vArr(2, 2) = Array("4567", "8765", "3214", "4123", "5773")


    vArr(3, 1) = "Group 3"
    vArr(3, 2) = Array("8884")
    
    For Each vElement1 In vArr
        Debug.Print vElement1
        For Each vElement2 In vElement1
            Debug.Print vElement2
        Next vElement2
    Next vElement1
End Sub
Click to expand...

Hi

There are several options.

You can use your information about the structure of the jagged array, like
- an undefined number of rows but always 2 columns
- the first element in each row is a string, the second an array with an undefined number of elements

This allows you to make a smart print, like:

Code: 
Option Explicit
Option Base 1

Public Sub Test1()
Dim i As Long, k As Long
Dim vArr(1 To 3, 1 To 2) As Variant


    vArr(1, 1) = "Group 1"
    vArr(1, 2) = Array("1234", "2345", "3456")


    vArr(2, 1) = "Group 2"
    vArr(2, 2) = Array("4567", "8765", "3214", "4123", "5773")


    vArr(3, 1) = "Group 3"
    vArr(3, 2) = Array("8884")
    
    For i = 1 To UBound(vArr)
 
       Debug.Print vArr(i, 1)
        
        For k = 1 To UBound(vArr(i, 2))
            Debug.Print vArr(i, 2)(k)
        Next k
 
   Next i

End Sub


Does this solve your problem?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Userform: Populating Cells based on the information selected in the Combobox
Replies
9
Views
202
iantech
I
O
  • Solved
VBA - Save sheets whose names are in table to single pdf
2
Replies
10
Views
321
John_w
J
O
  • Question
Running through a Config and pulling out the relevant information using keywords into smaller sheets
Replies
0
Views
135
oitbc
O
D
  • Solved
Moving lines of a 2D array to a second 2D array based on a condition
Replies
3
Views
277
Peter_SSs
Peter_SSs
G
  • Question
VBA or Power Query or combo to unpivot my table
Replies
1
Views
98
mse330
M

Forum statistics

Threads
1,214,944
Messages
6,122,391
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top