Get the reverse index of a value from Array in VBA-CodePudding

I have an integer array.

Dim a as Variant
a = Array(1,2,3,4,1,2,3,4,5)
Dim index as Integer
index = Application.Match(4,a,0) '3

index is 3 here. It returns the index of first occurrence of 4. But I want the last occurrence index of 4.

In python, there is rindex which returns the reverse index. I am new to vba, any similar api available in VBA? Note: I am using Microsoft office Professional 2019 Thanks in advance.

CodePudding user response：

XMATCH() avaibalble in O365 and Excel 2021. Try-

Sub ReverseMatch()
Dim a As Variant
Dim index As Integer

    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)
    index = Application.XMatch(4, a, 0, -1) '-1 indicate search last to first.
    Debug.Print index

End Sub

You can also try below sub.

Sub ReverseMatch()
Dim a As Variant
Dim index As Integer, i As Integer

    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    For i = UBound(a) To LBound(a) Step -1
        If a(i) = 4 Then
            Debug.Print i   1
            Exit For
        End If
    Next i

End Sub

CodePudding user response：

Try the next way, please:

Sub lastOccurrenceMatch()
   Dim a As Variant, index As Integer
    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    index = Application.Match(CStr(4), Split(StrReverse(Join(a, "|")), "|"), 0) '2
    Debug.Print index
End Sub

Or a version not raising an error in case of no match:

Sub lastOccurrenceMatchBetter()
   Dim a As Variant, index As Variant
    a = Array(1, 2, 3, 4, 1, 2, 3, 4, 5)

    index = Application.Match(CStr(4), Split(StrReverse(Join(a, "|")), "|"), 0) '2
    If Not IsError(index) Then
        Debug.Print index
    Else
        Debug.Print "No any match..."
    End If
End Sub

Edited:

The next version reverses the array as it is (without join/split sequence). Just using Index. Just for the sake of playing with arrays:

Sub testReverseArray()
   Dim a As Variant, index As Integer
    a = Array(1, 2, 3, 4, 1, 12, 3, 4, 15)
    a = Application.index(Application.Transpose(a), Array(9, 8, 7, 6, 5, 4, 3, 2, 1), 0)

    Debug.Print Join(a, "|") 'just to visually see the reversed array...
    index = Application.match(4, a, 0)
    debug.print index
End Sub

Now, when I will have some time, I will try to also build the necessary rows array, but not manually and without iteration, also... I (just) hope that it is possible. Now I am very busy to start testing. I only had a revelation about the last way of array reversing...