Making Comparisons in Excel VBA

An extract from Excel VBA in easy steps, 2nd edition

The comparison operators used in VBA are listed in the table below, together with a description of the comparison they make:

Comparisons table

Each comparison operator returns a Boolean value of True or False according to the result of the comparison made. The = Equality operator compares two operands and will return True if both are equal in value, otherwise it will return a False value. If both are the same number, they are equal, or if both are strings, the ASCII code values of their characters are compared numerically to achieve the comparison result.

Conversely, the <> Inequality operator returns True if two operands are not equal, using the same rules as the = equality operator, otherwise it returns False. Equality and Inequality operators are useful in testing the state of two variables to perform conditional branching in a macro according to the result.

The > Greater Than operator compares two operands and will return True if the first is greater in value than the second, or it will return False if it is equal or less in value. The < Less Than operator makes the same comparison but returns True if the first operand is less in value than the second, otherwise it returns False. A > Greater Than or < Less Than operator is often used to test the value of an iteration counter in a loop construct.

Adding the = Equality operator after a > Greater Than or < Less Than operator makes it also return True if the two operands are exactly equal in value.

Hot-Tip-New_cropped

 

ASCII is the American Standard Code for Information Interchange. A-Z uppercase characters have ASCII code values 65-90, and a-z lowercase characters have ASCII code values 97-122.

 

Comparison.xlsm

Step 1
Begin a VBA macro module with a subroutine that declares and initializes five variables
Sub Comparison( )
Dim Nil As Integer
Dim Num As Integer
Dim Max As Integer
Dim Lower As String
Dim Upper As String
Nil = 0
Num = 0
Max = 1
Lower = “a”
Upper = “A”
‘ Statements to be inserted here (Steps 2 & 3).
End Sub

Step 2
Next, insert statements to display the result of equality and inequality comparisons
Range( “A1:C1” ) = _
Array( “Equality:” , “0 = 0“ , ( Nil = Num ) )
Range( “A2:C2” ) = _
Array( “Equality:” , “a = A“ , ( Lower = Upper ) ) Range( “A3:C3” ) = _
Array( “Inequality:” , “0 <> 1“ , ( Nil <> Max ) )

Step 3
Now, insert statements to display the result of greater and less comparisons
Range( “A4:C4” ) = _
Array( “Greater:” , “0 > 1“ , ( Nil > Max ) ) Range( “A5:C5” ) = _
Array( “Less:” , “0 < 1“ , ( Nil < Max ) )
Range( “A6:C6” ) = _
Array( “Less Or Equal:” , “0 <= 1“ , ( Nil <= Max ) )

Step 4
Run the macro to see the result of comparison operations

059-1[comparison]

 

 

 

 

 

 

Hot-Tip-New_cropped

 

Comparison of strings that contain multiple characters also take character order into account – so that “ABC” is not equal to “BAC”.

 

 

 

 

 

Want to know more?

To find out more about using Excel VBA, all in the trusted In Easy Steps style, click here. In full-colour with free, downloadable code, Excel VBA in easy steps, 2nd edition will help you get more out of the popular spreadsheet application using Visual Basic for Applications, in no time at all!