VBA Tip: Position in an Array

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

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

The arrayPos 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 testValue variable in the testArray array and then display its position in a MsgBox.

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

    testValue = 9
    
    MsgBox arrayPos(testArray, testValue)

End Sub