Variable Naming.....?

[Jason Paris]

Hi all,

Just working on my first VBA code. A quick question please.......

When using a Counter, how can you create a Variable whose name is
based on the current value of the Counter?

For example:
________________________________________

First Iteration
***************
Counter = 1
Create a Variable whose name is 'VAR1'


Second Iternation
********************
Counter = 2
Create a Variable whose name is 'VAR2'


Third Iteration
***************
Counter = 3
Create a Variable whose name is 'VAR3'
________________________________________

I hope that makes sense.....!

Many thanks,

Jason Paris

 

[Jim Cone]

Jason,
I don't believe you can (at least within the same sub)
and it probably isn't necessary...

If Counter = 1 Then
'do something
ElseIf Counter = 2 Then
'do something else
ElseIf Counter > 2 Then
'take a break
End If

Also, you can also declare a variable at the top of a standard module.
Its value would then be available in all subs/function/modules...

Public Counter as Long
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Jason Paris" <[email protected]>
wrote in message
Hi all,
Just working on my first VBA code. A quick question please.......
When using a Counter, how can you create a Variable whose name is
based on the current value of the Counter?
For example:

First Iteration
***************
Counter = 1
Create a Variable whose name is 'VAR1'
Second Iternation
********************Counter = 2
Create a Variable whose name is 'VAR2'
Third Iteration
***************
Counter = 3
Create a Variable whose name is 'VAR3'
I hope that makes sense.....!
Many thanks,
Jason Paris

 

[Bob Phillips]

You can't, but you can use an array

Dim Var(1 to 12)

Counter = 2
Msgbox Var(Counter)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Smallweed]

You can't do this as written but why not use an array (look them up in VBA
help to get the full info)?

Dim myArray(20)
Counter = 1
myArray(Counter)=???
Counter=2
myArray(Counter)=???
etc

 

[John Mansfield]

Possibly another option if you do intend to put "VAR" in front of the counter
number:

Sub Test()
Dim i As Variant
Cnt = 1
Do Until Cnt = 10
Cnt = Cnt + 1
Loop
i = "Var" & Cnt
msgbox i
End Sub

 

[Dave Peterson]

VBA doesn't work like that.

But you can define an array and store things in the elements of that array.

Dim Counter as long
dim myArr() as long 'whatever you need

redim myarr(1 to 10)

for counter = lbound(myarr) to ubound(myarr)
myarr(counter) = 17 * counter + 32
next counter

==========
Later you can refer to any element

dim iCtr as long 'just another variable

ictr = 5 'something between 1 and 10 in my example.
msgbox myarr(ictr)