VBA InStr Function: Simple and Excel Sheet Examples

What is InStr function in VBA?

The InStr is a text/string function which is used to search in a string.

It returns (as Variant/Long ) the first occurrence of the searched text/string.

We will show you simple examples of the VBA InStr function as well as with Excel data.

Following is the syntax:

So, InStr function takes two strings. See the table below for all arguments:

Argument	Description
String1	This is the text/string that you want to search in the given string2.
String2	This is the source string where you want to perform the search.
Start	The Start argument specifies where to start searching in string2. (Optional)
Compare	This is an optional argument. It specifies which type of comparison to perform.

Possible values for the compare argument are:

vbUseCompareOption	-1
vbBinaryCompare	0
vbTextCompare	1
vbDatabaseCompare	2

This argument is explained in the last example.

An example of InStr function

Let us start with a simple example of using two strings.

We will assign values to these strings after declaration and use both in the InStr function.

The result is displayed in the message box:

VBA code:

Sub InStr_ex()

Dim str1, str2

str1 = "VBA/Excel Tutorials"

str2 = "T"

MsgBox InStr(str1, str2)

End Sub

Result:

So, we searched for the letter ‘T’ in the str1 string. You can see, its first occurrence is at number 11 – that is the returned value of inStr function.

Using start argument

We have this source string:

We will search = “VBA”

The search will start at position 7.

VBA code:

Sub InStr_ex()

Dim str1, str2
Dim res

str1 = "VBA is simple and cool. Like VBA!"

str2 = "VBA"

res = InStr(7, str1, str2)

MsgBox "Occurrence of 'VBA' = " & res

End Sub

Result:

If we omitted the start argument i.e.

str1 = "VBA is simple and cool. Like VBA!"

str2 = "VBA"

res = InStr(str1, str2)

MsgBox "Occurrence of 'VBA' = " & res

Result:

Is InStr case sensitive function?

Consider this string:

We will search for “T” without the start argument.

VBA code:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"

str2 = "T"
res = InStr(str1, str2)

MsgBox "Occurrence of 'T' = " & res

End Sub

Result:

The result proves that inStr is a case-sensitive function.

However, by using the compare argument you may perform a case-insensitive search. See the section at the end for a comparison argument example.

What if the search term is not found?

The inStr function returns 0 if the searched text/string is not found.

See this example:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"
str2 = "That"


res = InStr(str1, str2)

MsgBox "Occurrence of 'That' = " & res


End Sub

Result:

So, InStr returns 0 if no match is found.

Working with Excel sheet and InStr function

Let us show you the usage of the InStr function in an Excel sheet.

We will use the following data:

In the first example, we will create a range of “Product Name” columns.

The task is to search “Product Name” starting with the letter “T” by using the InStr function and make all those products color red.

See the code and resultant sheet below:

Sub InStr_ex()

Dim Rng As Range

Dim cell As Range

Dim res

'Creating range of cells

Set Rng = Range("B2:B11")


For Each cell In Rng

    res = InStr(cell, "T")

    If Not res = 0 Then

       'Applying color to Cell which name contains T

       cell.Font.ColorIndex = 3

    End If

Next cell

End Sub

Result:

Making “Out of Stock” rows bold and red

A little more complex example.

If you look at the sample sheet, it contains a Status column with “Out of Stock” values for three products.

The task is to make all those rows red, bold, and italic whose status is “Out of Stock” by using the inStr function.

First, have a look at the solution and then a little explanation of how it worked:

VBA code:

Sub InStr_ex()

Dim Rng As Range

Dim cell As Range

Dim res


'Creating range of cells

Set Rng = Range("D2:D11")

For Each cell In Rng

    res = InStr(cell, "Out")

    If Not res = 0 Then

       'Applying color, bold and italic to to whole row

       cell.EntireRow.Font.Bold = True

       cell.EntireRow.Font.Italic = True

       cell.EntireRow.Font.ColorIndex = 3

    End If

Next cell

End Sub

Result:

The example of compare argument / case-insensitive search

The compare argument is useful in different scenarios.

In one of the above examples, we saw the InStr function is case-sensitive.

See the example below:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"

str2 = "T"

res = InStr(1, str1, str2, vbTextCompare)

MsgBox "Occurrence of 'T' = " & res

End Sub

Result:

You see, we searched for the capital ‘T’ and it returned the first occurrence for ‘t’.

You may learn more about InStr function and compare options here:

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function