access vars through string?

[drewdaman]

hi,

if you remember me from yesterday, you know i don't have much experience with vba and i'm a c++ person..

i remember doing somethign strange in vba a few years ago.. something that i know you can't do in c++. i somehow managed to use a string that started with a letter of the alphabet and appended a number to it. then i was able to use variables of the same name.. and access them through the string. do you know how i can do that? would make my life easier.

example:

Sub test()
Dim k1 As Integer
Dim k2 As Integer
Dim k3 As Integer

Dim temp As String
k1 = 11
k2 = 22
k3 = 33
temp = "k"

For i = 1 To 3
    temp = temp + CStr(i)
    MsgBox (temp) 
    temp = "k"
Next i

End Sub

that for loop i have is not correct.. ie it works, but it doesn't do what i want it to do.. every time i get temp to hold a value like "k1" or "k2" i want to be able to access the long declared in the first few lines of code.

is this possible? i remember doing it in access... i'm using excel now tho.. don't think that should make a diff..

thanks!

ps. if i'm mistaken about this, i won't be surprised! because i know you can't do it in other languages that i use!

 

[Zathras]

What's the point? It is just as easy (and probably easier) to use an array:

Sub test()
Dim k(3) As Integer
Dim i As Integer
  k(1) = 11
  k(2) = 22
  k(3) = 33
  For i = 1 To 3
    MsgBox k(i)
  Next i
End Sub

 

[drewdaman]

thanks for the reply.

true... but i'm trying to initialize an array based on some variables in a struct that i defined. i have it set up that
way and i don't want to change it!

 

[PHV]

Unfortunately the Eval stuff is not part of VBA but an application method, thus the diff between access and excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drewdaman]

oh ok.. i will find a way around it then

thanks for the replies!

 

[Zathras]

Here is one way around it using global variables and a custom function:

Dim k1 As Integer
Dim k2 As Integer
Dim k3 As Integer

Sub test()

Dim temp As String
k1 = 11
k2 = 22
k3 = 33
temp = "k"

For i = 1 To 3
    temp = temp + CStr(i)
    MsgBox MyWay(temp)
    temp = "k"
Next i

End Sub

Function MyWay(AString As String) As Integer
Select Case AString
  Case "k1": MyWay = k1
  Case "k2": MyWay = k2
  Case "k3": MyWay = k3
End Select
End Function