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