Does VBA support runtime variable name construction?

Question

Does VBA (inside Access) allow the runtime selection of a variable? I won't know until runtime the actual variable, but I will know at runtime how to construct its name with string manipulation. I know there are alternate ways to address the simple example given below, but my actual situation is much more complex.

The last line shows my made-up syntax to demonstrate what I'm attempting. Is there any legal syntax that would work in this situation?

Public Type myType
    my_abc as integer
    my_xyz as integer
End Type


Public Sub TestProg
    Dim A as myType
    myChoice = "xyz"
    A.my_(myChoice) = 345  ' Made up syntax to attempt assignment to A.my_xyz
End Sub

Possible duplicate of [VBA Creating a variable name by concatenating string and variable?](https://stackoverflow.com/q/35602499/11683) — GSerg, Jun 15 '17 at 16:20
Possible duplicate of [How to create dynamic variable names VBA](https://stackoverflow.com/q/38254337/11683) — GSerg, Jun 15 '17 at 16:20
"my actual situation is much more complex" IMHO if you want suggestions then the best approach is to *not* oversimplify your situation. The bottom-line answer though is that - outside of the "alternate ways" such as callByName, dictionaries, etc VBA does not support variable name construction. — Tim Williams, Jun 15 '17 at 18:48
Note - CallByName would work if you used a class instead of a struct/type — Tim Williams, Jun 15 '17 at 19:02
And, if this is in MS Access, perhaps you would be better off storing the "variables" in a table, and then it is just a case of selecting the field using a variable field name - e.g. "[A].[my_" & myChoice & "]" as part of the query. — YowE3K, Jun 15 '17 at 19:34
Possible duplicate of [How to create dynamic variable names VBA](https://stackoverflow.com/questions/38254337/how-to-create-dynamic-variable-names-vba) — Andre, Jun 15 '17 at 22:28

4dmonster · Answer 1 · 2017-06-16T20:22:42.280

If you can't accept solutions from links in comments.

And if you will know values for all "myChoice" before invocation of function TestProg. You can easially construct it dynamically.

step 1. create empty module and save it with name "emptyModule"

step 2. create module "RunTimeVariableConstruction" put this code there

Option Compare Database
Option Explicit

Public Type myType
    my_abc As Integer
    my_xyz As Integer
End Type

Public Function TestProg()
    Dim A As myType
    A.my_(((myChoice))) = 345  ' Made up syntax to attempt assignment to A.my_xyz
    Debug.Print "now A.my_(((myChoice))) is "; A.my_(((myChoice)))

    A.my_(((myChoiceForSecondField))) = 678  ' Made up syntax to attempt assignment to A.my_xyz
    Debug.Print "now A.my_(((myChoiceForSecondField))) is "; A.my_(((myChoiceForSecondField)))

    Debug.Print "and A.my_(((myChoice))) + A.my_(((myChoiceForSecondField))) is "; A.my_(((myChoice))) + A.my_(((myChoiceForSecondField)))
End Function

Public Sub MakeAndExecute(funcName As String, moduleName As String, ParamArray Mappings())
'MakeAndExecute "TestProg","RunTimeVariableConstruction", "myChoice","xyz","myChoiceForSecondField","abc"

    Dim mSrc As Module
    Dim mTemp As Module
    Set mSrc = Modules(moduleName)
    DoCmd.OpenModule "emptyModule"
    Set mTemp = Modules("emptyModule")
    Dim prog As String
    Dim i As Long

    prog = mSrc.Lines(mSrc.ProcStartLine(funcName, vbext_pk_Proc), mSrc.ProcCountLines(funcName, vbext_pk_Proc))
    prog = Replace(prog, funcName, funcName & "_RTVC")

    For i = 0 To UBound(Mappings) Step 2
        prog = Replace(prog, "(((" & Mappings(i) & ")))", Mappings(i + 1))
    Next i

    mTemp.DeleteLines 1, mTemp.CountOfLines

    mTemp.InsertText prog

    Eval funcName & "_RTVC()"

    DoCmd.Close acModule, "emptyModule", acSaveNo
End Sub

I placed you TestProg in this module for example. Placeholder is (((SomeDynamicPartName))) in text of function.

step 3. test it by calling in intermediate window:

MakeAndExecute "TestProg","RunTimeVariableConstruction", "myChoice","xyz","myChoiceForSecondField","abc"

here "TestProg" is name of function with dynamic template, "RunTimeVariableConstruction" is module of that function. Next pairs of parameters (placeHolder, value). i.e. every occurence of (((myChoice))) in TestProg will be replaced by "xyz", and (((myChoiceForSecondField))) by "abc"

sep 4. get debug output of TestProg

now A.my_xyz is  345 
now A.my_abc is  678 
and A.my_xyz + A.my_abc is  1023

Very clever. Thank you! – Bill Edwards Jul 04 '17 at 12:50 — Bill Edwards, Jul 04 '17 at 12:50

Does VBA support runtime variable name construction?

1 Answers1