VBA InStrRev Function: The InStrRev function in Excel VBA returns an integer representing the position of a substring within the specified string if the substring is fount else it returns the value 0. It searches the string from right to left (i.e. from the end to the start of the string).

Syntax

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

Parameter

StringCheck (required) – This parameter represents the string you want to search.

StringMatch (required)- This parameter represents the substring you want to search for.

Start (optional)- This parameter represents an integer argument, representing the position that you want to start the searching. The default value is -1 (search starts from the end of the string).

Compare (optional) - This parameter represents the type of comparison to make.

It can take any of the following values:

vbBinaryCompare: To perform a binary comparison
vbTextCompare: To perform a text comparison
vbDatabaseCompare: To perform a database comparison

Return

This function returns an integer representing the position of a substring within the specified string if the substring is fount else it returns the value 0.

Example 1

Sub InStrRev_Example1()
 Dim pos As Integer
 'returns an integer representing the position of a substring within a string
 rev = InStrRev("VBA is an easy programming language", "easy", 17)
 'This returns the result 10.
 ActiveCell.Value = rev
 End Sub

Output

Example 2

Sub InStrRev_Example2()
 'Finding the position of the last space that occurs within the string before the character 17.
 Dim rev As Integer
 rev = InStrRev("VBA is an easy programming language", " ", 17)
 ' This returns the result 15.
 ActiveCell.Value = rev
 End Sub

Output

Example 3

Sub InStrRev_Example3()
 'Find the last occurrence of the "ZZ" within the string.
 Dim rev As Integer
 rev = InStrRev("VBA is an easy programming language", "ZZ")
 'The text "ZZ" is not found and so the function returns the value 0.
 ActiveCell.Value = rev
 End Sub

Output

Excel VBA InStrRev Function