Return Index of an Element in an Array Excel VBA

Question

I have an array prLst that is a list of integers  The integers are not sorted  because their position in the array represents a particular column on a spreadsheet   I want to know how I find a particular integer in the array  and return its index     There does not seem to be any resource on showing me how without turning the array into a range on the worksheet  This seems a bit complicated   Is this just not possible with VBA

User · Answer

Taking care of whether the array starts at zero or one  Also  when position 0 or 1 is returned by the function  making sure that the same is not confused as True or False returned by the function   Function array return index arr As Variant  val As Variant  Optional array start at zero As Boolean   True  As Variant  Dim pos pos   Application Match val  arr  False   If Not IsError pos  Then     If array start at zero   True Then         pos   pos - 1          initializing array at 0     End If    array return index   pos Else    array return index   False End If  End Function  Sub array return index test   Dim pos  arr  val  arr   Array 1  2  4  5  val   1   When array starts at zero pos   array return index arr  val  If IsNumeric pos  Then MsgBox  Array starting at 0  Value found at      amp  pos Else MsgBox  Not found  End If   When array starts at one pos   array return index arr  val  False  If IsNumeric pos  Then MsgBox  Array starting at 1  Value found at      amp  pos Else MsgBox  Not found  End If    End Sub

User · Answer

To return the position of an element within any-dimension array    Returns 0 if the element is not in the array  and -1 if there is an error   Public Function posInArray ByVal itemSearched As Variant  ByVal aArray As Variant  As Long   Dim pos As Long  item As Variant    posInArray   -1   If IsArray aArray  Then       If not IsEmpty aArray  Then           pos   1           For Each item In aArray               If itemSearched   item Then                   posInArray   pos                   Exit Function               End If               pos   pos   1           Next item           posInArray   0       End If   End If  End Function

User · Answer

The only   amp  even though cumbersome but yet expedient   relatively quick  way I can do this  is to concatenate the any-dimensional array  and reduce it to 1 dimension  with    column number       as the delimiter    amp  use a single-cell result multiple lookupall macro function on the this 1-d column     amp  then index match to pull out the positions   usuing multiple find match   That way you get all matching occurrences of the element string your looking for  in the original any-dimension array  and their positions  In one cell   Wish I could write a macro   function for this entire process  It would save me more fuss

