Excel VBA InStr Function

VBA InStr Function

The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found else the function returns the value 0.

Syntax

InStr( [Start], String1, String2, [Compare] )

Parameter

Start (optional): This parameter represents an integer argument, representing the position that you want to start the searching. The default value is 1.

String1 (required): This parameter represents the string that you want to search.

String2 (required): This parameter represents the substring that you want to search for.

Compare (optional): This parameter represents the type of comparison to make.

It can take any of the following values:

• vbBinaryCompare: To perform a binary comparison
• vbTextCompare: To perform a text comparison
• vbDatabaseCompare: To perform a database comparison

Return

This function returns the position of a substring within a string, as an integer if the substring is found else it returns 0.

Example 1

Sub InStrFunction_Example1()
 'Find the text "rld" within the string "Hello World", starting from position 1.
 Dim pos As Integer
 position = InStr("Hello World", "rld")
 ' position now has the value 9.
 MsgBox (position)
 End Sub 

Output

Example 2

Sub InStrFunction_Example2()
 'Find the text "rld" within the string "Hello World", starting from position 10.
 Dim pos As Integer
 'specifying the starting position as 10
 position = InStr(10, "Hello World", "rld")
 ' position now has the value 0.
 MsgBox (position)
 End Sub 

Output