Case Insensitive String Comparison In VBA

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.

Open developer tab to write VBA to do case insensitive string comparison

Step 2

Insert a module in which we will write a VBA code to make the string comparison case insensitive.

Insert a module in the VB editor

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.

VBA code to match string independent of case

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.

result showing string comparison was done independent of case

Hope this helped.

Share The Knowledge

Random Posts