Concatenating string and variable to reference another variable

Question

Is it possible to combine a string and a variable to create a name of another variable and referencing it in the same go? Like this:

Sub Test()
    Dim colorName As String
    Dim columnYellow As Long

    colorName = "Yellow"
    columnYellow = 3

    Debug.Print columnYellow '-> prints "3"
    Debug.Print "column" & colorName '-> prints "columnYellow" (I would like it to return 3)

End Sub

I would want Debug.Print "column" & colorName to return "3" instead of "columnYellow". Ho can I do that?

You could use a scripting dictionary to associate them, but that seems like a bit of overkill for such a simple task... Maybe an enumeration would do what you wanted? in short, the structure would have to be one variable to associate them... they cant just be a string and a long in 2 independent variables. — braX, Feb 23 '20 at 02:14
Does this answer your question? [How can I convert a string to an actual form label caption in Microsoft Access VBA?](https://stackoverflow.com/questions/60103081/how-can-i-convert-a-string-to-an-actual-form-label-caption-in-microsoft-access-v) — ComputerVersteher, Feb 23 '20 at 02:23

score 1 · Accepted Answer · answered Feb 23 '20 at 02:48

Variable identifiers can't be concated, except with CallByName what is limited to objects (you can't call a method from a standard module).

As alternative use an array or a collection. You have to use the collection, where you can have a string as key to a value and strings can be concated.

Sub Test()
    Dim ColorNameNr As Collection
    Dim colorName As String
    Set ColorNameNr = New Collection
    ColorNameNr.Add 3, "columnYellow"
    colorName = "Yellow"

    Debug.Print ColorNameNr.Item("columnYellow") '-> prints "3"
    Debug.Print ColorNameNr.Item("column" & colorName) '-> prints "3")
End Sub

score 1 · Answer 2 · answered Feb 23 '20 at 02:51

Might this code help? Enter either a valid index number or a word for a listed colour.

Function ColIndex(ByVal Arg As Variant) As Variant
    ' return 0 if not found

    ' first colour listed has the index #1
    Const Cols As String = "Red,Green,Yellow"
    Dim Col() As String
    Dim i As Integer

    Select Case VarType(Arg)
        Case vbString
            Col = Split(Cols, ",")
            For i = UBound(Col) To 0 Step -1
                ' use VbBinaryCompare for case sensitive comparison
                If StrComp(Col(i), Arg, vbTextCompare) = 0 Then Exit For
            Next i
            If i >= 0 Then ColIndex = i + 1
        Case vbInteger, vbLong
            On Error Resume Next
            ColIndex = Split(Cols, ",")(Arg - 1)
    End Select
End Function

score 1 · Answer 3 · answered Feb 23 '20 at 04:22

Could we use an array?

Sub Test()
    Dim colors(0 To 1, 0 To 1) as Variant

    colors(0, 1) = "Yellow"
    colors(1, 0) = 3

    Debug.Print "Column " & colors(1, 0) 'prints column number
    Debug.Print "Color " & colors(0, 1) 'prints color name
End Sub

Concatenating string and variable to reference another variable

3 Answers3