VBA Tip: Search in an Array

To check if a value exists in an array, copy this function into a module:

Function inArray(myArray, myValue)
    
    'Source: https://www.excel-pratique.com/en/vba_tricks/search-in-array-function
	
    inArray = False
    
    For i = LBound(myArray) To UBound(myArray)
        If myArray(i) = myValue Then 'If value found
            inArray = True
            Exit For
        End If
    Next
    
End Function

The inArray function will return True or False depending on the result.

Here's a simple example that will look for the presence of the value of the testValue variable in the testArray array and display True or False in a MsgBox:

Sub test()
    
    testArray = Array(23, 67, 38, 17, 854, 9, 92)

    testValue = 17
    
    MsgBox inArray(testArray, testValue)

End Sub