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.
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
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] ] )
The string to search within.
The substring that you want to find.
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).
Optional. The type of comparison to be performed when searching for substring. Can be one of the following constants:
|vbUseCompareOption||-1||Uses option compare|
|vbBinaryCompare||0||Binary comparison (distinguishes letter case)|
|vbTextCompare||1||Textual comparison (ignores letter case)|
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.
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