How to convert the string to variable?

[Christopher Panadol]

I write VBA in Excel 2003. I have a program which have the serveral
variables, say, var1, var2, var3. Every time when I need to put a value to
these 3 variables I need to write the code as follows:

for i = 1 to 3
select case i
case 1
var1 = <value>
case 2
var2 = <value>
case 3
var3 = <value>
end select
next i

Is there a way to simplify the code that can convert the string to
variables? Sorry for my poor English. Thank you very much!

 

[RB Smissaert]

Maybe use an array instead of single variables, so you will get something
like this:

Dim i As Long
Dim arrLong(1 to 3) As Long

For i = 1 to 3
arrLong(i) = <value>
Next i


RBS

 

[Tom Ogilvy]

You can use an array

Sub Demo1()
Dim i as long, v(1 to 3) as Long
for i = 1 to 3
v(i) = int(rand()*10+1)
Next

for i = 1 to 3

msgbox "var(" & i & ") = " & var(i)
Next

end Sub


if you wanted to assign them based on ranges

i = 1
for each cell in Range("B1,C9,F7")
v(i) = cell.value
i = i + 1
Next

as an example.

 

[J. Caplan]

Instead of having 3 string variables, can you store the values in an Array

Dim vars(1 to 3) As String
for i = 1 to 3
vars(i) = <value>
next i

 

[Tom Ogilvy]

sorry, got sloppy with the var's and v's


Sub Demo1()
Dim i as long, v(1 to 3) as Long
for i = 1 to 3
v(i) = int(rand()*10+1)
Next

for i = 1 to 3

msgbox "v(" & i & ") = " & v(i)
Next

end Sub


if you wanted to assign them based on ranges

i = 1
for each cell in Range("B1,C9,F7")
v(i) = cell.value
i = i + 1
Next

as an example.

You could use

Dim var(1 to 3) as long as well.

 

[Tom Ogilvy]

bad day

Sub Demo1()
Dim i As Long, v(1 To 3) As Long
For i = 1 To 3
v(i) = Int(Rnd() * 10 + 1)
Next

For i = 1 To 3

MsgBox "v(" & i & ") = " & v(i)
Next

End Sub

 

[Christopher Panadol]

Dears,

I need to say sorry that I was the FoxPro programmer before. I feel very
comfortable that using the way of storing information by locating the
variable address using "&" function. I have certainly considered using
array method. It is, however, necessary for me to use this
string-to-variable method.

Anyway, thank you for your answer.

Chris