VBA InStrRev Function

Overview of VBA InStrRev Function:

VBA InStrRev function is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. It returns the position of a substring within a string, searching from right to left. It has two required parameters and two optional parameters. If substring is not found, then function returns the value ‘0’. If Start parameter is Null, then the function returns an error. This function could be used 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:

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 substring 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. Dafault 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 comparision while search is to be performed. Default comparision is ‘vbBinaryCompare’. Here is the comparision table.

VBA Compare	Value	Explanation
vbUseCompareOption	-1	Performs a comparision 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 substring position in within given string, starting at position -1.

'Search for the last occurence 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 occurence 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.

Instructions to use Macro Codes

Here are the step by step instructions to use the VBA InStrRev function in Excel VBA.

• Open an Excel workbook.
• Press Alt+F11 to open VBA Editor window.
• Go to Insert menu and click on module from the available options.
• Copy above specified macros to the VBA editor.
• Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.
List of VBA Functions