Concatenate a Const Name in Sub Routine

[Brandt]

I have declared several constants at the top of Module1 such as:

Option Explicit
Public Const BM1Left As Single = 10.1
Public Const BM2Left As Single = 20.1
Public Const BM3Left As Single = 30.1
Etc..

In the subroutine I would like to access them with a counter via
concatenating the name such as:

Sub Test1()
Dim Counter As Integer
Dim Left As Single
Counter = 1
Left = "BM" & Counter & "Left"
End Sub

Where counter could be set by a For loop or otherwise. (So that in the
above case the variable Left = BM1Left = 10.1) The above code does not work
because the expression is evaluated as a string rather than the name of the
Constant I want. Does anyone know how I could access the Const names similar
to the "indirect" worksheet function?

Thanks in advance

 

[Mike H.]

Could you not just say:



If counter=1 then
left=10.1
elseif counter=2 then
left=20.1
....etc

or just
left=(counter*10)+.1

 

[Brandt]

Thanks for the reply Mike. I guess I had over simplified my post. The
numbers are random (I am sorry I implied that (counter*10)+.1 would work) and
there are enough of them that I'd like to avoid using an "IF" or "Select
Case" statement (hence the constant declaration).

Any other ideas?

 

[Mike H.]

I tried using the evaulate function in conjunction with indirect but could
not get it to work. But if you would explain what you're trying to do,
perhaps I might have a different approach. I have not used constants at all
and have never felt like I needed to.

 

[Brandt]

I am creating drawing objects on the active sheet such as:
with Activesheet.Shapes
.AddShape(msoShapeRectangle, Left, Top, , 100).Name = "BM"
The drawings are created when the user clicks a paticular button on the
sheet. The click event of the button initializes the counter to the
appropriate value (1 thru 20 lets say). The drawing object would then be
placed at the appropriate "Left" position by setting
Left = "BM" &Counter &"Left"
such that if button 18 was clicked "Left" should be set equal to the value
of BM18Left. (There will also be such variables for "Top", "Width", etc) I
would like the BMxxLeft variables as Const's because that makes it much
easier to adjust them as the code progresses and changes. But either way I
do it, it would be much easier to concatenate the name as mentioned above
then to use a "IF" statement or something.

Thanks again for your help and any ideas.

 

[Bernd P]

I have declared several constants at the top of Module1 such as:

Option Explicit
Public Const BM1Left As Single = 10.1
Public Const BM2Left As Single = 20.1
Public Const BM3Left As Single = 30.1
Etc..

Hello,

I suggest to declare
Const BMLeft(1 to 3) as single

Then initialize in a dedicated sub:
BMLeft(1) = 10.1
....

Later on you can assign:
Left = BMLeft(Counter)


Regards,
Bernd