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.

Basic syntax

VBA


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

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).

Example

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