Return index of the element in array

[YasserKhalil]

Hello everyone

I have this code

Code: Select all

Sub TestArray()
    Dim a, e
    
    a = Array(5, 15, 4, 7)
    
    For Each e In a
        Debug.Print e
    Next e
End Sub

and I want to return the index of each element in the same order so if I used Debug.Print I can get 1 then 2 then 3 then 4

I can use a variable and increment it .. example using i the inside the loop assign i to be equal to i + 1 and this is acceptable solution
But I wonder if there is a way relying on the array (1d array) itself to get or return its index

[HansV]

An array doesn't have a built-in way to get the index of an item. You could do this:

Code: Select all

Sub TestArray()
    Dim a, i As Long
    
    a = Array(5, 15, 4, 7)
    
    For i = 0 To UBound(a)
        Debug.Print i + 1, a(i)
    Next i
End Sub

[YasserKhalil]

Thanks a lot Mr. Hans for great help
I think this is better than assigning a separate variable for this purpose
Best Regards

[Doc.AElstein]

Here is another way, ( Rem 1 ) … but it has the limitation that it will give the wrong index for all but the first occurrence of any duplicated values.
It uses Match, which returns the “position along”( counting from the left or top) of the first occurrence it finds of what it is looking for, in what it is looking in ….. What it is looking in must be 1 element “wide”. So it can be a 1 Dimension Array or a 1 “column” 2 dimensional Array or a 1 “row” 2 Dimensional Array or a spreadsheet single column or a spreadsheet single row or part of a spreadsheet single column or part of a spreadsheet single row .

“Position along” at first Match is ( What it is looking for is this first argument, This is what it looks in , 0 specifies exact match)
“Position along counting from top or left” = Match ( ThingToSearchFor , 1 Dimensional Array or 1 element “wide” Array or 1 cell “wide” range , 0 )

So as long as your values in the array are unique, then that “position along” is effectively the index

Results of from Rem 1) are: https://imgur.com/r29WIHb" onclick="window.open(this.href);return false;

RstRem1.JPG

Rem 2) is a way to get over the problem with duplicate values… but it is a bit messy.
Results of from Rem 2) are: https://imgur.com/FSX4DDd" onclick="window.open(this.href);return false;

RstRem2.JPG

Code: Select all

Sub TestieRay()
Rem 0
Dim ieRay() As Variant, Ray As Variant, strungOut As String
Rem 1
 Let ieRay() = Array(5, 15, 4, 7)
    For Each Ray In ieRay()
     Debug.Print Ray, Application.Match(Ray, ieRay(), 0)
     Let strungOut = strungOut & Ray & vbTab & Application.Match(Ray, ieRay(), 0) & "       " & vbCrLf
    Next Ray
 MsgBox prompt:=strungOut, Title:="Element     Index"
 Let strungOut = ""
Rem 2
Let ieRay() = Array(5, 15, 5, 7)
Dim strungOutAGen As String
 Let strungOutAGen = Join(ieRay(), " ")
 Dim PrayBack() As String ' ------------------- '_- Split function returns elements of String type
 Dim MeDupyDo() As String: ReDim MeDupyDo(1)
    For Each Ray In ieRay()
     Let PrayBack() = Split(strungOutAGen, " ") '_- Split function returns elements of String type
        If IsError(Application.Match(CStr(Ray), MeDupyDo(), 0)) Then
         Let strungOut = strungOut & Ray & vbTab & Application.Match(CStr(Ray), PrayBack(), 0) & "             " & vbCrLf
         Debug.Print Ray, Application.Match(CStr(Ray), PrayBack(), 0)
        Else
         Let strungOut = strungOut & Ray & vbTab & Application.Match(CStr(Ray), PrayBack(), 0) & " **Dupy Do is" & vbCrLf
         Debug.Print Ray, Application.Match(CStr(Ray), PrayBack(), 0) & " **Dupy Do is"
        End If
      Let MeDupyDo(UBound(MeDupyDo())) = Ray
      ReDim Preserve MeDupyDo(UBound(MeDupyDo()) + 1)
     Let strungOutAGen = Replace(strungOutAGen, PrayBack(LBound(PrayBack())) & " ", Rnd & " ", 1, 1, vbBinaryCompare)
    Next Ray
 MsgBox prompt:=strungOut, Title:="Element     Index"
End Sub

Alan

[YasserKhalil]

Thanks a lot Mr. Alan for this great explanation. You're a legend
It is Mr. Hans' approach which is simpler and it works fine for me