VBA Tip: Search in an array

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

Function in_array(my_array, my_value)
    in_array = False
    For i = LBound(my_array) To UBound(my_array)
        If my_array(i) = my_value Then 'If value found
            in_array = True
            Exit For
        End If
End Function

The in_array 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 "test_value" variable in the "test_array" array and display True or False in a MsgBox:

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

    test_value = 17
    MsgBox in_array(test_array, test_value)

End Sub