Replacing the contents of a variable as the name of an array

[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 count them and put the figure in a variable.
Using a counter I want to repeat an action on each of the arrays by creating
the name of the array and 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

 

[Ian]

I think I can see your problem.

You are using strArrName = "Arr" & Nr but Nr is of null value. Maybe you
meant strArrName = "Arr" & strArrNr

Another possible issue is that strArrNr appears to be a string, but you are
trying to increment it by 1 with each loop (strArrNr = strArrNr + 1). The
line following this uses Nr again.

 

[Rick Rothstein \(MVP - VB\)]

You can do this simpler (in my opinion) if you use a Variant array to hold
your two arrays. Run this sample subroutine and see if you can make
head-or-tails of it (if you have any questions, feel free to post back with
them)...

Sub Test()
Dim X As Long
Dim Arr1 As Long
Dim Arr2 As Long
Dim VarArray As Variant
' Array function arrays are zero based
' unless Option Base 1 is used.
Arr1 = 0
Arr2 = 1
' 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.
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


Rick