vba 检查值是否是列表的成员

Checking if a value is a member of a list

提问by Evil Washing Machine

• I have to check a piece of user input against a list of items; if the input is in the list of items, then direct the flow one way. If not, direct the flow to another.
• This list is NOTvisible on the worksheet itself; it has to be obfuscated under code.

• 我必须根据项目列表检查一段用户输入；如果输入在项目列表中，则以一种方式引导流程。如果没有，则将流程引向另一个。
• 此列表在工作表本身上不可见；它必须在代码下进行混淆。

I have thought of two strategies to do this:

我想到了两种策略来做到这一点：

1. Declare as an enumand check if input is part of this enum, although I'm not sure on the syntax for this - do I need to initialise the enumevery time I want to use it?
2. Declare as an array and check if input is part of this array.

1. 声明为 anenum并检查 input 是否是 this 的一部分enum，尽管我不确定它的语法 -enum每次我想使用它时都需要初始化吗？
2. 声明为一个数组并检查输入是否是该数组的一部分。

I was wondering for VBA which is better in terms of efficiency and readability?

我想知道 VBA 在效率和可读性方面哪个更好？

采纳答案by Evil Washing Machine

Unlike in .NET languages VBA does not expose Enum as text. It strictly is a number and there is no .ToString()method that would expose the name of the Enum. It's possible to create your own ToString()method and return a String representation of an enum. It's also possible to enumerate an Enum type. Although all is achievable I wouldn't recommend doing it this way as things are overcomplicated for such a single task.

与 .NET 语言不同，VBA 不会将 Enum 作为文本公开。它严格来说是一个数字，并且没有.ToString()方法可以公开 Enum 的名称。可以创建自己的ToString()方法并返回枚举的字符串表示。也可以枚举一个 Enum 类型。虽然一切都是可以实现的，但我不建议这样做，因为对于这样一个单一的任务来说事情过于复杂。

How about you create a Dictionary collection of the items and simply use Existmethod and some sort of error handling (or simple if/else statements) to check whether whatever user inputs in the input box exists in your list.

您如何创建项目的 Dictionary 集合，然后简单地使用Exist方法和某种错误处理（或简单的 if/else 语句）来检查您的列表中是否存在用户在输入框中输入的任何内容。

For instance:

例如：

Sub Main()

    Dim myList As Object
    Set myList = CreateObject("Scripting.Dictionary")

    myList.Add "item1", 1
    myList.Add "item2", 2
    myList.Add "item3", 3

    Dim userInput As String
    userInput = InputBox("Type something:")

    If myList.Exists(userInput) Then
        MsgBox userInput & " exists in the list"
    Else
        MsgBox userInput & " does not exist in the list"
    End If

End Sub

Note: If you add references to Microsoft Scripting Runtimelibrary you then will be able to use the intelli-sense with the myListobject as it would have been early bound replacing

注意：如果您添加对Microsoft Scripting Runtime库的引用，那么您将能够对对象使用智能感知，myList因为它会被早期绑定替换

 Dim myList As Object
 Set myList = CreateObject("Scripting.Dictionary")

with

和

Dim myList as Dictionary
Set myList = new Dictionary

It's up to you which way you want to go about this and what is more convenient. Note that you don't need to add references if you go with the Late Binding while references are required if you want Early Binding with the intelli-sense.

这取决于您想要采用哪种方式以及哪种方式更方便。请注意，如果您使用后期绑定，则不需要添加引用，而如果您希望使用智能感知进行早期绑定，则需要引用。

---

---

Just for the sake of readers to be able to visualize the version using Enum let me demonstrate how this mechanism could possibly work

为了让读者能够使用 Enum 可视化版本，让我演示一下这种机制是如何工作的

Enum EList
    item1
    item2
    item3
    [_Min] = item1
    [_Max] = item3
End Enum

Function ToString(eItem As EList) As String
    Select Case eItem
        Case EList.item1
            ToString = "item1"
        Case EList.item2
            ToString = "item2"
        Case EList.item3
            ToString = "item3"
    End Select
