In Excel VBA, test whether two strings are equal with If firstText = secondText Then. That comparison is case-sensitive by default; use StrComp(firstText, secondText, vbTextCompare) = 0 when the match should ignore letter case.
Basic string equality in VBA
The equals sign (=) is VBA’s equality operator for strings. Put the comparison in an If...Then statement, and VBA runs the statements inside the block only when the two values match. The comparison is exact: both strings must have the same characters in the same order, subject to the module’s comparison setting.
For example, a procedure can compare a string with a literal value such as "Ready". The following uses the default binary comparison, which is case-sensitive. Save a copy of the workbook before running any macro; VBA changes cannot be undone with Ctrl+Z.
Option Explicit
Option Compare Binary
Public Sub CheckStatus()
Dim statusText As String
' Read a text value from the active sheet.
statusText = CStr(ActiveSheet.Range("A2").Value2)
If statusText = "Ready" Then
MsgBox "The status is Ready."
End If
End Sub
If A2 contains Ready, the condition is true. If it contains ready or Ready with a trailing space, it is false. VBA does not use == for equality; use one equals sign. Also, Is is for checking whether two object variables refer to the same object, not for comparing string contents.
Case-sensitive and case-insensitive comparisons
In a standard Excel VBA module, binary comparison is the default unless the module specifies another setting. Binary comparison distinguishes uppercase and lowercase letters, so "North" and "north" are not equal. You can state the default explicitly with Option Compare Binary at the top of a module, before any procedures.
To ignore case for one comparison, use StrComp with the vbTextCompare argument. It returns zero when the strings compare as equal, a negative number when the first sorts before the second, and a positive number when it sorts after. Therefore, the equality test checks whether its result is zero:
Save a copy of the workbook before running this macro, because macro changes cannot be reversed with Ctrl+Z.
Option Explicit
Public Sub CheckStatusIgnoringCase()
Dim statusText As String
' Compare the cell text without distinguishing letter case.
statusText = CStr(ActiveSheet.Range("A2").Value2)
If StrComp(statusText, "READY", vbTextCompare) = 0 Then
MsgBox "The status matches, regardless of case."
End If
End Sub
For a whole module that should treat string comparisons as case-insensitive, place Option Compare Text at the top. Then ordinary string comparisons in that module, such as If a = b Then, use text comparison. This setting affects the module, not every module in the workbook, so it can be less obvious than specifying vbTextCompare at the point of use. Text comparisons follow locale rules; if a particular comparison must be explicit, StrComp makes the intended behavior easier to see.
What the equality test actually compares
A string comparison checks the complete contents, not whether one value merely contains another. The string "Order 24" equals "Order 24", but not "Order 24 ", "Order 24A", or "24". Spaces, punctuation, and other characters are part of the value. If data comes from a form, a copy-and-paste operation, or an imported file, visually similar text may still contain different characters.
VBA’s comparison mode also matters. Option Compare Binary gives a case-sensitive comparison; Option Compare Text makes ordinary string comparisons case-insensitive under the system’s text-sorting rules. You can avoid relying on a module default by using StrComp(a, b, vbBinaryCompare) = 0 for an explicit case-sensitive match or StrComp(a, b, vbTextCompare) = 0 for an explicit case-insensitive match.
These are VBA language comparisons, not worksheet formulas. In a worksheet cell, the Excel formula for a case-insensitive equality test is different from VBA syntax. A VBA If statement belongs in a procedure in the Visual Basic Editor, not in a worksheet cell.
Comparing text read from worksheet cells safely
A cell’s Value or Value2 is a Variant, not necessarily a String. A cell may be blank, contain a number or date, or contain an Excel error value. Converting every value directly with CStr can cause problems for error values, so check for errors before converting. Decide how blank cells should behave as well: converting a blank cell’s Empty value to text can yield an empty string, which may match a target of "".
This example exits for an error, a blank cell, or a Null Variant before converting the value. It then performs a case-insensitive exact match. Save a copy first; running a macro cannot be undone with Ctrl+Z.
Option Explicit
Public Sub CheckCellSafely()
Dim cellValue As Variant
Dim cellText As String
' Read the underlying value from A2.
cellValue = ActiveSheet.Range("A2").Value2
' Handle values that should not be converted to text.
If IsError(cellValue) Then Exit Sub
If IsEmpty(cellValue) Then Exit Sub
If IsNull(cellValue) Then Exit Sub
cellText = CStr(cellValue)
If StrComp(cellText, "Approved", vbTextCompare) = 0 Then
MsgBox "A2 contains Approved."
End If
End Sub
Using Value2 is common when reading worksheet values because it returns the underlying value without the Currency or Date subtypes used by Value. A date cell is therefore typically read as its serial number; it should not be compared to a text label unless that is genuinely the intended data. For text comparisons, keep source cells consistently populated with text, or convert only after checking the value’s type and meaning.
Handling spaces and other invisible characters
When text looks identical but the comparison fails, inspect whitespace first. Trim$ removes ordinary leading and trailing spaces, so you can compare cleaned values. It does not remove spaces inside the text, and it is not a universal cleaner for tabs, line breaks, or nonbreaking spaces copied from web pages. Do not trim automatically if spaces are meaningful in the data.
For example, this pattern ignores ordinary spaces at the ends of both values while still requiring the remaining text to match exactly and with the chosen case rule. The variables are strings here, so this example does not need a cell-value conversion. Save a workbook copy before running the macro; Ctrl+Z cannot undo VBA actions.
Option Explicit
Public Sub CompareTrimmedText()
Dim enteredText As String
Dim expectedText As String
enteredText = " Approved "
expectedText = "Approved"
If StrComp(Trim$(enteredText), Trim$(expectedText), vbTextCompare) = 0 Then
MsgBox "The trimmed strings match."
End If
End Sub
If ordinary trimming does not solve the issue, inspect the actual characters rather than repeatedly changing the comparison. A tab or nonbreaking space can look like a regular space. For imported data, decide which characters are safe to remove, then normalize them deliberately; broad replacement can accidentally alter legitimate names or codes.
Exact equality versus partial matching
Use = or StrComp when the whole string must match. For a substring search, use InStr instead. Its result is zero when the substring is absent and a positive position when it is found. The optional comparison argument can make the search case-insensitive. For instance, a condition built around InStr(1, messageText, "late", vbTextCompare) > 0 checks whether the text contains “late” anywhere, not whether the entire string equals “late.”
VBA’s Like operator is another option when a pattern is needed, such as matching text against a pattern with wildcards. It is not a substitute for simple equality: pattern characters have special meaning, and the comparison setting can affect its behavior. Choose the operator that describes the requirement: exact equality, substring search, or pattern match.
- Use
If a = b Thenfor straightforward equality governed by the module’s comparison setting. - Use
StrComp(a, b, vbBinaryCompare) = 0for an explicit case-sensitive match. - Use
StrComp(a, b, vbTextCompare) = 0for an explicit case-insensitive match. - Use
InStrto find text inside a larger string, andLikewhen you need a pattern.
Common errors and practical checks
A frequent mistake is assuming that a plain equals comparison always ignores case. It does not: the result depends on the module’s Option Compare setting, which is binary by default. Another is checking cell text without considering that the cell might contain a number, a blank, or an error. A third is using equality when the requirement is really “contains,” which needs a different test.
When a comparison behaves unexpectedly, check these points in order:
- Confirm both values are the expected text, rather than a number, error value, or blank.
- Check for leading or trailing spaces, tabs, line breaks, and nonbreaking spaces.
- Decide whether capitalization matters and use an explicit
StrCompmode if needed. - Make sure the requirement is a full match rather than a substring or wildcard match.
- Check whether a module-level
Option Compare Textchanges the behavior of an ordinary equals comparison.
Excel for Microsoft 365 on Windows includes the desktop Visual Basic Editor for VBA. Excel for the web cannot create, run, or edit VBA macros; use desktop Excel for that work. On Windows, Alt+F11 opens the Visual Basic Editor. On Mac, Option+F11 is the corresponding shortcut on many keyboards, though some require Fn as well. The comparison expressions themselves are VBA language features, not Windows-only worksheet functions.
Frequently Asked Questions
Is VBA string comparison case-sensitive?
By default in an Excel VBA module, binary string comparison is case-sensitive, so "Yes" and "yes" differ. Use StrComp(a, b, vbTextCompare) = 0 to ignore case for a specific comparison, or set Option Compare Text at the top of a module.
How do I compare a cell to a word in VBA?
Read the cell’s value and compare it in an If statement, for example If Range("A1").Value2 = "Done" Then. For robust code, first check whether the cell contains an error or a blank, then convert an appropriate value to a string if necessary.
How do I check whether two strings are exactly equal?
Use If firstText = secondText Then for equality under the module’s comparison option. To make the case-sensitive requirement explicit, use If StrComp(firstText, secondText, vbBinaryCompare) = 0 Then. Both require the complete strings to match.
How do I check whether a string contains another string?
Use InStr and test whether its result is greater than zero. For example, InStr(1, fullText, searchText, vbTextCompare) > 0 checks for a case-insensitive substring. This is not the same as testing whether the two complete strings are equal.
Why does the comparison fail when the text looks the same?
The strings may differ in capitalization, leading or trailing spaces, tabs, line breaks, or other invisible characters. They may also come from a cell that contains a non-text value. Check the underlying value and clean only characters that your data rules say should be ignored.
Related
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Split Data Cleanly and Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Excel Open and Save As Dialog Box: How It WorksIn Excel VBA, use Application.GetOpenFilename to display a file-selection dialog and Application.GetSaveAsFilename to ask where a file should…
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
- Excel VBA Block Comments: How to Comment Out Code SafelyExcel VBA has no special multi-line block-comment syntax such as /* … */. To comment out several lines,…
- Excel VBA Out of Memory Error: Causes and FixesAn Excel VBA “Out of memory” error usually means Excel cannot allocate enough usable memory for the macro’s…
- Excel VBA Date Math: Add, Subtract, and Compare DatesIn Excel VBA, subtract one Date value from another to get the elapsed time in days, and use…
- Excel VBA Compile Error: Syntax Error Causes and FixesAn Excel VBA “Compile error: Syntax error” message means VBA cannot parse part of your code as valid…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.