VBA Tip: Position in an array

To find the position of a value in an array, copy this function into a module:

Function array_pos(my_array, my_value)
    
    'https//www.excel-pratique.com/en/vba_tricks/position-in-array-function
    
    array_pos = -1
    
    For ii = LBound(my_array) To UBound(my_array)
        If my_array(ii) = my_value Then 'If value found
            array_pos = ii
            Exit For
        End If
    Next
    
End Function

The array_pos function will then return the position of the first value found in the array (or -1 if the value was not found in the array).

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 then display its position in a MsgBox.

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

    test_value = 9
    
    MsgBox array_pos(test_array, test_value)

End Sub