End Function

Function Exists(userInput As String) As Boolean
    Dim i As EList
    For i = EList.[_Min] To EList.[_Max]
        If userInput = ToString(i) Then
            Exists = True
            Exit Function
        End If
    Next
    Exists = False
End Function

Sub Main()

    Dim userInput As String
    userInput = InputBox("type something:")

    MsgBox Exists(userInput)

End Sub

First you declare your Listas Enum. I have added only 3 items for the example to be as simple as possible. [_Min]and [_Max]indicate the minimum value and maximum value of enum (it's possible to tweak this but again, let's keep it simple for now). You declare them both to be able to iterate over your EList.

首先，您将List声明为 Enum。为了尽可能简单，我只为示例添加了 3 个项目。[_Min]并[_Max]指出 enum 的最小值和最大值（可以对此进行调整，但同样，现在让我们保持简单）。您声明它们都能够迭代您的EList.

ToString()method returns a String representation of Enum. Any VBA developer realizes at some point that it's too bad VBA is missing this as a built in feature. Anyway, you've got your own implementation now.

ToString()方法返回 Enum 的字符串表示形式。任何 VBA 开发人员都会在某个时候意识到 VBA 缺少此作为内置功能太糟糕了。无论如何，您现在已经有了自己的实现。

Existstakes whatever userInputstores and while iterating over the Enum EListmatches against a String representation of your Enum. It's an overkill because you need to call many methods and loop over the enum to be able to achieve what a simple Dictionary's Existsmethod does in one go. This is mainly why I wouldn't recommend using Enums for your specific problem.

Exists接受任何userInput存储，并在迭代 Enum 时EList与Enum的 String 表示匹配。这是一种矫枉过正，因为您需要调用许多方法并遍历枚举才能一次性实现 simpleDictionary的Exists方法。这就是为什么我不建议将 Enums 用于您的特定问题的主要原因。

Then in the end you have the Mainsub which simply gathers the input from the user and calls the Existsmethod. It shows a Message Box with either trueor falsewhich indicates if the String exists as an Enum type.

然后最后你有Mainsub ，它只是从用户那里收集输入并调用该Exists方法。它显示一个带有true或的消息框false，指示字符串是否作为枚举类型存在。

回答by brettdj

You can run a simple array test as below where you add the words to a single list:

您可以运行一个简单的数组测试，如下所示，将单词添加到单个列表中：

Sub Main1()
arrList = Array("cat", "dog", "dogfish", "mouse")
Debug.Print "dog", Test("dog")   'True
Debug.Print "horse", Test("horse") 'False
End Sub

Function Test(strIn As String) As Boolean
Test = Not (IsError(Application.Match(strIn, arrList, 0)))
End Function

Or if you wanted to do a more detailed search and return a list of sub-string matches for further work then use Filter. This code would return the following via vFilterif looking up dog

或者，如果您想进行更详细的搜索并返回子字符串匹配列表以供进一步工作，请使用Filter. vFilter如果查找，此代码将通过以下方式返回以下内容dog

dog, dogfish

狗，狗鱼

In this particular case the code then checks for an exact match for dog.

在这种特殊情况下，代码然后检查dog.

Sub Main2()
arrList = Array("cat", "dog", "dogfish", "mouse")
Debug.Print "dog", Test1("dog")
Debug.Print "horse", Test1("horse")
End Sub

Function Test1(strIn As String) As Boolean
Dim vFilter
Dim lngCnt As Long
vFilter = Filter(arrList, strIn, True)
For lngCnt = 0 To UBound(vFilter)
    If vFilter(lngCnt) = strIn Then
        Test1 = True
        Exit For
    End If
Next
End Function

回答by Steve G

Just use the Select Casewith a list:

只需将Select Case与列表一起使用：

Select Case entry
   Case item1,item2, ite3,item4 ' add up to limit for Case, add more Case if limit exceeded
      do stuff for being in the list
   Case Else
      do stuff for not being in list
End Select