String names to variable names... 2

[GWhiz]

So here's a puzzler (to me, anyway):

I have a database full of electronic instrument names. One of the fields in that database contains a string name for the instrument, such as "HP3437A_ID" (using quotes to refer to a string name). I need to take that same name and convert it to a numeric variable name with exactly the same name (HP3437A_ID -- not a string anymore, hence no quotes around it) to which will be assigned a numeric (long data type) value during the program -- so that when I refer to HP3437A_ID, the numeric value assigned to that non-string variable name is invoked.

IOW -- converting a text name to a numeric variable name of exactly the same name on the fly.

Not sure how to do this, or if it can even be done. If it can be done, I'm sure it's embarrassingly simple to do -- with all the time I've put into trying to figure it out, I still haven't been able to make it work.

Am I barking up a non-existent tree here? Or have I just gotten so paradigm-locked that I can't think outside of my own box?

 

[Andrzejek]

So pretty much you want something like:

Dim HP3437A_ID As Long

HP3437A_ID = 123

MsgBox HP3437A_ID  [green]'Shows 123[/green]

but the [tt]HP3437A_ID[/tt] will come from your database, and the value 123 you will assign in your app?

If so....
Not very *elegant* way - 2 arrays (aryName and aryValue)

[tt]aryName aryValue
(1)"HP3437A_ID" (1)123
(2)"HP343B_ID" (2)234
(3)"HP357F_ID" (3)345
(4)"MAC3437_ID" (4)765
(5)"HPX337A_ID" (5)236
(6)"HZ3437X_ID" (6)45[/tt]

So if you need a value for HP3437A_ID, you find which element from aryName (1) and you can pick up the value from element 1 of aryValue

Have fun.

---- Andy

 

[strongm]

One solution might be to use a Collection (or, better yet, a dictionary)...

[blue]Option Explicit

Public Sub example()
    Dim instrument As New Collection
    instrument.Add "1234567", "HP3437A_ID"
    MsgBox instrument!HP3437A_ID
End Sub

' example2 requires that you set reference to the Microsoft Scripting Runtime in your project
Public Sub example2()
    Dim instrument As New Dictionary
    instrument.Add "HP3437A_ID", 1234567
    MsgBox instrument!HP3437A_ID
End Sub[/blue]

 

[GWhiz]

Ty, Andy. Yeah, I had thought of doing that, and may have to do that still...but wanted a more direct way if that was possible, so want to exhaust all other possibilities before going that route.

-----------------------------------------------

Hmmm...StrongM. That looks interesting. Kind of the same thing as Andy's suggestion, but a little more embedded -- and an embedded solution is kinda what I'm looking for.

I'll give that a try and see where it takes me.

-----------------------------------------------

Thanks very much, both of you fellows...