VBA INSTRREV Function

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.

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.
VBA InStrRev Function

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.
VBA InStrRev Function

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

1 thought on “VBA InStrRev Function in Excel”

Leave a Reply