VBA InStr function

The VBA InStr function returns the index position of the first occurrence of a given substring within the provided string. Even if more than one occurance is found, InStr will return only the index position of the first matched occurance.

Here is a simple example of finding the position of the word “Hello” in the “Hi Hello” string:

Debug.Print InStr("Hi Hello", "Hello") 
'Result: 4

Why did we get 4 as a result? See below, as the word “Hello” start at the 4th character.
VBA InStr function example

Use InStrRev to return the index position of the first occurrence looking from the right end of the string.

VBA Reference – String Functions

VBA InStr Syntax

The syntax for the InStr function in VBA is:

InStr( [start], string, substring, [compare] )

Parameters

Parameter Description
start Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
string The string to search within.
substring The substring that you want to find.
compare Optional. The type of comparison to perform when looking for the substring. This can be one of the following VBA Constants:

Constant Value Description
vbUseCompareOption -1 Uses option compare (default)
vbBinaryCompare 0 Binary comparison (distinguishes letter case)
vbTextCompare 1 Textual comparison (ignores letter case)

Other Notes

The InStr function will return 0 if substring is not found within string.

Strings character positions in VBA are indexed starting with 1. See examples below.

Worth reading:

Example usage

The InStr function can only be used in VBA code. Let’s look at some VBA InStr function examples:

InStr "Hello World!", "World"
'Result: 7

InStr "Wow, Wow, Wow", "Wow"
'Result: 1

InStr 1, "Hi There!", "There", vbBinaryCompare
'Result: 4

InStr 1, "Hi There!", "there", vbBinaryCompare
'Result: 0

InStr 1, "Hi There!", "there", vbTextCompare
'Result: 4