User · Answer

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 loop  result1  2702    function variant array  result2  1498    function integer array  result3  2511   snippet variant array  result4  1508   snippet integer array  result5  58493   excel function Application Match on variant array  result6  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 Sub   Sub testTimer    Dim myList 500  As Variant  myValue As Variant Dim myList2 500  As Integer  myValue2 As Integer Dim n  For n   1 To 500 myList n    n Next  For n   1 To 500 myList2 n    n Next  myValue   100 myValue2   100   Dim oPM Set oPM   New PerformanceMonitor Dim result0 As Long Dim result1 As Long Dim result2 As Long Dim result3 As Long Dim result4 As Long Dim result5 As Long Dim result6 As Long  Dim t As Long  Dim a As Long  a   0 Dim i  t   GetTickCount oPM StartCounter For i   1 To 1000000  Next result0   oPM TimeElapsed      GetTickCount - t  a   0   t   GetTickCount oPM StartCounter For i   1 To 1000000 a   GetIndex1 myList  myValue  Next result1   oPM TimeElapsed    result1   GetTickCount - t   a   0   t   GetTickCount oPM StartCounter For i   1 To 1000000 a   GetIndex2 myList2  myValue2  Next result2   oPM TimeElapsed    result2   GetTickCount - t    a   0   t   GetTickCount  oPM StartCounter Dim found As Integer  foundi As Integer   put only once For i   1 To 1000000 found   -1 For foundi   LBound myList  To UBound myList    If myList foundi    myValue Then   found   foundi  Exit For  End If Next a   found Next result3   oPM TimeElapsed    result3   GetTickCount - t    a   0   t   GetTickCount  oPM StartCounter For i   1 To 1000000 found   -1 For foundi   LBound myList2  To UBound myList2    If myList2 foundi    myValue2 Then   found   foundi  Exit For  End If Next a   found Next result4   oPM TimeElapsed    result4   GetTickCount - t   a   0   t   GetTickCount oPM StartCounter For i   1 To 1000000 a   pos   Application Match myValue  myList  False  Next result5   oPM TimeElapsed    result5   GetTickCount - t    a   0   t   GetTickCount oPM StartCounter For i   1 To 1000000 a   pos   Application Match myValue2  myList2  False  Next result6   oPM TimeElapsed    result6   GetTickCount - t   MsgBox  result0     amp  result0  amp  vbCrLf  amp   result1     amp  result1  amp  vbCrLf  amp   result2     amp  result2  amp  vbCrLf  amp   result3     amp  result3  amp  vbCrLf  amp   result4     amp  result4  amp  vbCrLf  amp   result5     amp  result5  amp  vbCrLf  amp   result6     amp  result6     Milliseconds  End Sub   a class named PerformanceMonitor  Option Explicit  Private Type LARGE INTEGER     lowpart As Long     highpart As Long End Type  Private Declare Function QueryPerformanceCounter Lib  kernel32   lpPerformanceCount As LARGE INTEGER  As Long Private Declare Function QueryPerformanceFrequency Lib  kernel32   lpFrequency As LARGE INTEGER  As Long  Private m CounterStart As LARGE INTEGER Private m CounterEnd As LARGE INTEGER Private m crFrequency As Double  Private Const TWO 32   4294967296      256    256    256    256   Private Function LI2Double LI As LARGE INTEGER  As Double Dim Low As Double     Low   LI lowpart     If Low  lt  0 Then         Low   Low   TWO 32     End If     LI2Double   LI highpart   TWO 32   Low End Function  Private Sub Class Initialize   Dim PerfFrequency As LARGE INTEGER     QueryPerformanceFrequency PerfFrequency     m crFrequency   LI2Double PerfFrequency  End Sub  Public Sub StartCounter       QueryPerformanceCounter m CounterStart End Sub  Property Get TimeElapsed   As Double Dim crStart As Double Dim crStop As Double     QueryPerformanceCounter m CounterEnd     crStart   LI2Double m CounterStart      crStop   LI2Double m CounterEnd      TimeElapsed   1000     crStop - crStart    m crFrequency End Property

User · Answer

Is this what you are looking for   public function GetIndex byref iaList   as integer  byval iInteger as integer  as integer  dim i as integer   for i lbound ialist  to ubound ialist    if iInteger ialist i  then    GetIndex i    exit for   end if  next i  end function

User · Answer

Dim pos  arr  val  arr Array 1 2 4 5  val   4  pos Application Match val  arr  False   if not iserror pos  then    Msgbox val  amp    is at position    amp  pos else    Msgbox val  amp    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 x  For x   1 To 10     arr x  1    x     arr x  2    11 - x Next x  Debug 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   amp  r  amp    C   amp  c         Next c     Next r      t   Timer     Debug Print FindLoop a  VAL TO FIND   Timer - t        gt  gt  0 00781 sec       t   Timer     Debug Print FindIndex a  VAL TO FIND   Timer - t        gt  gt  14 18 sec  End Sub  Function 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 r End Function  Function 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 r End Function

User · Answer

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    amp  FindInArray pList  18   amp          MsgBox  Value 217 is at array position    amp  FindInArray pList  217   amp          MsgBox  Value 1001 is at array position    amp  FindInArray pList  1001   amp       End Sub  Function 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   FoundValueLocation  End Function

[arrays] Return Index of an Element in an Array Excel VBA

Examples related to arrays

Examples related to excel

Examples related to vba

Examples related to indexing

Examples related to find