VBA: Array of ArrayNames




Let's say I have a finite list arrays of varies sizes, but the size of each
array is unknow during declaration. I have another array which stores the
name of each array declared. How can I use a loop to assess each element in
each array? Line 3 of the code doesn't work.

Const NumOfArray = 3
Dim Array1() As String
Dim Array2() As String
Dim Array3() As String
Dim ArrayList(NumOfArray) As String

1: For j = 1 to NumOfArray
2: For i = 1 to Ubound(Array(ArrayList(j)))
3: column = WorksheetFunction.Match(Array(ArrayList(j))(i), Rows(1), 0)
4: Next i
5: Next j

How can I solve it?



Frank Kabel

why not use a multi-dimensional array. Something like
Dim aMulit(1 to NumOfArray, 1 to 20)

Tom Ogilvy

Arraylist holds an array of strings (the array names). These can not be
converted into variables during execution.

You can assign the arrays to the Arraylist
Dim Array1() As String
Dim Array2() As String
Dim Array3() As String
Dim NumOfArray as Long
NumOfArray = 3
Dim ArrayList(1 to NumOfArray) As Variant
'code to initialize and populate the 3 arrays

ArrayList(1) = Array1
ArrayList(2) = Array2
ArrayList(3) = Array3

for i = 1 to NumOfArray
for j = lbound(Arraylist(i)) to Ubound(ArrayList(i))
debug.print i, j, ArrayList(i)(j)



Alex J

Frank has the simplest approach, but you would of course need to verify if
elements were blank since the arrays are of different dimension.

I would use another approach - collections:
(Although this might be more applicable to a bigger problem, it stilll

Dim xcArrays As New Collection
Dim Ax As Variant

xcArrays.Add Array1
xcArrays.Add Array2
xcArrays.Add Array3

For x = 1 To xcArrays.Count
Ax = xcArrays(x)
For i = LBound(Ax) To UBound(Ax)
MsgBox Ax(i)
Next i
Next x
Set xcArrays = Nothing

Alex J

Note: If you were to use twodimensional arrays for your Array1, Array2,
Array3, then add an extra loop such as:
For j = Lbound(Ax,2) to Ubound(Ax(2)
For i = LBound(Ax,1) To UBound(Ax,1)
MsgBox Ax(i)
Next i
Next j

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question