VBA – Excel : get rid of the case sensitivity when comparing words?

calendar_today Asked Jun 11, 2013
thumb_up 30 upvotes
history Updated April 14, 2026

Direct Answer

There is a statement you can issue at the module level: Option Compare Text This makes all "text comparisons" case insensitive. This means the following code will show the message…. This is an advisory response with reference links, ranked #14th of 95 by community upvote score, from 2013.


The Problem (Q-score 15, ranked #14th of 95 in the VBA Core archive)

The scenario as originally posted in 2013

I am working on an VBA program which would allow the user to type an address and find the location by matching elements of the address with a database.

Unfortunately, I am having a recurrent problem with the case sensitivity.

For example, when I am using this code : 

For i = 11 To lRowB
Range("B" & i).Activate
myResult = IsNumeric(Application.Match(ActiveCell.Value, manilaListRange, 0))

It is gonna compare the value of the active cell to a list of words from my database. Problem is, if in my active cell the word is “miami” or “MIAMI” and only “Miami” is in the database, it won’t work…

Other example: 

If Range("J6").Value = "tawi" Then
Range("J6").Value = "Tawi-Tawi"
End If

Same problem, only the word written with the same case is gonna work.

How can I get rid of this? It’s particularly annoying and I can’t rewrite my database in every case combination possible!

Thanks in advance !

Why community consensus is tight on this one

Across 95 VBA Core entries in the archive, the accepted answer here holds strong answer (top 25 %%) status — meaning voters are unusually aligned on the right fix.


The Verified Solution — strong answer (top 25 %%) (+30)

Advisory answer — community consensus with reference links

Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.

There is a statement you can issue at the module level:

Option Compare Text

This makes all “text comparisons” case insensitive. This means the following code will show the message “this is true”:

Option Compare Text

Sub testCase()
  If "UPPERcase" = "upperCASE" Then
    MsgBox "this is true: option Compare Text has been set!"
  End If
End Sub

See for example http://www.ozgrid.com/VBA/vba-case-sensitive.htm . I’m not sure it will completely solve the problem for all instances (such as the Application.Match function) but it will take care of all the if a=b statements. As for Application.Match – you may want to convert the arguments to either upper case or lower case using the LCase function.

Error-handling details to lift with the snippet

This answer wires error flow through MsgBox / Err.Description. Keep that intact: stripping it to “make it cleaner” removes the signal you’ll need when the macro fails silently on a user machine.


When to Use It — classic (2013–2016)

Ranked #14th in its category — specialized fit

This pattern sits in the 75% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2013 and 2026

The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.

help
Frequently Asked Questions

Why does this sit in the top quartile of VBA Core answers?
expand_more

Answer score +30 vs the VBA Core archive median ~10; this entry is strong. The score plus 15 supporting upvotes on the question itself (+15) means the asker and 29 subsequent voters all validated the approach.

This answer links out — what are the reference links worth following?
expand_more

Read the first external link for the canonical reference, then search this archive for a top-10 entry in the same category — advisory answers are best paired with a ranked code snippet to close the loop.

Published around 2013 — what’s changed since?
expand_more

Published 2013, which is 13 year(s) before today’s Office 2026 build. The VBA Core object model has had no breaking changes in that window. Three things to re-test: (1) blocked macros on downloaded files (Mark-of-the-Web), (2) 64-bit API declarations (PtrSafe, LongPtr), (3) any shift toward Office Scripts for web scenarios.

Which VBA Core pattern ranks just above this one at #13?
expand_more

The pattern one rank above is “Environ Function code samples for VBA”. If your use case overlaps, compare both before committing.

Data source: Community-verified Q&A snapshot. Q-score 15, Answer-score 30, original post 2013, ranked #14th of 95 in the VBA Core archive. Last regenerated April 14, 2026.

vba