Checking if a value is a member of a list

Question

• 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:

1. 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?
2. 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?

Answer 1

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 vFilter if looking up dog

dog, dogfish

In this particular case the code then checks for an exact match for 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