How to use InStr Function in Excel VBA Easily (3 Example)

Welcome to Excel Avon

What is InStr Function?

So, today’s post is for How to use InStr Functions in Excel VBA, to find the position of a given substring within a given string in VBA use the VBA InStr function. It returns the first occurrence of the substring as an output. Instr Function is case sensitive. The value of the InStr function is an integer.

To understand for InStr Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

The syntax of Instr function

InStr([start], [string1], [string2], [Compare as Vbcomaparemethod = VbBinaryCompare])

Arguments

Start – [Optional] Start is the position from where the function starts searching.

String1 – string1 is the actual string within which the substring is to be found. string value is required.

String2 – String2 is the substring to be found. substring2 value is required.

Compare – [Optional] Compare is the type of comparison to be performed.

VbBinaryCompare -This is a binary comparison and can be entered as zero (0). It is a case-sensitive search of the string2 in the actual string 1.

VbTextCompare – It is a case-insensitive search of the “string 2” in the “string 1.”

VbUseCompareOption – It is used to do option compare.

To understand InStr Function in Excel VBA, we will write a subroutine for InStr Function in Excel VBA after inserting the module in VBE.

Sub UseInStr()

End Sub

After writing the subroutine we will define the variable

Sub UseInStr()
Dim InpTxt as String
Dim i As Long

End Sub

After we define the variable, For the InpTxt we will write some text.

Sub UseInStr()
Dim InpTxt as String
Dim i As Long

InpTxt = "Raju is a good boy but not a good student"

End Sub

Now we find the value of first ‘good’ of string text we will use InStr function to find the value.

Sub UseInStr()
Dim InpTxt as String
Dim i As Long

InpTxt = "Raju is a good boy but not a good student"
i = InStr(InpTxt, "good")

End Sub

And now we will use debug.Print to print the value result in the Immediate window

Sub UseInStr()
Dim InpTxt as String
Dim i As Long

InpTxt = "Raju is a good boy but not a good student"
i = InStr(InpTxt, "good")

debug.Print i
End Sub

We set the value of first good with the help of InStr function in the Immediate window.

Now we will find the value of second good in the string text, but for second good we will first write the value of first good because now we need value next to first good. Write the InStr function.

Sub UseInStr()
Dim InpTxt as String 
Dim i As Long

InpTxt = "Raju is a good boy but not a good student"
i = InStr(15, InpTxt, "good")

End Sub

And now we will use debug.Print to print the value result in the Immediate window.

Sub UseInStr() 
Dim InpTxt as String 
Dim i As Long 

InpTxt = "Raju is a good boy but not a good student" 
i = InStr(15, InpTxt, "good")
debug.print i
 
End Sub

and click run button then you can see the value of second ‘good’ is printed in the Immediate window.

Suppose you have to print the value of ‘GOOD’, yes you will say that you have already found the value of ‘good’ but if you look, you will see that if you try to find the value of ‘GOOD’ then you will get the value zero. Because the case has changed, let’s run the code once and see

Sub UseInStr() 
Dim InpTxt as String 
Dim i As Long 

InpTxt = "Raju is a good boy but not a good student"

i = InStr(15, InpTxt, "GOOD")
debug.print i

End Sub

As you can see the value of ‘GOOD’ is zero.

Now we will use the last parameter of the instr function to compare as, in compare as we will use vbTextCompare

Sub UseInStr() 
Dim InpTxt as String 
Dim i As Long 

InpTxt = "Raju is a good boy but not a good student" 

i = InStr(15, InpTxt, "GOOD", vbTextCompare)
debug.Print i 
End Sub

After using all the four parameters, we will click on the Run button and in the Immediate window we will see the value of ‘GOOD’ printed with this. 

        It is a case-sensitive function. To eliminate this issue, supply the “compare” argument “vbTextCompare.”

You can also see well-explained video here about InStr Function in Excel VBA