How to use InStrRev function in VBA?

In VBA (Visual Basic for Applications), the `InStrRev` function is used to search for a substring within a string, but it starts searching from the end of the string instead of the beginning. It returns the position of the first occurrence of a specified substring in the string, searching from the end to the start. This is essentially the reverse of the `InStr` function.

The syntax for `InStrRev` is as follows:

InStrRev(StringCheck, StringMatch, [Start], [Compare])

Where:

`StringCheck` is the string to search within.
`StringMatch` is the substring you are looking for.
`Start` is an optional parameter that specifies the starting position for the search. If omitted, the default is to start at the last character.
`Compare` is another optional parameter that specifies the type of comparison to perform: `vbBinaryCompare` (default, case-sensitive) or `vbTextCompare` (case-insensitive).

Here is an example of how to use `InStrRev` in VBA:

Sub ExampleInStrRev()
    Dim FullText As String
    Dim SearchText As String
    Dim Position As Integer
    
    FullText = "Hello, world! Programming world is fun."
    SearchText = "world"
    
    ' Search for the substring "world" starting from the end of the string
    Position = InStrRev(FullText, SearchText)
    
    ' If Position > 0, the substring was found
    If Position > 0 Then
        MsgBox "The last occurrence of the word '" & SearchText & "' is at position " & Position
    Else
        MsgBox "The word '" & SearchText & "' was not found."
    End If
End Sub

When this macro runs, it will display a MessageBox that indicates the position of the last occurrence of the word “world” within the `FullText` string.

Remember that the positions of characters in a string in VBA are 1-based, not 0-based. The first character has the position 1, the second has the position 2, and so on. If the `InStrRev` function doesn’t find the substring, it returns 0.

facebooklinkedin