检查值是否是列表的成员 [英] Checking if a value is a member of a list

问题描述

• 我必须根据项目列表检查一段用户输入;如果输入在项目列表中，则可以单向引导流程。如果没有，请将流量引导到另一个。


• 此列表是工作表上的不可见;它必须在代码下混淆。

我已经考虑过两种策略：

1. 声明为枚举，并检查输入是否是此枚举的一部分，虽然我不确定这个语法 - 每次我想要使用它时，是否需要初始化枚举？


2. 声明为数组，并检查输入是否为此数组的一部分。

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

解决方案

与.NET语言不同，VBA不会将Enum显示为文本。它严格是一个数字，并且没有将公开枚举名称的 .ToString（）方法。可以创建自己的 ToString（）方法并返回枚举的String表示形式。也可以枚举枚举类型 。尽管所有这些都是可以实现的，但我不会建议这样做，因为这样一个任务过于复杂。

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

例如：

  Sub Main（）
 
 Dim myList As Object 
设置myList = CreateObject（Scripting.Dictionary）
 
 myList.Additem1，1 
 myList.Additem2 ，2 
 myList.Additem3，3 
 
 Dim userInput As String 
 userInput = InputBox（Type something：）
 
如果myList.Exists（userInput）然后
 MsgBox userInput& 存在于列表
 Else 
 MsgBox userInput& 列表中不存在
 End If 
 
 End Sub 
  

注意：如果添加对 Microsoft Scripting Runtime 库的引用，那么您将能够使用Intelli-sense与 myList 对象，因为它将是早期绑定替换

  Dim myList As Object 
设置myList = CreateObject（ scripting.Dictionary）
  

与

  Dim myList as Dictionary 
 Set myList = new Dictionary 
  

这取决于你想要的方式，什么更方便。请注意，如果您希望使用Intelli-sense的Early Binding，则不需要添加引用，如果您使用Late Binding，则需要引用。

---

只是为了读者能够使用枚举来显示版本，让我演示这种机制可能如何工作

  Enum EList 
 item1 
 item2 
 item3 
 [_Min] = item1 
 [_Max] = item3 
结束枚举
 
函数ToString（eItem As EList）As String 
选择案例eItem 
案例EList.item1 
 ToString =item1
案例EList.item2 
 ToString =item2
案例EList.item3 
 ToString =item3
结束选择
结束函数
 
函数存在（userInput As String）As Boolean 
 Dim i As EList 
 For i = EList。[_ Min] To EList。[_ Max] 
如果userInput = ToString（i）Then 
 Exists = True 
退出函数
结束如果
下一个
存在= False 
结束函数
 
 Sub Main（）
 
 Dim userInput As String 
 userInput = InputBox（type something：）
 
 MsgBox存在（userInput）
 
 End Sub 
  pre> 
 
 
首先，您将 列表 声明为枚举。我已经添加了只有3个项目为例，尽可能简单。  [_ Min] 和 [_ Max] 表示枚举的最小值和最大值（可以调整这个，再一次，现在让我们保持简单）。您声明他们都能够迭代您的 EList 。 
 
 
 
  ToString（）方法返回枚举的字符串表示形式。任何VBA开发人员在某种程度上意识到这太糟糕了VBA缺少这个内置功能。无论如何，你现在都有自己的实现。
 
 
 
 存在采取任何 userInput 存储，同时遍历枚举 EList 与您的枚举的String表示符相匹配。这是一个overkill，因为你需要调用很多方法并循环遍历枚举，以便能够实现一个简单的字典的存在方法一次就行了。这主要是为什么我不建议为您的具体问题使用枚举。
 
 
 
最后，您有主要 sub，其简单地从用户收集输入并调用 Exists 方法。它显示一个消息框，其中包含  true  或  false  这表示字符串是否以枚举类型存在。
 

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 NOT visible on the worksheet itself; it has to be obfuscated under code.


I have thought of two strategies to do this:

Declare as an enum and check if input is part of this enum, although I'm not sure on the syntax for this - do I need to initialise the enum every time I want to use it?
Declare as an array and check if input is part of this array. 
I was wondering for VBA which is better in terms of efficiency and readability?
解决方案
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.

How about you create a Dictionary collection of the items and simply use Exist method 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.

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 Runtime library you then will be able to use the intelli-sense with the myList object as it would have been early bound replacing 
 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 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 List as 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. 

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.

Exists takes whatever userInput stores and while iterating over the Enum EList matches 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 Exists method does in one go. This is mainly why I wouldn't recommend using Enums for your specific problem.

Then in the end you have the Main sub which simply gathers the input from the user and calls the Exists method. It shows a Message Box with either true or false which indicates if the String exists as an Enum type.

                        
这篇关于检查值是否是列表的成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！