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
- 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
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 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.
|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.|
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 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.
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.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.