Prevent Case Sensitivity in VBA Code

Keep Excel from distinguishing between capital and lowercase letters in your text.

By default, Excel VBA code is case sensitive and uses what is known as binary comparisons. This means that it sees Cat and cat as two different words. There are many times, however, where you would like Excel VBA not to use binary comparisons like this (e.g., when counting entries) and you would like it to see Cat and cat as the same. You can do this easily in at least two different ways.

Ucase Function

The Ucase function can compare text in a range of cells, allowing us to write a macro that compares text case-insensitively.

Here is one that displays a message box if it encounters any cell in A1:A10 of the active sheet containing any case variation of the word CAT.

The code.
To insert the code, press Alt/Option-F11, select Insert → Module, and paste the following:

Sub CompareText( )
Dim rCell As Range

    For Each rCell In Range("A1:A10")
	If UCase(rCell) = "CAT" Then
	    MsgBox rCell.Address & " has " & rCell & " in it"
	End If
    Next rCell
End Sub

Exit the VBE and return to Excel and save your workbook.

To run the macro, select Developer → Macros (pre-2007, Tools → Macro → Macros...), select CompareText from the list, and click Run. So if you had the word CAT (uppercase) in cells A1:A9 and the word cat (lowercase) in A10, the macro would display a message box for each instance of the word "cat," regardless of what case it is entered in.

Option Compare Text

The other method eliminates case sensitivity for all procedures (or macros) in a specified module. This means that if you have 20 macros in a module, each doing something different, running this code removes case-sensitivity from all macros. This means that if we have the word CAT entered in 20 macros doing different things, it will always be seen without regard to case sensitivity.

We do this by placing the words Option Compare Text at the very top of the module we want to make case insensitive. This will ensure that any procedures placed within the same module as the procedure in the following section will no longer be case sensitive.

The code. To insert the code, press Alt/Option-F11, select Insert → Module and paste the following:

Option Compare Text

Sub OptionCompareText( )
Dim rCell As Range

    For Each rCell In Range("A1:A10")
	If rCell = "CAT" Then
	    MsgBox rCell.Address & " has " & rCell & " in it"
	End If
    Next rCell
End Sub

Exit the VBE and return to Excel and save your workbook.

To run the macro select Developer → Macros (pre-2007, Tools → Macro → Macros...), select OptionCompareText from the list, and click Run.

To make all procedures within the Module case sensitive again we would replace Option Compare Text with Option Compare Binary.