根据用户输入评估变量 [英] Evaluate variable based on user input

问题描述

来自Python，由于Excel VBA中不存在eval()之类的功能，因此我无法找到实现最终目标的方法.

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.

推荐答案

您正在向用户提供一组有限的，预定的选项.

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.

更好的选择是放弃 InputBox 方法，并向用户提供实际的 UserForm ，以便用户的输入受到约束有限的预定选项集-想到一个 ComboBox 控件:

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:

UserForm的代码很简单-单击按钮时隐藏表单，将"X-out"视为取消，公开用户的 Selection 以及是否通过< Property Get 成员，并在下拉列表中填充以下可能的值:

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

这可以处理用户取消提示的问题，并且如果您的 ComboBox 控件的 Style 属性设置为 fmStyleDropDownList ，则绝对没有办法用户可以提供您不期望的值.您还可以通过提供 Property Get 和 Property Let 成员将 Title 和 Instructions 标签配置为或使调用代码负责提供有效值列表以填充 ComboBox .

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.

那是问题的一部分.下一部分是用另一个字符串值映射"输入.最好的数据结构是 Dictionary ，但是带键的 Collection 也可以正常工作:

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

当然，有很多 方式可以填充此处所需的数据，包括组合框值和集合中的映射字符串.您可以像这样对它们进行硬编码，或者从工作表上的 Range 或从数据库中获取它们，不管您的船摇动了什么.

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.

这篇关于根据用户输入评估变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！