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]