🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA UCase & LCase in Excel — Case-Insensitive Compares and the StrConv Title-Case Trap

|

VBA UCase & LCase in Excel — Case-Insensitive Compares and the StrConv Title-Case Trap

TL;DR — UCase and LCase flip text to all-caps or all-lowercase. The mistake is thinking they're for cleaning data. They're for two jobs only: comparing (VBA's = is case-sensitive, so "Yes" = "yes" is False — normalize both sides with UCase before you compare) and displaying. They are lossy and irreversible, so never overwrite stored data with them. And for Title Case there's no built-in PCase — you use StrConv(s, vbProperCase), which works for plain names but mangles McDonald, O'Brien, and iPhone.

Changing case feels like the simplest thing in VBA, and UCase/LCase themselves are simple. The bugs come from why people reach for them. Used as a comparison tool, they're indispensable. Used as a storage tool, they quietly destroy information.

What you'll learn

  • Why "Yes" = "yes" is False in VBA — and the right way to compare ignoring case
  • Three ways to do a case-insensitive match (and which to pick)
  • Why you should never store UCase output over your original data
  • StrConv for Title Case — and the proper-noun trap that comes with it

The mental model: case is for comparing and displaying, not for storing

Think of UCase/LCase as a pair of lenses you look through, not edits you make to the page. When you want to know whether two strings match regardless of case, you look at both through the same lens (UCase them) and compare what you see — the page underneath is untouched. The moment you treat the lens as an edit — writing UCase(name) back over name — you've thrown away the original capitalization forever.

Sub TheLensIdea()
    Dim stored As String
    stored = "McDonald"               ' the real data — keep it

    ' Compare through a lens, don't change the data
    If UCase(stored) = UCase("mcdonald") Then
        Debug.Print "match"           ' this prints
    End If

    Debug.Print stored                ' still "McDonald" — untouched
End Sub

The rule that catches the #1 case bug: VBA's = is case-sensitive

This is the failure mode behind a huge share of "my If never runs" questions. By default VBA compares strings byte for byte, so capitalization matters:

Sub CaseSensitiveByDefault()
    Dim answer As String
    answer = "YES"                    ' what the user actually typed

    If answer = "yes" Then            ' FALSE — "YES" <> "yes"
        Debug.Print "confirmed"       ' never runs
    End If

    ' Fix: normalize both sides through the same lens
    If UCase(answer) = "YES" Then     ' TRUE
        Debug.Print "confirmed"       ' runs
    End If
End Sub

You have three ways to make a comparison case-insensitive, and the choice is a real judgment call:

  1. UCase/LCase both sides — UCase(a) = UCase(b). Explicit, local, obvious to the next reader. Best default for a one-off compare.
  2. StrComp(a, b, vbTextCompare) — returns 0 when equal ignoring case. Cleanest when you specifically want a comparison and nothing else.
  3. Option Compare Text at the top of the module — makes every =, Like, and InStr in that module case-insensitive. Powerful but invisible: someone reading one line can't tell the comparison rules changed. Use it sparingly and only when the whole module wants that behavior.

My rule: prefer explicit UCase for a single compare; use Option Compare Text only when an entire module is genuinely case-insensitive, and comment it loudly.

Dictionary keys are case-sensitive too

The same trap bites Scripting.Dictionary. By default d("USA") and d("usa") are two different keys — you'll get duplicate entries you didn't expect. Either set d.CompareMode = vbTextCompare right after creating it, or UCase every key before you store or look it up. Pick one and be consistent.

Title Case: there is no PCase, so you use StrConv

VBA gives you UCase and LCase but no built-in proper-case function. The tool is StrConv with the vbProperCase flag:

Debug.Print StrConv("john smith", vbProperCase)   ' -> "John Smith"

That works beautifully for ordinary lowercase input. But StrConv only knows one rule — capitalize the first letter of each word, lowercase the rest — and that rule is wrong for a lot of real names:

Debug.Print StrConv("McDonald", vbProperCase)  ' -> "Mcdonald"   (wrong)
Debug.Print StrConv("O'BRIEN", vbProperCase)   ' -> "O'brien"    (wrong)
Debug.Print StrConv("iPhone", vbProperCase)    ' -> "Iphone"     (wrong)
Debug.Print StrConv("ACME III", vbProperCase)  ' -> "Acme Iii"   (wrong)

The judgment: StrConv(..., vbProperCase) is a "good enough" cleaner for free- text names and addresses where the input is all-caps or all-lowercase. It is not a brand/proper-noun formatter. If correctness matters for specific names, keep an exceptions list and fix them after StrConv, or don't auto-title-case at all.

StrConv does far more than case, which is why it's the right home for this — it also converts between half-width and full-width (vbWide/vbNarrow) and between Hiragana and Katakana (vbHiragana/vbKatakana) for East Asian text, plus byte/ Unicode conversions. Worth knowing the flag list exists when you hit those needs.

How ExcelMaster helps

Most case-handling VBA is part of a cleanup pass: standardize a column so matches and lookups stop missing. ExcelMaster does that from a description — "match these two customer lists ignoring case and spacing" — and normalizes for the comparison without destroying the display values in your sheet. You describe the intent (case-insensitive match, Title Case for display) and it keeps the original data intact.

You'll still write UCase/LCase inline in macros. But for "clean and match these columns" work, stating the rule beats hand-normalizing every key.

Frequently asked questions

How do I convert text to uppercase or lowercase in VBA?

Use UCase(text) for all-caps and LCase(text) for all-lowercase. They return a new string and don't modify the original, so assign the result somewhere if you want to keep it.

How do I do a case-insensitive comparison in VBA?

Normalize both sides with the same function — UCase(a) = UCase(b) — or use StrComp(a, b, vbTextCompare) which returns 0 when the strings are equal ignoring case. For a whole module, Option Compare Text makes every comparison case-insensitive.

How do I capitalize the first letter of each word in VBA?

Use StrConv(text, vbProperCase). Be aware it lowercases everything else, so it turns McDonald into Mcdonald and iPhone into Iphone. For names with internal capitals, correct them with an exceptions list afterward.

Why does StrConv mess up names like McDonald?

vbProperCase follows one rigid rule: uppercase the first letter of each word, lowercase the rest. It has no knowledge of proper nouns, so any name with an internal capital (McDonald, MacLeod, iPhone) or apostrophe (O'Brien) comes out wrong. Treat it as a best-effort cleaner, not an authority on names.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-06-14.

Related guides: VBA Concatenate · VBA Str · VBA Replace · VBA Dictionary · VBA For Loop