Array question

[Jim Simpson]

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

 

[Myrna Larson]

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?

 

[Jim Simpson]

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

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?

 

[Myrna Larson]

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))

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