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)
   
    'https://www.excel-pratique.com/en/vba_tricks/search-in-array-function.php
       
    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
    Next
   
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