How to use the name for an array by using a variable?

[Rob]

Hello,

Somehow my previous answer and msg was lost, that's why I post this again.

I want to write some universal code to process arrays. The number of arrays
can change per project. So I wrote the next piece of code.

First I count the needed arrays and put the outcome in a variable.
Using a counter and a loop I want to repeat an action on each of the arrays
by creating
the name of the array and then process it. But substituting the variable to
use
it as the name of the array doesn't work. The question is how I can get the
value in the variable to work as a NAME for the array.

Sub WorkWithArray()

Dim Arr1 As Variant
Dim Arr2 As Variant
Dim strArrName
Dim strArrNr
Dim intArrCount

' Fill two arrays
Arr1 = Array("A", "B")
Arr2 = Array("C", "D")
' Fill counters and create the first name for the array
strArrNr = 1
intArrCount = 2
strArrName = "Arr" & Nr ' Nr = 1, so strArrName = "Arr1"

' Repeat doing something with each of the arrays
For intArrNr = 1 To intArrCount
Msgbox strArrName(1) ' But THIS doesn't work. How do I get
' the CONTENT in the
variable work as the Array-designation
' so that it says or
acts like
' Msgbox Arr1(1)

strArrNr = strArrNr + 1
strArrName = "Arr" & Nr
Next

End sub



Rob

 

[Gary''s Student]

See if Alan Beban has something you can use or adapt:

http://home.pacbell.net/beban/

 

[Chip Pearson]

I don't think you can do it directly. The closest you could get would be to
store your arrays in a Class and use CallByName to get the property that
returns the appropriate array. E.g.,

'[ In Class1 ]
Public Property Get Arr1()
Arr1 = Array("a", "b")
End Property

Public Property Get Arr2()
Arr2 = Array("c", "d")
End Property

'[ In Module1 ]
Sub WorkWithArray()
Dim Arr As Variant
Dim C As Class1
Dim N As Long
Dim J As Long
Dim S As String
Set C = New Class1

For N = 1 To 2
Arr = CallByName(C, "Arr" & CStr(N), VbGet)
For J = LBound(Arr) To UBound(Arr)
Debug.Print Arr(J)
Next J
Next N
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

Thinking about it further, you could store your arrays in a Collection
object and reference the Item in the Collection by its Key:

Sub AAA()
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim Coll As Collection
Dim N As Long
Dim J As Long
Dim Arr As Variant
Dim ArrayName As String
Set Coll = New Collection

Arr1 = Array("a", "b", "c")
Arr2 = Array("d", "e", "f")

Coll.Add Item:=Arr1, Key:="Arr1"
Coll.Add Item:=Arr2, Key:="Arr2"

For N = 1 To 2
ArrayName = "Arr" & CStr(N)
Arr = Coll(ArrayName)
For J = LBound(Arr) To UBound(Arr)
Debug.Print Arr(J)
Next J
Next N

End Sub




--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

I don't think you can do it directly. The closest you could get would be to
store your arrays in a Class and use CallByName to get the property that
returns the appropriate array. E.g.,

'[ In Class1 ]
Public Property Get Arr1()
Arr1 = Array("a", "b")
End Property

Public Property Get Arr2()
Arr2 = Array("c", "d")
End Property

'[ In Module1 ]
Sub WorkWithArray()
Dim Arr As Variant
Dim C As Class1
Dim N As Long
Dim J As Long
Dim S As String
Set C = New Class1

For N = 1 To 2
Arr = CallByName(C, "Arr" & CStr(N), VbGet)
For J = LBound(Arr) To UBound(Arr)
Debug.Print Arr(J)
Next J
Next N
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Hello,

Somehow my previous answer and msg was lost, that's why I post this
again.

I want to write some universal code to process arrays. The number of
arrays
can change per project. So I wrote the next piece of code.

First I count the needed arrays and put the outcome in a variable.
Using a counter and a loop I want to repeat an action on each of the
arrays by creating
the name of the array and then process it. But substituting the variable
to use
it as the name of the array doesn't work. The question is how I can get
the
value in the variable to work as a NAME for the array.

Sub WorkWithArray()

Dim Arr1 As Variant
Dim Arr2 As Variant
Dim strArrName
Dim strArrNr
Dim intArrCount

' Fill two arrays
Arr1 = Array("A", "B")
Arr2 = Array("C", "D")
' Fill counters and create the first name for the array
strArrNr = 1
intArrCount = 2
strArrName = "Arr" & Nr ' Nr = 1, so strArrName = "Arr1"

' Repeat doing something with each of the arrays
For intArrNr = 1 To intArrCount
Msgbox strArrName(1) ' But THIS doesn't work. How do I get
' the CONTENT in the
variable work as the Array-designation
' so that it says or
acts like
' Msgbox Arr1(1)

strArrNr = strArrNr + 1
strArrName = "Arr" & Nr
Next

End sub



Rob

 

[Rick Rothstein \(MVP - VB\)]

Somehow my previous answer and msg was lost, that's why I post this again.

Your original post, and the answer I provided to it at the time, were not
lost. Here is a link to the Google archives for it...

http://groups.google.com/group/micr...5/54460582ed4ea3de?lnk=st&q=#54460582ed4ea3de

Rick

 

[Rick Rothstein \(MVP - VB\)]

See inline comment....

Or, to make it independent of 0-Base/1-Base:

Sub Rothstein()
Dim X As Long
Dim Arr1 As Long
Dim Arr2 As Long
Dim VarArray As Variant
' We use VarArray to hold two elements,
' each of which is an array.
VarArray = Array(Array("A", "B"), Array("C", "D", "E", "F"))
' Note the syntax to address each array... a double pair of
' parentheses... the first set address which array member of
' VarArray we want, the second set addresses the element
' of the member array we want.
Arr1 = LBound(VarArray)
Arr2 = UBound(VarArray)

Good thought about making the assignments automatic; however, I think the
above line should more properly be this...

Arr2 = Arr1 + 1

The ArrX variables are indexes for the VarArray itself... Arr1 points to the
first stored array and Arr2 points to the second stored array (I did that to
make the "structure" similar to the OP's intial naming convention). While
your code works for the given example, the OP might find it more difficult
to extrapolate it if he chose to put 3 or more arrays into varArray. I think
he would be able to guess that a 3rd stored array would have Arr3=Arr2+1
more easily using my suggestion.

Rick

 

[Alan Beban]

Your original post, and the answer I provided to it at the time, were
not lost. Here is a link to the Google archives for it...

http://groups.google.com/group/micr...5/54460582ed4ea3de?lnk=st&q=#54460582ed4ea3de


Rick

Or, to make it independent of 0-Base/1-Base:

Sub Rothstein()
Dim X As Long
Dim Arr1 As Long
Dim Arr2 As Long
Dim VarArray As Variant
' We use VarArray to hold two elements,
' each of which is an array.
VarArray = Array(Array("A", "B"), Array("C", "D", "E", "F"))
' Note the syntax to address each array... a double pair of
' parentheses... the first set address which array member of
' VarArray we want, the second set addresses the element
' of the member array we want.
Arr1 = LBound(VarArray)
Arr2 = UBound(VarArray)
Debug.Print "******** Arr1 member elements ********"
For X = LBound(VarArray(Arr1)) To UBound(VarArray(Arr1))
Debug.Print VarArray(Arr1)(X)
Next
Debug.Print "******** Arr2 member elements ********"
For X = LBound(VarArray(Arr2)) To UBound(VarArray(Arr2))
Debug.Print VarArray(Arr2)(X)
Next
Debug.Print "******** DONE ********"
End Sub

Alan Beban