Checking if a value is a member of a list

excel, vba

Solution

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.

Problem

Original source

Related problems

• Matching values in string array
• Hidden features of VBA