By default in Excel, text comparison is case insensitive.
But, in Excel VBA default text comparison is case sensitive.
In this article, you will see how to remove case-sensitivity in VBA and make case insensitive string comparison in VBA
Example: If you write the below formula or comparison in an Excel cell then the output will be TRUE
=”abc”=”ABC”
But in VBA, the above string comparison will output FALSE.
Two ways are discussed to remove case sensitivity in VBA when comparing text values.
Excel VBA default case sensitivity in action
In the below image you can see the formula in cell C1 outputting True.
For Excel, small and upper case spellings are the same in comparison.
But if the same string values are compared in VBA then output will be False.
After running the below code you will get False in message box.
This behavior can cause issue in VBA If statement evaluation.
Using Option Compare Text statement
One of the easiest and fastest ways to remove case sensitivity in VBA is to declare the Option Compare Text statement.
This statement must be declared at the very top of the module and before any Sub or function.
Option Compare Text is applicable to a module in which it is declared or written not other modules.
So to make a VBA case insensitive comparison in all the modules you have to declare this statement in each module.
This statement directs the VBA to compare text irrespective of the text case in that module.
Now, if you run the test() sub, True will be shown in the message box, considering A1 cell have “abc” value and B1 cell have “ABC” value.
Video (Hindi Speech) : What is Option Compare Text in Excel VBA? | Case insensitive VBA if
https://youtu.be/htplJQs3sR8
Making text case same
You can also make text comparison in VBA code case insensitive without using Option Compare Text declaration.
Here the trick is to make both text values or variables in the same case when comparing them.
You can use either LCase or UCase VBA function to make the text case same.
Below is the example of case-sensitive VBA if, if you run the test2() sub then message box will display “Name not matched”.
Now you can make case insensitive VBA if, just convert both strings in the same case when comparing.
In the below code both strings are wrapped in LCase VBA function.
You can also use UCase like this UCase(“ABHI”) = UCase(“abhi”).
Because of Lcase both strings got converted in lower case “abhi”=”abhi”, thus making the comparison True.
If you run test3() sub then the message box will show “Name matched”.
You may also like to read the below topics
How to run macro in Excel|Create macro button