VBA: Array of ArrayNames
 

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?
--
We can do no great things; only small things with great love

Array of ArrayNames
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"ewize1" schrieb im Newsbeitrag
...
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?
--
We can do no great things; only small things with great love

Array of ArrayNames
 

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

--
Regards,
Tom Ogilvy



"ewize1" wrote in message
...
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?
--
We can do no great things; only small things with great love

Array of ArrayNames
 

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






"Frank Kabel" wrote in message
...
Hi
why not use a multi-dimensional array. Something like
Dim aMulit(1 to NumOfArray, 1 to 20)

--
Regards
Frank Kabel
Frankfurt, Germany

"ewize1" schrieb im Newsbeitrag
...
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?
--
We can do no great things; only small things with great love