Evaluate Variable At Run-Time

[wsnyder]

Hi,

Using Excel 365.

How do I evaluate a variable at run-time?
The code below is returning sp1, sp2, sp3
I am trying to evaluate the variable as Alpha, Bravo, Charlie,

Thanks
-w

Sub foo2()

    Const sp1 As String = "Alpha"
    Const sp2 As String = "Bravo"
    Const sp3 As String = "Charlie"
    Const myv As String = "sp"
    Dim v As Variant
    Dim s As String
    Dim i As Long
    
    For i = 1 To 3
        s = myv & i
        Evaluate (s)
        Debug.Print s
    Next i
    
End Sub

 

[JEC]

Try:

Debug.Print Evaluate(s)

 

[Alex Blakenburg]

I am not sure it can be done that way. Why not ?

Option1

Sub Option1()

    Const sp As String = "Alpha,Bravo,Charlie"
    
    Dim i As Long
    Dim s As Variant
     
    s = Split(sp, ",")
    
        For i = LBound(s) To UBound(s)
            Debug.Print s(i)
        Next i
End Sub

Option2

Sub Option2()
        
    Dim i As Long
    Dim s As Variant
    s = Array("Alpha", "Bravo", "Charlie")
    
    For i = LBound(s) To UBound(s)
            Debug.Print s(i)
    Next i
    
End Sub

 

[wsnyder]

I am not sure it can be done that way. Why not ?

Option1

Sub Option1()

    Const sp As String = "Alpha,Bravo,Charlie"
   
    Dim i As Long
    Dim s As Variant
    
    s = Split(sp, ",")
   
        For i = LBound(s) To UBound(s)
            Debug.Print s(i)
        Next i
End Sub

Option2

Sub Option2()
       
    Dim i As Long
    Dim s As Variant
    s = Array("Alpha", "Bravo", "Charlie")
   
    For i = LBound(s) To UBound(s)
            Debug.Print s(i)
    Next i
   
End Sub

Thanks Alex,
Much better!

Thanks,
-w

 

[Alex Blakenburg]

Thanks for the feedback. Glad I could help.