In this post we will see how to make string comparison case insensitive in VBA program.
I realized this scenario while writing a VBA program which would allow the user to type an address and find the location by matching elements of the address with a database and the problem would arise if the value entered is “miami” or “MIAMI” and we have “Miami” in the database, it won’t work
To get rid of case insensitivity we will introduce a keyword in the begining of our VBA program that will take care of the string comparison inn VBA program.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to make the string comparison case insensitive.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Option Compare Text
Sub Chek_String()
If “MIAMI” = “miAMi” Then
MsgBox “this is true: option Compare Text has been set!”
End If
End Sub
Option Compare Text is the statement that we have introduced in the beginning of our VBA program that tell VBA to consider low case and up case string same.
You can see in the program we are comparing two strings whose values are same but they are of different case, but option compare text will take care of the string comparison in VBA program and we will get the message box saying both strings are equal as IF condition results in true.
Now run the code by pressing F5 or by clicking on play button in the editor to get the font details from excel cell.
Hope this helped.
