VBA how to check if a variant is represented in array

Question

I have InputBox in which user can enter any value into the text field. I want to check whether this value represented in an array: for example, "asdf" is not in array ValidEntries, which contains integers 0, 1, 2, 3, 4, 5, and 6, but "6" is. How do this?

Answer 1

Here are three possible solutions:

Sub test1()
    Dim ValidEntries As Variant
    Dim i As Long, v As Variant
    Dim valid As Boolean

    ValidEntries = Array(1, 2, 3, 4, 5, 6)
    v = InputBox("Enter something")

    valid = False
    For i = LBound(ValidEntries) To UBound(ValidEntries)
        If v = ValidEntries(i) Then
            valid = True
            Exit For
        End If
    Next i

    MsgBox v & IIf(valid, " is valid", " isn't valid")

End Sub

Sub test2()
    Dim ValidEntries As Variant
    Dim v As Variant
    Dim valid As Boolean

    ValidEntries = Array(1, 2, 3, 4, 5, 6)
    v = InputBox("Enter something")

    valid = Join(ValidEntries, "@") Like "*" & v & "*" And Not (v Like "*@*")

    MsgBox v & IIf(valid, " is valid", " isn't valid")

End Sub

Sub test3()
    Dim ValidEntries As Variant
    Dim i As Long, v As Variant, key As Variant
    Dim valid As Boolean
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

    ValidEntries = Array(1, 2, 3, 4, 5, 6)
    For i = LBound(ValidEntries) To UBound(ValidEntries)
        key = Trim(Str(ValidEntries(i)))
        If Not dict.exists(key) Then dict.Add key, 0
    Next i
    v = InputBox("Enter something")

    valid = dict.exists(v)

    MsgBox v & IIf(valid, " is valid", " isn't valid")

End Sub

The first one is a simple linear search over the array. The second one takes a delimiter which can't appear in any valid entry (you might have to pick a different one) which is used to join the array into a big string and then check if the input is a substring (but not one which spans over a delimiter). The third one creates a dictionary, after which you can check for membership in O(1) time. For validating a single input this probably isn't worth it, but if your code has to repeatedly check for membership in the array it might be worth it.

Answer 2

If you're trying to see if a string representation of an integer exists in an array, just cast the string to an integer using CInt()

Sub Foo()

Dim Bar As Variant

Bar = InputBox("Enter a number:")

If IsNumeric(Bar) Then    '// Check if the entry represents a number
    Bar = CInt(Bar)       '// If it is then cast "Bar" to an Integer
Else
    MsgBox Bar & " Is not a valid number"
    Exit Sub              '// If not then exit the sub routine
End If

'// I've used Evaluate() as an example, you would use whatever method you like for checking the presence in the array.
If Evaluate("ISERROR(MATCH(" & Bar & ",{0,1,2,3,4,5,6},0))") Then
    MsgBox Bar & " does not exist in array."
Else
    MsgBox Bar & " exists in array."
End If

End Sub

---

If however, you're asking how to check if an array entry exists then here are 3 methods to try:

---

Create a string from the array and use Evaluate():

Sub MM_1()
    Const x As Integer = 5 '// Change to any number to test
    y = Array(1, 2, 3, 4, 5, 6)

    If Evaluate("ISERROR(MATCH(" & x & ",{" & Join(y, ",") & "},0))") Then
        MsgBox x & " does NOT exist in array"
    Else
        MsgBox x & " DOES exist in array"
    End If
End Sub  

Or use an ArrayList object:

Sub MM_2()

Const x As Integer = 5 '// Change to any number to test
Set y = CreateObject("System.Collections.ArrayList")

For i = 1 To 6
    y.Add i
Next

If y.Contains(x) Then
    MsgBox x & " DOES exist in the array."
Else
    MsgBox x & " does NOT exist in the array."
End If

End Sub

For a list of set values, use a Select Case statement instead of an array:

Sub MM_3()

Const x As Integer = 5 '// Change to any number to test

Select Case x
    Case 1, 2, 3, 4, 5, 6
        MsgBox x " DOES exist in the array."
    Case Else
        MsgBox x " does NOT exist in the array."
End Select

End Sub