Contents
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
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
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.
Unlock Your Potential
Excel
Access
Power BI
