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:
- Overview of VBA InStrRev Function
- 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 use Macro Codes
The syntax of the VBA InStrRev function is
InStrRev(StringCheck, StringMatch, [Start], [Compare])
Note: This InStrRev function returns the value.
This function has one mandatory parameter and three optional parameters for the InStrRev Function.
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.
|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.|
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.
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.
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.
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