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
– braX, Commented Feb 23, 2020 at 2:14
Does this answer your question? How can I convert a string to an actual form label caption in Microsoft Access VBA? — ComputerVersteher
– ComputerVersteher, Commented Feb 23, 2020 at 2:23

ComputerVersteher · Accepted Answer · 2020-02-23 02:48:55Z

1

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

answered Feb 23, 2020 at 2:48

ComputerVersteher

2,6941 gold badge12 silver badges21 bronze badges

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

Comments

Variatus · Accepted Answer · 2020-02-23 02:51:05Z

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

Marx · Accepted Answer · 2020-02-23 04:22:38Z

1

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

answered Feb 23, 2020 at 4:22

Marx

1161 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

Concatenating string and variable to reference another variable

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related