Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the contents of a variable as the name of an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the contents of a variable as the name of an array
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. -- Ian -- "Rob" wrote in message news:0t1Vi.7431$Hv4.3353@amstwist00... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing the contents of a variable as the name of an array
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 "Rob" wrote in message news:0t1Vi.7431$Hv4.3353@amstwist00... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replacing contents of a cell with data froma list/file | Excel Worksheet Functions | |||
Concatenate cells, replacing blanks with cell contents from other rows | Excel Worksheet Functions | |||
Replacing a value in the string variable | Excel Programming | |||
Replacing Contents of 1 Cell to Another. | Excel Discussion (Misc queries) | |||
replacing a range with a variable? | Excel Programming |