CODEWITHSUNDEEP
excelvbaconcatenation
Alex
Alex

Reputation: 535

Concatenating string and variable to reference another variable

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?

Upvotes: 1

Views: 1017

Answers (3)

Marx
Marx

Reputation: 116

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

Upvotes: 1

Variatus
Variatus

Reputation: 14373

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

Upvotes: 1

ComputerVersteher
ComputerVersteher

Reputation: 2686

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

Upvotes: 1

Related Questions