VBA InStrRev Function in Excel

VBA InStrRev function in Excel is categorizes a Text/String function in VBA. It is a built-in function in MS Office Excel. It returns the position of a sub-string within a string, searching from right to left. It has two required parameters and two optional parameters. If sub-string is not found, then function returns the value ‘0’. If Start parameter is Null, then the function returns an error.

This function use as a VBA function and can’t be used as a Excel Worksheet function. The InStrRev function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA InStrRev function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the InStrRev function, where we can use this InStrRev function and real-time examples in VBA.

Table of Contents:

Overview
Syntax of VBA InStrRev Function
Parameters or Arguments
Where we can apply or use the VBA InStrRev Function?
Example 1: Search for the last occurrence of word “help” in a string
Example 2: Search for the last occurrence of an empty space in a string
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of VBA InStrRev Function

The syntax of the VBA InStrRev function is

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

Note: This InStrRev function returns the value.

Parameters or Arguments:

This function has one mandatory parameter and three optional parameters for the InStrRev Function.
Where
StringCheck: The Expression is a mandatory argument. It represents a string that you want to search within.
StringMatch: The Expression is a mandatory argument. It represents a sub-string that you want to search for.
Start: The FirstDayOfWeek is an optional argument. It represents the starting position that you want to start searching from. Default value of this parameter is ‘-1’. If you ignore this argument, then it considers default value.
Compare: The FirstWeekOfYear is an optional argument. It represents the type of comparison while search is to be performed. Default comparison is ‘vbBinaryCompare’. Here is the comparison table.

VBA Compare	Value	Explanation
vbUseCompareOption	-1	Performs a comparison using the ‘option compare’
vbBinaryCompare	0	Performs a Binary comparison
vbTextCompare	1	Performs a Textual comparison
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

Where we can apply or use the VBA InStrRev Function?

We can use this VBA InStrRev function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Search for the last occurrence of word “help” in a string “VBA Help help”

Here is a simple example of the VBA InStrRev function. This below example macro uses the InStrRev function and searches for specified sub-string position in within given string, starting at position -1.

'Search for the last occurrence of word "help" in a string "VBA Help help"
Sub VBA_InStrRev_Function_Ex1()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "VBA Help help"
    
    iPosition = InStrRev(sWord, "help")
    'or
    Position = InStrRev(sWord, "help", -1)
        
    'Display output message
    MsgBox "The word 'help' position in a string : " & iPosition, vbInformation, "VBA InStrRev Function"
    
End Sub

Output:Here is the screen shot of first example output.

Example 2: Search for the last occurrence of an empty space in a string “VBA Help help”

Here is another example of the VBA InStrRev function. This below example macro uses the InStrRev function and finds an empty space position within given string, starting at position 3

'Search for the last occurrence of an empty space in a string "VBA Help help"
Sub VBA_InStrRev_Function_Ex2()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "VBA Help help"
    
    iPosition = InStrRev(sWord, " ")
       
    'Display output message
    MsgBox "The last occurence of an empty space position : " & iPosition, vbInformation, "VBA InStrRev Function"
    
End Sub

Output:Here is the screen shot of second example output.