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."
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions