The VBA InStrRev function returns the index position of the first occurrence of a string in a provided string, looking from the right end of the string. The return result is the placement of the string looking from the front.
'InStrRev
Debug.Print InStrRev ("abcdefabc", "a") 'Result: 7
'Because it found it's first "a" as the 3rd from the end of the string.
'The result is the position of the second "a" looking from the front of the string
'InStr
Debug.Print InStr ("abcdefabc", "a") 'Result: 1
'Because it found it's first "a" as the 1st from the start of the string.
Use InStr to return the index position of the first occurrence looking from the beginning end of the string (the left).
VBA InStrRev Syntax
The syntax for the InStrRev function in VBA:
InStrRev ( string, substring [, start [ , compare] ] )
Parameters
string
The string to search within.
substring
The substring that you want to find.
start
Optional. The starting index position for the InStr function. If omitted, the search will begin at index position -1 which is the last character index position in a string (hypothetically – the length of the string + 1).
compare
Optional. The type of comparison to be performed when searching for substring. Can be one of the following constants:
| Constant | Value | Description |
|---|---|---|
| vbUseCompareOption | -1 | Uses option compare |
| vbBinaryCompare | 0 | Binary comparison (distinguishes letter case) |
| vbTextCompare | 1 | Textual comparison (ignores letter case) |
Other Notes
The InStrRev function will return 0 if substring is not found within string.
Strings character positions in VBA are indexed starting with 1. See examples below.
Example usage
The InStrRev function can only be used in VBA code. Let’s look at some VBA InStrRev function examples:
InStrRev "Hello World!", "World" 'Result: 7 InStrRev "Wow, Wow, Wow", "Wow" 'Result: 11 InStrRev "Hi There!", "There", -1, vbTextCompare 'Result: 4 InStrRev "Hi There!", "there", -1, vbBinaryCompare 'Result: 0 InStr 1, "Hi There!", "There", -1, vbBinaryCompare) 'Result: 4
