VBA InStr

Excel VBA InStr Function

Instr in VBA is used to find out the position of a given substring in a string after we specify the comparison method to the function, there are four different comparison methods for this function, Instr is a string function but the output returned by the function is numeric so the output this function is in an integer variable.

String in VBA is nothing but a series of characters i.e., all the texts that are supplied with double quotes are treated as strings. InStr Function is a built-in text function used to manipulate Strings. For example – If you want to extract a substring from a sentence or if you want to apply font design changes to a particular string in a series of characters, or if you want to find the position of a character and many other possibilities, you can use InStr.

VBA InStr Function

Syntax

Instr Function

It has 4 arguments, as shown in the below image.

  • [Start]: This is not mandatory. This is the numerical value we need to specify from which position of the string Instr function starts to look for the supplied text. For example: if you want to search the character “a” in the word “Bangalore” from the 3rd position, we need to tell the Instr function starting position as 3. So from the 3rd position, the character “a” is in the 5th position. If you ignore this parameter, the default value is 1.
  • String 1: This is the actual string we are supplying i.e., from this text, we are trying to find the substring. For example, if you are looking for the string “a” in “Bangalore,” String 1 in Bangalore.
  • String 2: This is nothing but what is the string we are searching for. For example, if you are looking for the string “a” in “Bangalore,” String 2 is a.
  • [Compare]: This is again an optional argument. There are three kinds of options available in the [compare] argument.

vba inStr

  • vbBinaryCompare: This is nothing but a case sensitive search of the substring (string 2) in String 1. For example, if we are searching for “a” in the word “Bangalore,” Instr would return 2 as a result, and if you are searching for “A” in in the word “Bangalore,” Instr would return 0 as the result because the supplied string is upper case value.

We can also put zero (0) as the argument.

vbTextCompare: This is not a case sensitive search of the string 2 in string 1. For example, if we are searching for “a” in the word “Bangalore,” Instr would return 2 as a result, and if you are searching for “A” in the word “Bangalore,” Instr would return 2 as well. Logic is A=a, B=b, C=c etc.….

We can also put one (1) as the argument.

vbDatabaseCompare: This is used to compare the information from your database i.e., Microsoft Access database.

We can also put one (-1) as the argument.

Top 5 Examples of using VBA Instr Function

You can download this VBA Instr Function Excel Template here – VBA Instr Function Excel Template

Example #1

Let’s get started with the first example. In the word Bangalore, find the position of the character a.

The below code will perform the task for us.

Code:

Sub Instr_Example1()

Dim i As Variant

i = InStr("Bangalore", "a")

MsgBox i

End Sub

Now run the above-given VBA code using the F5 Key, or you can also run this code manually, as shown in the below-given screenshot.

VBA InStr Example 1-2

Output:

VBA Instr Example 1-2

Example #2

Now in the word Bangalore, find the position of the character “a” from the third position.

The below code will perform the task for us.

Code:

Sub Instr_Example2()

Dim i As Variant

i = InStr(3, "Bangalore", "a")

MsgBox i

End Sub

In order to run the above-given code, you can use the F5 key, or you can also run this code manually, as shown in the below-given screenshot.

VBA InStr Example 2-2

Output:

VBA Instr Example 2-2

Now in the above image, look at the difference from the previous code. Since we have mentioned the starting position of the character as 3, it ignored the first character “a” at the 2nd position.

Example #3

Now we will see case sensitive search. In the word Bangalore, find the letter “A.”

For this, we need to supply the compare argument as vbBinaryCompare.

Code:

Sub Instr_Example3()

Dim i As Variant

i = InStr(1, "Bangalore", "A", vbBinaryCompare)

MsgBox i

End Sub

Now run this code using the F5 Key, or you can also run this code manually.

VBA InStr Example 3-2

Output:

VBA Instr Example 3-2

Since we have supplied the compare argument as vbBinaryCompare Instr function returned the result as zero because there are no uppercase letter “A” exists.

Example #4

Now we will see one more case sensitive search. In the word Bangalore, find the letter “A.” The previous example returned the result as zero.

In order to overcome the case sensitive approach here, we need to supply the compare argument as vbTextCompare.

Code:

Sub Instr_Example4()

Dim i As Variant

i = InStr(1, "Bangalore", "A", vbTextCompare)

MsgBox i

End Sub

Now Run, this code using the F5 Key, or you can also run this code manually.

VBA InStr Example 4-2

Output:

Example 4-2

Example #5

Now we will see the advanced level of Instr function. If you have many sheets that have a more or less similar name and you want to hide all those sheets at once, we can use the below code to hide a specific sheet.

For example, I have 5 sheets named Summary 1, Summary 2, Summary 3, Summary 4, and Data Sheet.

Example 5

Now I want to hide all those sheets which have the word “Summary.” Use the below code to hide all the sheets which have the word Summary in its name.

Code:

Sub To_Hide_Specific_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If InStr(Ws.Name, "Summary") > 0 Then
Ws.Visible = xlSheetVeryHidden
End If

Next Ws
'InStr function looks for word or phrase in the sheet name
'If it finds then it will be hidden
End Sub

Now Run, this code using the F5 Key, or you can also run this code manually and see the Output.

Example 5-2

Similarly, in order to unhide all those sheets, use the below code.

Code:

Sub To_UnHide_Specific_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If InStr(Ws.Name, "Summary") > 0 Then
Ws.Visible = xlSheetVisible
End If

Next Ws
'InStr function looks for word or phrase in the sheet name
'If it finds then it will be hidden
End Sub

Now Run, this code using the F5 Key, or you can also run this code manually and see the Output.

Example 5-3

Things to Remember

  • Instr is a case sensitive function. In order to eliminate this case sensitive issue, you need to supply the [compare] argument as.
  • If you are searching for case sensitive character, you need to supply the [compare] argument as, by default, VBA takes this as the argument even if you do not supply.
  • Instr is a VBA function, so that you cannot use it in an excel worksheet like other built-in formulas.
  • If the function cannot find the String 2, the result will be zero.

Recommended Articles

This has been a guide to VBA InStr. Here we learn how to use the InStr function to find the position of a letter and to hide & unhide sheets along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>