VBA InStr Function

In VBA we have a function called InStr Function which is used to return the position of the first occurrence of a substring in a string. Which means, for example, we have a name called “Jhon” and we have to find the occurrence of the position of H alphabet in the string. This is what the function is used for.

Formula for InStr Function in Excel VBA

Excel VBA InStr function has the following syntax:

In the syntax Start and compare are optional while string and substring values are required. A string is a string where we want to find our substring and substring is the value which is required to be found in the string. Suppose if we had to use the above syntax on our example of Jhon above the VBA code would be as below,

Instr(“Jhon”,”H”) and the result would be 2 as the position of alphabet H is 2 in the string.

Now we move towards the optional parts of the syntax which are, start and compare. The start is the position of the string from where we want to start the search, whereas compare is of three types,

VbUseCompareOption: (-1) It is used to do option compare.
VbBinaryCompare: (0) It is used to do binary compare.
VbTextCompare: (1) It is used to the textual comparison.

But we need a variable to store the value of the result. As the value is an integer so the variable defined should also be an integer. Now if we have calculated the value we also need to display the result we use msgbox function to display the result.

How to Use Excel VBA InStr Function?

We will learn how to use a VBA InStr Excel function with few examples.

You can download this VBA InStr Excel Template here – VBA InStr Excel Template

VBA InStr Function – Example #1

In the explanation above I used the word Jhon as a string and wanted to find the position of H in the string. I will write the code for the same in VBA and use Instr function to find the position of J in the string.

Follow the below steps to use InStr function in VBA.

Step 1: In the Developer Tab click on Visual Basic to open the VB Editor.

Step 2: Once the module is open, declare a sub-function to start writing the code.

Code:

Sub Compare()

End Sub

Step 3: Now the function is declared, We need to declare a variable to store the position of the substring.

Code:

Sub Compare()

Dim Pos As Integer

End Sub

Step 4: Now we write the InStr function to find out the position of our substring.

Code:

Sub Compare()

Dim Pos As Integer 
Pos = InStr("Jhon", "J")

End Sub

Step 5: To return the position of the substring as a value, we use the MsgBox function and close the subfunction.

Code:

Sub Compare()

Dim Pos As Integer 
Pos = InStr("Jhon", "J")

MsgBox Pos

End Sub

Step 6: If we run the code it shows the position of alphabet J in the string.

Step 7: If we change the substring to check that the code is running properly, let us find the alphabet n in the string.

Code:

Sub Compare()

Dim Pos As Integer 
Pos = InStr("Jhon", "n")

MsgBox Pos

End Sub

Step 8: Now if we run the code we can see the position of alphabet n in the string.

VBA InStr Function – Example #2

Now we have seen what the required arguments of the function do, now let us use the optional ones. The String is “I am a Good Boy but not a Good Runner”. We will find the substring “Good” from the string but ignore the first Good from the sentence.

Follow the below steps to use InStr function in VBA.

Step 1: Open the VB editor from the developers’ tab by clicking on Visual Basic.

Step 2: Start by declaring a sub-function.

Code:

Sub Compare1()

End Sub

Step 3: Now declare a variable to store the position of the substring.

Code:

Sub Compare1()

Dim Pos1 As Integer

End Sub

Step 4: Now we will use InStr function to find the position of the string.

Code:

Sub Compare1()

Dim Pos1 As Integer 
Pos = InStr(12, "I am a Good Boy but bot a Good Runner", "Good", vbBinaryCompare)

End Sub

Step 5: Now we use the Msgbox function to return the value of the string.

Code:

Sub Compare1()

Dim Pos1 As Integer 
Pos = InStr(12, "I am a Good Boy but bot a Good Runner", "Good", vbBinaryCompare)

MsgBox Pos

End Sub

Step 6: Now if we run the code we can get the position of the substring.

Explanation of Excel VBA Instr Function

InStr function in VBA is used to return the position of the value of the substring in a string. What happens if the value is not found? If the value is not found in the string the result displayed is zero.

One thing we should always remember the Instr function is case sensitive. To prove this we move to example 1 where we used instr function to search an alphabet.

What happens if the substring is “N” instead of “n’? let us find out. I have changed the case of the alphabet in the string.

Now if I run the code I will get the result as shown below.

This proves that the instr function is case sensitive.

Things to Remember

The two required fields are String and Substring.
Instr Function is case sensitive.
The value of the InStr function is an integer.