Return Index of an Element in an Array Excel VBA

Dim pos, arr, valarr=Array(1,2,4,5)val = 4pos=Application.Match(val, arr, False)if not iserror(pos) then   Msgbox val & " is at position " & poselse   Msgbox val & " not found!"end if

Updated to show using Match (with .Index) to find a value in a dimension of a two-dimensional array:

Dim arr(1 To 10, 1 To 2)Dim xFor x = 1 To 10    arr(x, 1) = x    arr(x, 2) = 11 - xNext xDebug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

EDIT: it's worth illustrating here what @ARich pointed out in the comments - that using Index() to slice an array has horrible performance if you're doing it in a loop.

In testing (code below) the Index() approach is almost 2000-fold slower than using a nested loop.

Sub PerfTest()    Const VAL_TO_FIND As String = "R1800:C8"    Dim a(1 To 2000, 1 To 10)    Dim r As Long, c As Long, t    For r = 1 To 2000        For c = 1 To 10            a(r, c) = "R" & r & ":C" & c        Next c    Next r    t = Timer    Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t    ' >> 0.00781 sec     t = Timer    Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t    ' >> 14.18 secEnd SubFunction FindLoop(arr, val) As Boolean    Dim r As Long, c As Long    For r = 1 To UBound(arr, 1)    For c = 1 To UBound(arr, 2)        If arr(r, c) = val Then            FindLoop = True            Exit Function        End If    Next c    Next rEnd FunctionFunction FindIndex(arr, val)    Dim r As Long    For r = 1 To UBound(arr, 1)        If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then            FindIndex = True            Exit Function        End If    Next rEnd Function

array of variants:

    Public Function GetIndex(ByRef iaList() As Variant, ByVal value As Variant) As Long    Dim i As Long     For i = LBound(iaList) To UBound(iaList)      If value = iaList(i) Then       GetIndex = i       Exit For      End If     Next i    End Function

a fastest version for integers (as pref tested below)

    Public Function GetIndex(ByRef iaList() As Integer, ByVal value As Integer) As Integer     Dim i As Integer     For i = LBound(iaList) To UBound(iaList)      If iaList(i) = value Then: GetIndex = i: Exit For:     Next i    End Function' a snippet, replace myList and myValue to your varible names: (also have not tested)

a snippet, lets test the assumption the passing by reference as argument means something. (the answer is no) to use it replace myList and myValue to your variable names:

  Dim found As Integer, foundi As Integer ' put only once  found = -1  For foundi = LBound(myList) To UBound(myList):   If myList(foundi) = myValue Then    found = foundi: Exit For   End If  Next  result = found

to prove the point I have made some benchmarks

here are the results:

---------------------------Milliseconds---------------------------result0: 5 ' just empty loopresult1: 2702  ' function variant arrayresult2: 1498  ' function integer arrayresult3: 2511 ' snippet variant arrayresult4: 1508 ' snippet integer arrayresult5: 58493 ' excel function Application.Match on variant arrayresult6: 136128 ' excel function Application.Match on integer array---------------------------OK   ---------------------------

