VBA InStr function

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

InStr Function Description

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.

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

Syntax

The syntax for the InStr function in VBA is:

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

Parameters

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.

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

Simply the best place to learn VBA!