Excel VBA String Comparison: Rules, Methods, and Examples

In Excel VBA, compare strings with StrComp when you want to choose case sensitivity explicitly: use StrComp(firstText, secondText, vbTextCompare) = 0 for a case-insensitive match, or vbBinaryCompare for a case-sensitive one. The = operator also compares strings, but its behavior depends on the module’s Option Compare setting.

What string comparison means in VBA

A string comparison checks whether two sequences of characters are equal or determines which one sorts before the other. In practical Excel macros, it often answers questions such as whether a cell contains a particular label, whether two names match, or whether a text value begins with an expected code. The important decision is whether uppercase and lowercase letters should count as different.

For example, a case-sensitive comparison treats "North" and "north" as different strings. A case-insensitive comparison treats them as a match. Neither behavior is universally correct: a password-like code or case-sensitive identifier may require an exact match, while a category label entered by people usually should not fail just because its capitalization varies.

VBA also distinguishes string comparison from numeric comparison. If values come from worksheet cells, a cell may contain a number, a date, an error value, or an empty value rather than text. Decide how those values should be handled before comparing them, instead of relying on an accidental type conversion.

Binary and text comparison

VBA provides two common comparison modes. vbBinaryCompare compares characters according to their character-code order. It is case-sensitive, so uppercase and lowercase versions of a letter do not match. vbTextCompare performs a text-oriented, case-insensitive comparison using the system’s locale rules. Locale can affect how letters are ordered or treated, so text comparison is not simply a universal alphabetic rule.

These constants are useful with StrComp, InStr, and other VBA operations that accept a comparison argument. For most code, naming the mode directly makes the intent easiest to understand:

Use vbBinaryCompare when capitalization matters or an exact character-level match is required.
Use vbTextCompare when capitalization should be ignored for ordinary text.
Use the module’s comparison setting only when you deliberately want a consistent default across that module.

A text comparison does not mean that every visually similar string is identical. Leading or trailing spaces remain characters, and different Unicode character sequences can look alike on screen while still comparing differently. If your data has those issues, clean or normalize it according to a defined rule before comparison.

How Option Compare changes the default

Option Compare is a module-level statement, placed in the declarations section at the top of a VBA module before procedures. It controls the default comparison method for string comparisons in that module. If a module has no Option Compare statement, VBA uses binary comparison.

For example, a module beginning with Option Compare Text makes ordinary string comparisons such as firstText = secondText case-insensitive in that module. With Option Compare Binary, that same expression is case-sensitive. The setting is not a workbook-wide switch: separate modules can have different settings, which can make code harder to reason about if the setting is left implicit.

Option Compare Database is intended for Microsoft Access database comparisons and is not the setting to use for ordinary Excel VBA work. In Excel, prefer Binary or Text, or specify the mode in the comparison function itself. Explicit arguments are particularly helpful in shared code because a reader can see the intended behavior without checking the module header.

Using StrComp for a clear result

StrComp compares two expressions and returns a numeric result: zero means equal, a negative value means the first string sorts before the second, and a positive value means it sorts after the second. Its optional third argument selects the comparison mode. When using the result as a match test, compare it with zero rather than assuming a particular nonzero value.

With two text values held in variables named firstText and secondText, a case-insensitive equality check is StrComp(firstText, secondText, vbTextCompare) = 0. For a case-sensitive equality check, replace vbTextCompare with vbBinaryCompare. The following complete function illustrates the case-insensitive version:

Option Compare Binary
Option Explicit

Public Function SameText(ByVal firstText As String, _
                         ByVal secondText As String) As Boolean
    ' Return True only when the strings match without regard to case.
    SameText = (StrComp(firstText, secondText, vbTextCompare) = 0)
End Function

The function uses an explicit comparison mode, so its result does not change if the module’s default is later changed. Its arguments are declared as strings, which suits callers that already have text. If the source may be an Excel error value or another non-text type, validate or convert the input deliberately before calling it. Save a copy of the workbook before running macros; changes made by VBA generally cannot be undone with Ctrl+Z.

Comparing with = and finding text with InStr

The equals operator is concise: If firstText = secondText Then runs a branch when the strings match under that module’s Option Compare setting. It is appropriate when the module’s setting is clear and intentionally applies to the whole module. If comparison behavior must be obvious at the point of use, StrComp is usually more readable.

Use InStr when the question is whether one string occurs inside another, rather than whether the complete strings are equal. Its first argument is the starting position, its next arguments are the text to search and the text to find, and its optional final argument sets the comparison mode. For example, to look for “lee” anywhere in a variable called fullName without regard to case, use this condition inside a procedure:

