Use a string as a Variable Name

[Brandt]

First let me apologize for reposting this question. I didn't get any help
yesterday and am hoping a fresh set of eyes will do the trick.

I have a large number of Constants defined sequentially as such :
Public Const BM1Left As Single = 10.2
Public Const BM2Left As Single = 18.7
Public Const BM3Left As Single = 26.4
and so on...

There are enough of these that I would prefer not to use an IF statement or
a Select Case construction to access the desired const. I would be great to
be able to access the particular constant by concatenating or otherwise
"constructing" the name such as in the code below:

Sub Test(index as integer)
Dim Left As Single
Dim myString As String
myString = "BM" & index & "Left"
Left = Evaluate(myString)
End Sub

I get a "type mismatch" with the above code because myString is a string and
Left is a Single. I have searched the discussion groups for 2 days now and
tried every variation I can think of unsuccessfully. Is there a way to get
the variable Left to recognize the name of the Const represented by the
string rather than the just the string itself? I know that named ranges on a
worksheet and buttons on a user form can be evaluated in a similar manner,
but I can not figure out how to do it in this case.

Thanks for any help you can give me.

 

[Dave Peterson]

VBA doesn't work like that.

Maybe you could use a real array:

Dim BMLeft(1 to 3) as double 'I wouldn't use Single
dim myIndex as long

BMLeft(1) = 10.2
BMLeft(2) = 18.7
BMLeft(3) = 26.4

myIndex = 2
msgbox mbleft(myindex)

 

[Brandt]

Thanks Dave

That's what I was afraid of. The array was my backup plan. And sorry for
posting this in the wrong group - not sure what I was thinking.

 

[Dana DeLouis]

myString = "BM" & index & "Left"
The Array approach is the way to go.
Just throwing out an inefficient idea here...

Sub Demo()
Dim indx As Double

With ActiveWorkbook.Names
.Add "BM1Left", 10.2
.Add "BM2Left", 18.7
.Add "BM3Left", 26.4
End With

indx = 2

MsgBox Evaluate("BM" & indx & "Left")
End Sub

 

[Brandt]

Thanks Dana

That's an interesting idea. I appreciate the response.