VBA - Is there a way to get a Constant value from a variable String?

Question

I have created few Constants (VBA identifiers declared with the key word Const) that look like Rg_Euro, Rg_Usd, Rg_Cad, ... to define specific regions into my workbook.

As I have these "extensions" (Euro, Usd, Cad, ...) in the DataBase that I'm working with, I tried to get the values of my constant by creating a string like this : Str = "Rg_" & extension(i)

But I can't seem to find a workaround to call the Constant and get its value from it... I'm googled it but didn't found what I was looking for and I'm starting to think that it might not be possible directly...

I thought of a User Defined Function with a Select Case on the String to return the right value, but it is just going to add another function, so I'm looking for a more direct solution if there is one!

What do you mean by constants? Do you mean VBA identifiers declared with the key word Const? If so -- I don't see how you can do much better than using select case or a dictionary. Wrapping it in a function is a sign of good programming rather than bad programming. If by constant you mean entries in the workbook's Names collection (named ranges or named values) then it is easy to access them from string variables. — John Coleman
– John Coleman, Commented Jul 15, 2015 at 15:31
@JohnColeman : I'll edit right away to avoid misunderstandings, but indeed I meant Const variables declared in VBA, I know the others are accessible via ListObject... For the function, I know it is still a good solution but I was wondering if there was a more direct way to do this, even if it apparently does not. — R3uK
– R3uK, Commented Jul 15, 2015 at 15:34
Is there any reason that they aren't names? In any event -- rather than writing a function to return the value from a string, perhaps you can write a function that runs once on workbook_open() and creates or updates names which serve as aliases for these. Named ranges are more natural in VBA then const-tagged ranges (to give a term for what you seem to be doing). — John Coleman
– John Coleman, Commented Jul 15, 2015 at 15:41
Nope, and indeed it seems to be a better idea that the VBA Consts that I started with! Thx for input, I haven't thought of it that way but I'll most probably change it to Named Ranges! I'll still let open the question for a bit and see if anyone has interesting input on the subject — R3uK
– R3uK, Commented Jul 15, 2015 at 15:52
VBA has automatic garbage collection. This shouldn't be an issue. — John Coleman
– John Coleman, Commented Jul 15, 2015 at 16:04

genespos · Accepted Answer · 2015-07-15 15:28:05Z

1

I'm not a pro and my answer it's only what I've done to solve a similar problem, anyway, I hope it helps:

You can add controls (for example Textbox) named as your constants and set the value you need, then you'll be able to catch any value with this:

Me.Controls("RG_" & extension(i)).text

answered Jul 15, 2015 at 15:28

genespos

3,3216 gold badges44 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

R3uK Over a year ago

I didn't thought of that! It is an interesting work around but I think I'll prefer my Select Case function, but anyway, thank you for the input! ;)

Collectives™ on Stack Overflow

VBA - Is there a way to get a Constant value from a variable String?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related