Conversion of text to variable

[Don Wells]

I think the following example says it all.

Code: Select all

Public Sub Example()

Dim Oranges As Long
Dim Apples As Long
Dim Var_Name As String

  Apples = 15
  Oranges = 27
  
  Var_Name = "Apples"
  
' I need a code snippet here to return
' 15 or 27 depending on the
' string assigned to Var_Name.

End Sub

Many thanks in advance.

[ChrisGreaves]

Var_Name = "Apples"

' I need a code snippet here to return
' 15 or 27 depending on the
' string assigned to Var_Name.
...

This used to be possible in Word6.0, for back in the day, I wrote functions that built functions within the same template.
Sadly that language-based versatility was taken away with Office97. Another "improvement to serve you better"
(signed) "Bitter and disillusioned" of Bonavista.

Cheers
Chris

[HansV]

Access VBA has a method Eval that would do what you want, but it is not available in generic VBA.
You might create a class module named Fruit with the following code:

Code: Select all

Option Explicit

Public Property Get Apples()
    Apples = 15
End Property

Public Property Get Oranges()
    Oranges = 27
End Property

Then use

Code: Select all

Sub Example()
    Dim MyFruit As New Fruit
    Dim Var_Name As String
    Dim Elio As Long

    Var_Name = "Apples"

    ' Do something with the value represented by Var_Name
    Elio = CallByName(MyFruit, Var_Name, VbGet)
    Debug.Print Elio
End Sub

[rory]

You could also use something like a collection or dictionary:

Code: Select all

Public Sub Example()
Dim col As Collection
Set col = New Collection

Dim Var_Name As String

  col.Add 15, "Apples"
  col.Add 27, "Oranges"
  
  Var_Name = "Apples"
  
' I need a code snippet here to return
' 15 or 27 depending on the
' string assigned to Var_Name.
MsgBox col(Var_Name)
End Sub

[LisaGreen]

Hi,

There is also a novel way. You can build the variable in situ. As I said it's novel and probably of no practical use but I have used it to simulate arrays of variables.

The general method is to create code in a new module with Dim <variable name> in it and run it.

I've mentioned this before a couple of times I think here in the lounge. If you need more and can't find the reference I'll dig the code out for you.

HTH
Lisa