Aniruddah Alam
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:
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. |
Application:
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
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.
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.
Code Syntax:
Sub Instr_Function_CaseInsensitive()
string1 = "Hello World!"
String2 = "world"
x = InStr(1, string1, String2, vbTextCompare)
Debug.Print x
End SubSo, 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.
Code syntax:
Sub Instr_Function_UCase()
string1 = "Hello World!"
String2 = "world"
x = InStr(1, UCase(string1), UCase(String2))
Debug.Print x
End SubThings 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.
Conclusion
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.
