Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array question
I have an array of variables. Using a "Do" routine I want to assign a column
number to each of the variables in the array. For example: Array(1) contains Var1 and I want to store a column number in Var1. If I write Array(1) = c.column, I find Array(1) contains the value of c.column instead of Var1 containing the value of c.column. Is there a way I can do what I want? Thanks for help. Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array question
Can you post your exact code? I think you are confusing setting the values of
array elements with adding items to a collection, or setting two object variables to point to the same object. First of all, Array is a reserved work. You shouldn't be trying to use it as a variable name. A variable name is simply some text that you use to make it easier to refer to a particular address in memory. You can't "put one variable inside another" (but see comments later). So Array(1) (or, better, Ary(1) can't "contain" Var1. It contains a value. Let's say you wrote Dim Ary() As Variant Redim Ary(1 to 2) Dim Var1 As Variant Var1 = "abc" Ary(1) = Var1 The first 3 lines created 3 variables, with the names Ary(1), Ary(2), and Var1. The last 2 statements assign values to two of them. The last statement assigns the value to Ary(1) by copying the value from the memory space that you refer to with the name Var1 and pasting it into the memory space that you refer to by the name Ary(1). It doesn't "put var1 inside" of Ary(1). Ary(1) and Var1 are separate and independent variables. Changing the value of one of them doesn't affect the other. There's an exception to my statement above about one variable not containing another. That has to do with collections and object variables. The value of an object variable is actually the memory address of another variable. Collections are an array of object variables. When you add items to the collection, you create a new object variable and assign it a value. I just tried the following code. If you expect to print 1 and 2 the first time, then -1 and -2 the second, that doesn't happen. The object in the collection is now totally separate from the variable that provided its initial value. And I don't see a way to change the value once you've added an item to the collection. e.g. if you remove the apostrophe from the line just above the End Sub line, you get an error. Sub Test() Dim Coll As Collection Dim Var1 As Double Dim Var2 As Double Var1 = 1 Var2 = 2 Set Coll = New Collection Coll.Add Var1, "Var1" Coll.Add Var2, "Var2" Debug.Print Coll("Var1") Debug.Print Coll("Var2") Var1 = -1 Var2 = -2 Debug.Print Coll("Var1") Debug.Print Coll("Var2") 'Coll("Var1") = -1 End Sub But somehow I don't think you have any need to work with collections and objects here, do you? You just want to save several numbers using one variable name with indices, i.e. an array. Can you describe what your ultimate goal is? On Fri, 17 Sep 2004 15:08:37 -0400, "Jim Simpson" wrote: I have an array of variables. Using a "Do" routine I want to assign a column number to each of the variables in the array. For example: Array(1) contains Var1 and I want to store a column number in Var1. If I write Array(1) = c.column, I find Array(1) contains the value of c.column instead of Var1 containing the value of c.column. Is there a way I can do what I want? Thanks for help. Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array question
Thanks Myma for responding,
I want to get each column number of a number of columns of a table into a variable. My idea was to create an array containing the column headings and another array containing the associated variable: aHeads = array("Date", "Symbol",...etc.) aColVar = array(datCol, symCol,...etc.) Then using a ForEach routine, search for each heading name from the first array and enter the integer for the column number in the respective variable from the second array. So, if Column 1 heading is "Date" and Column 4 heading is "Symbol" then: datCol would contain "1", symCol would contain "4", and so on for all the columns I have and interest in. You indicate that I had better find another way to do it. Jim "Myrna Larson" wrote in message ... Can you post your exact code? I think you are confusing setting the values of array elements with adding items to a collection, or setting two object variables to point to the same object. First of all, Array is a reserved work. You shouldn't be trying to use it as a variable name. A variable name is simply some text that you use to make it easier to refer to a particular address in memory. You can't "put one variable inside another" (but see comments later). So Array(1) (or, better, Ary(1) can't "contain" Var1. It contains a value. Let's say you wrote Dim Ary() As Variant Redim Ary(1 to 2) Dim Var1 As Variant Var1 = "abc" Ary(1) = Var1 The first 3 lines created 3 variables, with the names Ary(1), Ary(2), and Var1. The last 2 statements assign values to two of them. The last statement assigns the value to Ary(1) by copying the value from the memory space that you refer to with the name Var1 and pasting it into the memory space that you refer to by the name Ary(1). It doesn't "put var1 inside" of Ary(1). Ary(1) and Var1 are separate and independent variables. Changing the value of one of them doesn't affect the other. There's an exception to my statement above about one variable not containing another. That has to do with collections and object variables. The value of an object variable is actually the memory address of another variable. Collections are an array of object variables. When you add items to the collection, you create a new object variable and assign it a value. I just tried the following code. If you expect to print 1 and 2 the first time, then -1 and -2 the second, that doesn't happen. The object in the collection is now totally separate from the variable that provided its initial value. And I don't see a way to change the value once you've added an item to the collection. e.g. if you remove the apostrophe from the line just above the End Sub line, you get an error. Sub Test() Dim Coll As Collection Dim Var1 As Double Dim Var2 As Double Var1 = 1 Var2 = 2 Set Coll = New Collection Coll.Add Var1, "Var1" Coll.Add Var2, "Var2" Debug.Print Coll("Var1") Debug.Print Coll("Var2") Var1 = -1 Var2 = -2 Debug.Print Coll("Var1") Debug.Print Coll("Var2") 'Coll("Var1") = -1 End Sub But somehow I don't think you have any need to work with collections and objects here, do you? You just want to save several numbers using one variable name with indices, i.e. an array. Can you describe what your ultimate goal is? On Fri, 17 Sep 2004 15:08:37 -0400, "Jim Simpson" wrote: I have an array of variables. Using a "Do" routine I want to assign a column number to each of the variables in the array. For example: Array(1) contains Var1 and I want to store a column number in Var1. If I write Array(1) = c.column, I find Array(1) contains the value of c.column instead of Var1 containing the value of c.column. Is there a way I can do what I want? Thanks for help. Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array question
The only way to do that is with code something like this:
Dim X As Long 'assume everything is found For i = LBound(aHeads) to UBound(AHeads) X = Application.MATCH(aHeads(i), Rows(1), 0) Select Case i Case 0: datCol = X Case 1: symCol = X Case 2: .... Case 3: .... Case 4: .... End Select Next I Another approach is to set up constants, i.e. Const datCol = 0 Const symCol = 1 etc. Then you can avoid code like above, but when you need to refer to a column, instead of Cells(R, datCol) you write Cells(R, aColVar(datCol)) "Jim Simpson" wrote in message news:1AX2d.98839$yh.26790@fed1read05... Thanks Myma for responding, I want to get each column number of a number of columns of a table into a variable. My idea was to create an array containing the column headings and another array containing the associated variable: aHeads = array("Date", "Symbol",...etc.) aColVar = array(datCol, symCol,...etc.) Then using a ForEach routine, search for each heading name from the first array and enter the integer for the column number in the respective variable from the second array. So, if Column 1 heading is "Date" and Column 4 heading is "Symbol" then: datCol would contain "1", symCol would contain "4", and so on for all the columns I have and interest in. You indicate that I had better find another way to do it. Jim "Myrna Larson" wrote in message ... Can you post your exact code? I think you are confusing setting the values of array elements with adding items to a collection, or setting two object variables to point to the same object. First of all, Array is a reserved work. You shouldn't be trying to use it as a variable name. A variable name is simply some text that you use to make it easier to refer to a particular address in memory. You can't "put one variable inside another" (but see comments later). So Array(1) (or, better, Ary(1) can't "contain" Var1. It contains a value. Let's say you wrote Dim Ary() As Variant Redim Ary(1 to 2) Dim Var1 As Variant Var1 = "abc" Ary(1) = Var1 The first 3 lines created 3 variables, with the names Ary(1), Ary(2), and Var1. The last 2 statements assign values to two of them. The last statement assigns the value to Ary(1) by copying the value from the memory space that you refer to with the name Var1 and pasting it into the memory space that you refer to by the name Ary(1). It doesn't "put var1 inside" of Ary(1). Ary(1) and Var1 are separate and independent variables. Changing the value of one of them doesn't affect the other. There's an exception to my statement above about one variable not containing another. That has to do with collections and object variables. The value of an object variable is actually the memory address of another variable. Collections are an array of object variables. When you add items to the collection, you create a new object variable and assign it a value. I just tried the following code. If you expect to print 1 and 2 the first time, then -1 and -2 the second, that doesn't happen. The object in the collection is now totally separate from the variable that provided its initial value. And I don't see a way to change the value once you've added an item to the collection. e.g. if you remove the apostrophe from the line just above the End Sub line, you get an error. Sub Test() Dim Coll As Collection Dim Var1 As Double Dim Var2 As Double Var1 = 1 Var2 = 2 Set Coll = New Collection Coll.Add Var1, "Var1" Coll.Add Var2, "Var2" Debug.Print Coll("Var1") Debug.Print Coll("Var2") Var1 = -1 Var2 = -2 Debug.Print Coll("Var1") Debug.Print Coll("Var2") 'Coll("Var1") = -1 End Sub But somehow I don't think you have any need to work with collections and objects here, do you? You just want to save several numbers using one variable name with indices, i.e. an array. Can you describe what your ultimate goal is? On Fri, 17 Sep 2004 15:08:37 -0400, "Jim Simpson" wrote: I have an array of variables. Using a "Do" routine I want to assign a column number to each of the variables in the array. For example: Array(1) contains Var1 and I want to store a column number in Var1. If I write Array(1) = c.column, I find Array(1) contains the value of c.column instead of Var1 containing the value of c.column. Is there a way I can do what I want? Thanks for help. Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array question | Excel Worksheet Functions | |||
another array question | Excel Worksheet Functions | |||
Array Question | Excel Programming | |||
Array question | Excel Programming | |||
array question | Excel Programming |