Excel VBA InStr

Excel VBA InStr. The VBA InStr function is used to find out if a string is contained in another line of text and it returns the first position of its occurrence operations. If the text is not found, it returns (0), otherwise it returns the character in which the text is found.

For instance, if you want to find the position of ‘x’ in ‘Excel’, using the Excel VBA InStr function would return 2.

Syntax of InStr Function

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

• [Start]: An optional parameter. This is an integer value that tells the InStr function the starting position from which it should start searching.
• String1: A required parameter. This is the main string and can also be referred to as the parent string) in which you want to search.
• String2: A required parameter. This is the substring that you are searching for.
• Compare: An optional parameter. It specify the string comparison to be used.

The compare argument settings are analyze in the below table:

• vbUseCompareOption: It performs the comparison with the help of setting of the Option Compare statement.
• vbBinaryCompare: This would do a character-by-character comparison. For example, if you’re searching for ‘x’ in ‘Excel, it will return 2, while if you’re searching for ‘X’ in ‘Excel, it will return 0 as X is in uppercase . If the [Compare] argument is omitted, then 0 is used as the default value.
• vbTextCompare: This would perform a textual comparison. For example, if you search for ‘x’ or ‘X’ in Excel, it would return 2 in both the cases. This function ignores the letter case. You can also use 1 instead of vbTextCompare.
• vbDatabaseCompare: This can be used for Microsoft Access only. It uses the information in the database to perform the comparison.

Also read: Excel VBA Tutorial

Some Important Points are

• InStr is a VBA function and not a worksheet function, so you cannot use it in a worksheet.
• The InStr function returns 0 if it cannot find the substring within the main string.

Examples 1

In this example, I will use the InStr function to find the position of ‘r’ in the word “Hello World”. You can follow the coding in the below screenshot.

Sub Instr_Example_1()

Dim strValue As String

strValue = "Hello World"

MsgBox InStr(strValue, "r")

End Sub

Either you can  run the above code using the F5 key, or you can also run the code manually if you like, and you will get the following output, such as

Example 2

Suppose, I want to find the position of ‘R’ in the sentence “Hello World”. We will use the compare argument as vbBinaryCompare, as shown in the below screenshot

Sub Instr_Example_2()

Dim strValue As String

strValue = "Hello World"

MsgBox InStr(1, strValue, "R", vbBinaryCompare)

End Sub

In conclusion, you can now execute the above program, the supplied compare argument as vbBinaryCompare InStr function returns the zero because there is no uppercase “R” letter exists in the word “Hello World”.