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!

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


"Christopher Panadol" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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!
>

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.

--
Regards,
Tom Ogilvy

"Christopher Panadol" wrote:

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

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

"Christopher Panadol" wrote:

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

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.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Christopher Panadol" wrote:
>
> > 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!
> >
> >
> >

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Christopher Panadol" wrote:
> >
> > > 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!
> > >
> > >
> > >

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

"Christopher Panadol" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:%(E-Mail Removed)...
>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!
>