If InStr(1, fullName, "lee", vbTextCompare) > 0 Then
    Debug.Print "Match found"
End If

InStr returns zero when it finds no match and a positive position when it does. It is not a whole-cell equality test: a search for “lee” can match a longer value such as “Leeds.” Use StrComp or = when extra characters should make the comparison fail.

Comparing worksheet values safely

Values read from Excel cells often arrive in VBA as Variant values. A cell that appears to contain text might instead contain a number, a formula result, or an error. Before converting a cell value with CStr or passing it to a string procedure, consider whether a blank cell, an Excel error, or a number should count as a match. Calling a conversion on an error value can raise a run-time error.

For a simple text-only column, a practical approach is to check the value type and then compare the resulting strings. If your macro uses Range.Value2, remember that it returns the underlying value rather than a formatted display string. A date cell, for example, may be represented as a number; comparing its displayed date text is a different task from comparing that underlying value. Use the cell’s displayed text only when the formatting itself is what matters, and account for column width or display formatting if you do.

Whitespace is another frequent source of unexpected results. "North" and "North " are not equal because the second value has a trailing space. You can apply Trim$ when the business rule says ordinary leading and trailing spaces should be ignored, but trimming changes the data being compared. It also does not remove every unusual whitespace character, such as a nonbreaking space copied from a web page. Inspect or clean source data when matches still fail unexpectedly.

Case conversion, Like, and other choices

Another familiar technique is to convert both values to one case, for example with LCase$, and then compare them. It can work for straightforward text, but it creates transformed copies and makes the intended comparison less direct than StrComp(..., vbTextCompare). Case conversion can also be a poor substitute for locale-aware text comparison in languages with special casing rules. Use it when normalized text is itself useful, not merely as a reflex.

The Like operator answers a pattern-matching question rather than ordinary equality. Its pattern symbols include * for any number of characters, ? for one character, and # for a digit. For example, Like "AB-###" can match a pattern with three digits after AB-. The comparison setting affects how Like treats text; it also matches the complete value against the pattern, not just a substring. Use InStr for a simple substring search.

Choose the method based on the question:

Exact equality, with an explicit case rule: use StrComp.
Short equality checks governed by a deliberate module default: use =.
Substring search: use InStr.
Wildcard-style pattern check: use Like.

Common mistakes and troubleshooting

A frequent misconception is that VBA string comparisons are always case-insensitive. They are not: the default is binary comparison unless the module specifies otherwise. Another is that setting Option Compare Text in one module changes comparisons throughout the workbook. It does not; check the module containing the procedure that performs the comparison.

Unexpected non-match: Check capitalization, leading or trailing spaces, hidden characters, and whether the values are really strings.
Unexpected match: Confirm that case-insensitive comparison is acceptable and that you are not using InStr when complete equality is required.
Different results in different procedures: Check each module’s Option Compare statement, or specify the mode explicitly in StrComp or InStr.
Run-time error from a cell value: Check for an Excel error before converting or comparing the value as text.

To diagnose invisible characters, inspect the source cell and test a small representative sample. Applying Trim$ can reveal ordinary extra spaces, but do not remove punctuation or alter case unless the matching rule permits it. If identifiers are supposed to match exactly, silently cleaning them could merge values that should remain distinct.

Frequently Asked Questions

Is VBA string comparison case-sensitive?
By default, VBA uses binary comparison, which is case-sensitive. A module can change the default with Option Compare Text, or a procedure can specify the mode explicitly with StrComp or InStr.

How do I compare two strings without regard to case?
Use StrComp(firstText, secondText, vbTextCompare) = 0. This states both the equality test and the case-insensitive comparison mode directly.

What does StrComp return when strings match?
It returns zero when the compared strings are equal. A negative result indicates the first sorts before the second, and a positive result indicates it sorts after the second. Test for zero when you need an equality check.

Does Option Compare affect every Excel VBA module?
No. It is a module-level setting, so a procedure uses the setting in its own module. An explicit comparison argument avoids depending on that local default.

Why do two strings that look the same not compare equal?
They may contain extra spaces, hidden characters, or different underlying Unicode character sequences. Check the source values and apply only the cleaning rules that make sense for the data.

Choosing a reliable comparison rule

Start by defining what “the same” means for the data: must capitalization match, are surrounding spaces meaningful, and can values contain non-text types? Then choose the appropriate operation and make the case rule explicit where possible. For a direct match, StrComp with vbBinaryCompare or vbTextCompare is clear and predictable; for a search inside text, use InStr; and for a wildcard pattern, use Like. Handling types and whitespace deliberately is just as important as choosing the comparison mode.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.