a module:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long#If VBA7 Then    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems#Else    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems#End If    Public Function GetIndex1(ByRef iaList() As Variant, ByVal value As Variant) As Long    Dim i As Long     For i = LBound(iaList) To UBound(iaList)      If value = iaList(i) Then       GetIndex = i       Exit For      End If     Next i    End Function'maybe a faster variant for integers    Public Function GetIndex2(ByRef iaList() As Integer, ByVal value As Integer) As Integer     Dim i As Integer     For i = LBound(iaList) To UBound(iaList)      If iaList(i) = value Then: GetIndex = i: Exit For:     Next i    End Function' a snippet, replace myList and myValue to your varible names: (also have not tested)    Public Sub test1()     Dim i As Integer     For i = LBound(iaList) To UBound(iaList)      If iaList(i) = value Then: GetIndex = i: Exit For:     Next i    End SubSub testTimer()Dim myList(500) As Variant, myValue As VariantDim myList2(500) As Integer, myValue2 As IntegerDim nFor n = 1 To 500myList(n) = nNextFor n = 1 To 500myList2(n) = nNextmyValue = 100myValue2 = 100Dim oPMSet oPM = New PerformanceMonitorDim result0 As LongDim result1 As LongDim result2 As LongDim result3 As LongDim result4 As LongDim result5 As LongDim result6 As LongDim t As LongDim a As Longa = 0Dim i't = GetTickCountoPM.StartCounterFor i = 1 To 1000000Nextresult0 = oPM.TimeElapsed() '  GetTickCount - ta = 0't = GetTickCountoPM.StartCounterFor i = 1 To 1000000a = GetIndex1(myList, myValue)Nextresult1 = oPM.TimeElapsed()'result1 = GetTickCount - ta = 0't = GetTickCountoPM.StartCounterFor i = 1 To 1000000a = GetIndex2(myList2, myValue2)Nextresult2 = oPM.TimeElapsed()'result2 = GetTickCount - ta = 0't = GetTickCountoPM.StartCounterDim found As Integer, foundi As Integer ' put only onceFor i = 1 To 1000000found = -1For foundi = LBound(myList) To UBound(myList): If myList(foundi) = myValue Then  found = foundi: Exit For End IfNexta = foundNextresult3 = oPM.TimeElapsed()'result3 = GetTickCount - ta = 0't = GetTickCountoPM.StartCounterFor i = 1 To 1000000found = -1For foundi = LBound(myList2) To UBound(myList2): If myList2(foundi) = myValue2 Then  found = foundi: Exit For End IfNexta = foundNextresult4 = oPM.TimeElapsed()'result4 = GetTickCount - ta = 0't = GetTickCountoPM.StartCounterFor i = 1 To 1000000a = pos = Application.Match(myValue, myList, False)Nextresult5 = oPM.TimeElapsed()'result5 = GetTickCount - ta = 0't = GetTickCountoPM.StartCounterFor i = 1 To 1000000a = pos = Application.Match(myValue2, myList2, False)Nextresult6 = oPM.TimeElapsed()'result6 = GetTickCount - tMsgBox "result0: " & result0 & vbCrLf & "result1: " & result1 & vbCrLf & "result2: " & result2 & vbCrLf & "result3: " & result3 & vbCrLf & "result4: " & result4 & vbCrLf & "result5: " & result5 & vbCrLf & "result6: " & result6, , "Milliseconds"End Sub

a class named PerformanceMonitor

Option ExplicitPrivate Type LARGE_INTEGER    lowpart As Long    highpart As LongEnd TypePrivate Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As LongPrivate Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As LongPrivate m_CounterStart As LARGE_INTEGERPrivate m_CounterEnd As LARGE_INTEGERPrivate m_crFrequency As DoublePrivate Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#Private Function LI2Double(LI As LARGE_INTEGER) As DoubleDim Low As Double    Low = LI.lowpart    If Low < 0 Then        Low = Low + TWO_32    End If    LI2Double = LI.highpart * TWO_32 + LowEnd FunctionPrivate Sub Class_Initialize()Dim PerfFrequency As LARGE_INTEGER    QueryPerformanceFrequency PerfFrequency    m_crFrequency = LI2Double(PerfFrequency)End SubPublic Sub StartCounter()    QueryPerformanceCounter m_CounterStartEnd SubProperty Get TimeElapsed() As DoubleDim crStart As DoubleDim crStop As Double    QueryPerformanceCounter m_CounterEnd    crStart = LI2Double(m_CounterStart)    crStop = LI2Double(m_CounterEnd)    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequencyEnd Property

Here's another way:

Option Explicit' Just a little test stub. Sub Tester()    Dim pList(500) As Integer    Dim i As Integer    For i = 0 To UBound(pList)        pList(i) = 500 - i    Next i    MsgBox "Value 18 is at array position " & FindInArray(pList, 18) & "."    MsgBox "Value 217 is at array position " & FindInArray(pList, 217) & "."    MsgBox "Value 1001 is at array position " & FindInArray(pList, 1001) & "."End SubFunction FindInArray(pList() As Integer, value As Integer)    Dim i As Integer    Dim FoundValueLocation As Integer    FoundValueLocation = -1    For i = 0 To UBound(pList)        If pList(i) = value Then            FoundValueLocation = i            Exit For        End If    Next i    FindInArray = FoundValueLocationEnd Function