MS-Excel / Functions and Formula

Using the StrComp Function to Compare Apples to Apples

As you've seen already, you can compare one item to another item by simply using the = operator:

If 1 = 1 Then MsgBox "One is one."

This straightforward comparison with the = operator also works with two strings, as shown in the second line here:

strPet = InputBox("Is your pet a dog or a cat?", "Pet")
If strPet = "Dog" Then MsgBox "We do not accept dogs."

The problem with this code as written is that the strings need to match exactly in capitalization for VBA to consider them equal. If the user enters dog or DOG (not to mention dOG, doG, dOg, or DoG) rather than Dog, the condition isn't met. Again, permit your users a variety of correct responses - don't enforce pointless capitalization and punctuation rules.

To accept variations of capitalization, you could use the Or operator to hedge your bets:

If Pet = "Dog" Or Pet = "dog" Or Pet = "DOG" Or Pet = "dogs" _
Or Pet = "Dogs" or Pet = "DOGS" Then MsgBox _
"We do not accept dogs. "

As you can see, such code rapidly becomes clumsy, even omitting some variations such as dOG. Or you could change the case of one or both strings involved to make sure their case matched, but it's simpler to just use the StrComp function, which is designed to permit you to ignore case. The syntax for StrComp is as follows:

StrComp(string1, string2 [, compare])

Here, string1 and string2 are required String arguments specifying the strings to compare, and compare is an optional argument specifying textual comparison (vbTextCompare) or binary comparison (vbBinaryCompare).

The following statement uses StrComp to settle the pet question once and for all:

If StrComp(Pet, "dog", vbTextCompare) = True Then _
    MsgBox "We do not accept dogs."
[Previous] [Contents] [Next]

In this tutorial:

  1. Using Built-in Functions
  2. What Is a Function?
  3. Passing Arguments to a Function
  4. Using Functions to Convert Data from One Type to Another
  5. Using the Asc Function to Return a Character Code
  6. Using the Str Function to Convert a Value to a String
  7. Using the Format Function to Format an Expression
  8. Using the Chr Function and Constants to Enter Special Characters in a String
  9. Using Functions to Manipulate Strings
  10. Using InStr and InStrRev to Find a String within Another String
  11. Using LTrim, RTrim, and Trim to Trim Spaces from a String
  12. Using Len to Check the Length of a String
  13. Using the StrComp Function to Compare Apples to Apples
  14. Using VBA's Mathematical Functions
  15. Excel VBA's Date and Time Functions
  16. Using the DateDiff Function to Return an Interval
  17. Using File-Management Functions