VBA: Array of ArrayNames

[ewize1]

Hi,

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]

Hi
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)
Next
Next

 

[Alex J]

eWise,
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
works)

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