VBA Instr Case Insensitive (2 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am going to show you how to make the VBA Instr function case insensitive in Excel. First, we will look at the syntax and applications of the VBA Instr function. Later, we will learn how to make the function case insensitive. So let’s start our journey.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

VBA Instr Function: Syntax and Applications

The VBA Instr function is used to determine the position of a substring inside another string. It returns the position of the first occurrence of the substring inside the main string. The syntax of the Instr function is given below.


Syntax of Instr Function

InStr([ start ], string1, string2, [ compare ])

The details of the arguments of the function are given below.

Argument Type Remarks
Start I.Optional if the Compare argument is specified.

II.Required if the Compare argument is not specified.

It takes numerical value. This argument specifies the starting position from which the substring will be searched within the string. The default value is 1.
String1 Required This is the string within which a substring will be searched.
String2 Required This is the substring to be searched.
Compare Optional It specifies the type of comparison. It can have either of the following 3 values.

I.vbBinaryCompare (Performs a binary comparison)

II.vbTextCompare(Performs a textual comparison)

III.vbDatabaseCompare (Only applicable in MS Access)

If omitted, the type of comparison is determined by the global default comparison Option Compare statement. Most often, the default value is vbBinaryCompare.


In the following code, we determine the first occurrence of the word “World” in the string “Hello World!”.

Sub Instr_Function()
string1 = "Hello World!"
String2 = "World"
x = InStr(1, string1, String2)
Debug.Print x
End Sub

Example Code of Instr Function

As expected, this results in 7.

If we look closely at the code, we can see that the Compare argument was left empty. Hence, the Instr function was case-sensitive. For example, if we looked for the substring “world” instead of “World”, the result would be 0. This is because the Instr function considers “world” and “World” as different strings.

Instr Function Acting as Case Sensitive

To make the VBA Instr function case insensitive, you can use the following methods.

VBA Instr Function Case Insensitive: 2 Suitable Examples

1. Using Compare Argument to Make VBA Instr Function Case Insensitive

To use the VBA Instr Function as case insensitive, we need to explicitly set the Compare argument to vbTextCompare. In the above example, if we modify the code in the following way, we get our desired result which is 7.

VBA Instr Function Acting as Case InSensitive

Code Syntax:

Sub Instr_Function_CaseInsensitive()
string1 = "Hello World!"
String2 = "world"
x = InStr(1, string1, String2, vbTextCompare)
Debug.Print x
End Sub

So, whenever we need to make the VBA Instr function case insensitive, we should use the vbTextCompare as the Compare argument.

2. Using UCase Function to Make VBA Instr Function Case Insensitive

Instead of explicitly using the Compare argument, we can use the UCase function to get case insensitive response from the VBA Instr function. Therefore, our modified code will be like this.

Use of UCase function to Make VBA Instr Case Insensitive

Code syntax:

Sub Instr_Function_UCase()
string1 = "Hello World!"
String2 = "world"
x = InStr(1, UCase(string1), UCase(String2))
Debug.Print x
End Sub
Here, we can see that we have used the UCase function in String1 and String2 arguments. The Ucase function converts both String1 and String2 into all uppercase strings. Hence, when both are compared by the Instr function, the case becomes irrelevant. Therefore, we got our expected result, which is 7.

Things to Remember

  • In method 2, Instead of using the UCase function, you can use the LCase function as well.
  • While inserting the string directly into the Instr function, don’t forget to contain them inside the quotation mark.

Frequently Asked Questions on VBA Instr Function

  • What does the Instr function return if the substring is not found in the string?

The Instr function gives a return value of 0 if the substring cannot be found.

  • Can I use the Instr function to find multiple occurrences of a substring within a string?

No, the Instr function only gives the location of the substring’s first occurrence. You would need to employ a loop or a different strategy if you wanted to find additional occurrences.

  • How can I use the Instr function to check if a string contains a specific substring?

You can use the Instr function to check if the returned position is greater than 0. If it is greater than 0, it means the substring is found in the string.

  • Are there any other functions similar to Instr that I can use?

Yes, there are other functions like InStrRev (searches for a substring in reverse order), StrReverse (reverses a string), and the Like operator (performs pattern matching) that can be used for similar purposes.


That is the end of this article regarding making the VBA Instr function case insensitive. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.


Aniruddah Alam
Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF