Evaluate variable based on user input

Question

Coming from Python, I cannot figure out a way to accomplish my end goal since a function like eval() does not exist with Excel VBA.

Below is what I am trying to achieve by using a simple example that I can scale:

Dim userinput as String
userinput = inputbox("A=John, B=Kate, C=Tim", "Enter a letter that corresponds with your name: ")
Dim A as String
Dim B as String
Dim C as String

A = John
B = Kate
C = Tim

Dim Phrase as Variant
phrase = msgbox("Hello, my name is " & userinput)

Result:
User enters a C
msg box pops up as "Hello, my name is Tim"

Regardless of what I try to do, I cannot have a userinput correspond to a variable that is then interpreted while in a string. My macro would be useless without a user's input referencing a much longer and complex phrase that would be too hard for a user to enter on their own.

Answer 1

The user is typing a letter. You need code to more explicitly translate that into a name. There are a lot of methods - if you have a long list or a user-entered list you may need a solution that is more dynamic. However here is a fix for your code:

Dim userinput as String
userinput = inputbox("A=John, B=Kate, C=Tim", "Enter a letter that corresponds with your name: ")
Dim A as String
Dim B as String
Dim C as String
Dim nameOut as String

select case userinput
    case "A"
         nameOut = "John"
    case "B"
         nameOut = "Kate"
    case "C"
         nameOut = "Tim"
end select

Dim Phrase as Variant
phrase = MsgBox("Hello, my name is " & nameOut)

Note: don't forget to enclose strings in double quotes.

Answer 2

You're presenting a finite, pre-determined set of options to the user.

Yet you let them enter quite literally anything, not validated, and roll with it.

A much better alternative would be to ditch the InputBox approach and present the user with an actual UserForm, so that the user's input is constrained to a finite, pre-determined set of options - a ComboBox control comes to mind:

The UserForm's code-behind is pretty straightforward - hide the form when a button is clicked, treat "X-out" as a cancellation, expose the user's Selection and whether the form was cancelled via Property Get members, and populate the dropdown with the possible values to pick from:

Option Explicit
Private isCancelled As Boolean

Public Property Get Cancelled() As Boolean
    Cancelled = isCancelled
End Property

Public Property Get Selection() As String
    Selection = ComboBox1.Value
End Property

Private Sub CancelButton_Click()
    isCancelled = True
    Hide
End Sub

Private Sub OkButton_Click()
    Hide
End Sub

Private Sub UserForm_Activate()
    With ComboBox1
        .Clear
        .AddItem "Lorem"
        .AddItem "Ipsum"
        '...
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        isCancelled = True
        Hide
    End If
End Sub

And now you can do this:

Public Sub Test()
    With New UserForm1
        .Show
        If Not .Cancelled Then
            MsgBox .Selection
        End If
    End With
End Sub

This handles the user cancelling out of the prompt, and if your ComboBox control's Style property is set to fmStyleDropDownList, there's absolutely no way the user can supply a value you're not expecting. And you can extend it further by supplying Property Get and Property Let members to configure the Title and Instructions labels as needed, or make the calling code responsible for supplying the list of valid values to populate the ComboBox with.

That's one part of the problem. The next part is "mapping" inputs with another string value. The best data structure for this is a Dictionary, but a keyed Collection can work just as well:

Dim values As Collection
Set values = New Collection
values.Add "string 1", "Lorem"
values.Add "string 2", "Ipsum"
'...

With New UserForm1
    .Show
    If Not .Cancelled Then
        MsgBox values(.Selection)
    End If
End With

Of course there are many ways to populate the data you need here, both for the combobox values and the mapped strings in the collection; you can hard-code them like this, or get them from a Range on a worksheet, or from a database, whatever rocks your boat.

Answer 3

Not to take away from Scott's answer but for a potentially more dynamic approach it seems class modules and callbyname have an answer:

Class module - class1

Private pa As String
Public Property Get a() As String
a = pa
End Property
Public Property Let a(value As String)
pa = value
End Property

Test sub

Sub testing()
Dim cls1 As Class1
    Set cls1 = New Class1

cls1.a = "tim"

userinput = "a"

Debug.Print CallByName(cls1, userinput, VbGet)

End Sub

Answer 4

Posted just because it's a different approach. It finds the position of the inputted letter in an array of all three, and then finds the corresponding item in an array of the names.

Sub x()

Dim userinput As String
Dim Phrase As String
Dim v

userinput = InputBox("A=John, B=Kate, C=Tim", "Enter a letter that corresponds with your name: ")
v = Array("John", "Kate", "Tim")

With Application
    If IsNumeric(.Match(userinput, Array("A", "B", "C"), 0)) Then
        Phrase = .Index(v, .Match(userinput, Array("A", "B", "C"), 0))
        MsgBox "Hello, my name is " & Phrase
    Else
        MsgBox "Wrong letter"
    End If
End With

End Sub