Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
See if Alan Beban has something you can use or adapt:
http://home.pacbell.net/beban/ -- Gary''s Student - gsnu2007a "Rob" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
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) "Rob" wrote in message news:I45Xi.8200$Hv4.5497@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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
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) "Chip Pearson" wrote in message ... 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) "Rob" wrote in message news:I45Xi.8200$Hv4.5497@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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
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/micro...460582ed4ea3de Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
See inline comment....
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/micro...460582ed4ea3de 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use the name for an array by using a variable?
Rick Rothstein (MVP - VB) wrote:
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/micro...460582ed4ea3de 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dim an Array with a variable | Excel Programming | |||
Array & variable | Excel Programming | |||
how can I see if an array contain a certain variable? | Excel Programming | |||
about ARRAY variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |