Excel VBA Comparison Operators – Complete Guide

```html

Excel VBA Comparison Operators

Learn how to compare numbers, text, dates and expressions and make decisions in Excel VBA.

← Operators & Expressions

Introduction to Comparison Operators

Comparison operators are used in VBA to compare two values, variables or expressions.

The result of a comparison is generally a logical result: True or False. VBA also has situations where a comparison can result in Null, particularly when a Variant expression contains Null.

Comparison operators are extremely important because they allow VBA to make decisions.

Example:

Suppose:

Age = 25

We can ask VBA:

Is Age greater than or equal to 18?

VBA evaluates:

25 >= 18

Result: True

Why Do We Use Comparison Operators?

Comparison operators are used whenever a VBA program needs to determine whether one value is related to another value in a particular way.

They are commonly used for:

  • Checking whether two values are equal.
  • Checking whether a value is greater or smaller.
  • Validating user input.
  • Checking employee salary or age.
  • Checking sales targets.
  • Comparing dates.
  • Filtering Excel data.
  • Controlling If...Then statements.
  • Controlling loops.
  • Combining multiple conditions with logical operators.

Comparison Operators in VBA

Operator Name Example Meaning
= Equal To 10 = 10 Both values are equal
<> Not Equal To 10 <> 5 Values are different
> Greater Than 10 > 5 Left value is greater
< Less Than 5 < 10 Left value is smaller
>= Greater Than or Equal To 10 >= 10 Left value is greater or equal
<= Less Than or Equal To 10 <= 10 Left value is smaller or equal

1. Equal To Operator (=)

The equal sign = is used to check whether two values are equal.

10 = 10

Result: True

VBA Example

Dim A As Integer Dim B As Integer A = 10 B = 10 If A = B Then MsgBox "Both values are equal" End If

Since A and B both contain 10, the comparison A = B returns True.

Important: Assignment vs Comparison

The same = symbol is also used when assigning a value to a variable.

A = 10

Here, VBA is assigning 10 to A.

If A = 10 Then MsgBox "A is 10" End If

Here, VBA is comparing A with 10.

Remember:

A = 10 outside a condition normally assigns a value.
A = 10 inside a condition compares values.

2. Not Equal To Operator (<>)

The <> operator checks whether two values are different.

10 <> 5

Result: True

Because 10 and 5 are different.

Example

Dim Status As String Status = "Pending" If Status <> "Completed" Then MsgBox "Work is still pending" End If

This is useful when you want to execute code only when a value is different from a particular value.

3. Greater Than Operator (>)

The greater-than operator checks whether the first value is greater than the second value.

20 > 10

Result: True

Sales Target Example

Dim Sales As Double Dim Target As Double Sales = 150000 Target = 100000 If Sales > Target Then MsgBox "Target exceeded" End If

This type of comparison is frequently used in financial reports and Excel automation.

4. Less Than Operator (<)

The less-than operator checks whether the first value is smaller than the second value.

5 < 10

Result: True

Example

Dim Stock As Long Stock = 5 If Stock < 10 Then MsgBox "Stock is running low" End If

5. Greater Than or Equal To (>=)

The >= operator returns True when the first value is either greater than or equal to the second value.

10 >= 10

Result: True

Notice that equality is included.

Example – Passing Marks

Dim Marks As Integer Marks = 40 If Marks >= 40 Then MsgBox "Pass" Else MsgBox "Fail" End If

A student with exactly 40 marks will pass because >= includes equality.

6. Less Than or Equal To (<=)

The <= operator returns True when the first value is either smaller than or equal to the second value.

10 <= 10

Result: True

Example

Dim Age As Integer Age = 18 If Age <= 18 Then MsgBox "Age is 18 or below" End If

Comparison Operators Return True or False

Comparison expressions can be stored in a Boolean variable.

Dim Result As Boolean Result = 20 > 10 MsgBox Result

The value of Result will be:

True

Another Example

Dim Result As Boolean Result = 20 < 10 MsgBox Result

The result will be:

False

Using Comparison Operators with If...Then

Comparison operators become especially powerful when combined with decision-making statements such as If...Then.

Dim Amount As Double Amount = 75000 If Amount > 50000 Then MsgBox "Amount is greater than 50,000" End If

VBA first evaluates:

Amount > 50000

75000 > 50000

Result = True

Using Comparison Operators with If...Else

You can use comparison operators to choose between two different actions.

Dim Marks As Integer Marks = 72 If Marks >= 40 Then MsgBox "Pass" Else MsgBox "Fail" End If

This is one of the most common uses of comparison operators in VBA programming.

Combining Comparison Operators

Comparison operators can be combined with logical operators such as And, Or and Not.

Example Using And

Dim Age As Integer Dim Salary As Double Age = 30 Salary = 60000 If Age >= 18 And Salary >= 50000 Then MsgBox "Both conditions are satisfied" End If

Here VBA checks two comparisons:

  • Age >= 18
  • Salary >= 50000

Both must be True for the complete condition to be True.

Comparing Text in VBA

Comparison operators can also be used with text values.

Dim Department As String Department = "Finance" If Department = "Finance" Then MsgBox "Finance Department" End If

VBA compares the text expressions according to the applicable string comparison rules. The exact behavior of text comparison can also depend on the module's Option Compare setting.

Important:

When comparing strings, be careful about spelling, spaces and comparison settings.

Comparing Dates in VBA

Comparison operators can also be used to compare dates. This is very useful in Excel reports, attendance systems, invoice tracking and deadline management.

Dim DueDate As Date DueDate = DateSerial(2026, 9, 10) If DueDate > Date Then MsgBox "Payment is due in the future" End If

You can also check whether a date has already passed.

If DueDate < Date Then MsgBox "Payment is overdue" End If

Comparing Excel Cell Values

Comparison operators are extremely useful when working directly with worksheet cells.

Example

If Range("B2").Value > 100000 Then MsgBox "Sales target achieved" End If

Suppose B2 contains:

125000

VBA evaluates:

125000 > 100000

Result = True

Practical Example – Employee Performance

Suppose an Excel worksheet contains an employee's sales performance.

Cell Information
A2 Employee Name
B2 Sales
C2 Target

VBA can compare Sales and Target:

Sub CheckTarget() Dim Sales As Double Dim Target As Double Sales = Range("B2").Value Target = Range("C2").Value If Sales >= Target Then MsgBox "Target Achieved" Else MsgBox "Target Not Achieved" End If End Sub

This simple concept can be expanded into complete automated sales dashboards and reporting systems.

Using ElseIf with Comparison Operators

You can test multiple ranges using ElseIf.

Dim Marks As Integer Marks = 82 If Marks >= 80 Then MsgBox "Grade A" ElseIf Marks >= 60 Then MsgBox "Grade B" ElseIf Marks >= 40 Then MsgBox "Grade C" Else MsgBox "Fail" End If

VBA evaluates the conditions from top to bottom and executes the first condition that evaluates to True.

Comparison with Null

One important VBA concept is Null. A Null value represents the absence of a valid value in a Variant.

If one of the expressions being compared is Null, the comparison result can also be Null rather than True or False.

Dim Value As Variant Value = Null
Important:

Do not normally test for Null using:

Value = Null

Use:

IsNull(Value)

when you need to determine whether a Variant contains Null.
If IsNull(Value) Then MsgBox "Value is Null" End If

Common Mistakes

1. Confusing = with <>

= means equal.
<> means not equal.

2. Confusing > with >=

The operator > does not include equality.

The operator >= includes equality.

10 > 10 = False

10 >= 10 = True

3. Confusing < with <=

10 < 10 = False

10 <= 10 = True

4. Forgetting Quotes Around Text

If Department = "Finance" Then MsgBox "Finance" End If

Text values should normally be written as string literals using quotation marks.

Practice Questions

Try to solve these questions yourself before checking the answers.

Question 1

What is the result of:

20 > 10
Question 2

What is the result of:

20 < 10
Question 3

What is the result of:

50 >= 50
Question 4

What is the result of:

50 <= 49
Question 5

Write VBA code to check whether the value in B2 is greater than 1000.
Question 6

Write VBA code to check whether a variable named Status is not equal to "Completed".
Question 7

Write VBA code to check whether Marks are greater than or equal to 40.

Quick Quiz

1. Which operator means "Not Equal To"?

A. !=
B. <>
C. <=
D. ==
Answer: B. <>
2. What is the result of 25 > 10?

A. True
B. False
C. 25
D. 10
Answer: A. True
3. What is the result of 25 < 10?

A. True
B. False
C. Null
D. Error
Answer: B. False
4. Which operator includes equality?

A. >
B. <
C. >=
D. <>
Answer: C. >=
5. Which function should normally be used to check whether a Variant contains Null?

A. IsEmpty
B. IsNull
C. IsNothing
D. NullCheck
Answer: B. IsNull

Key Takeaways

  • = checks whether two values are equal.
  • <> checks whether two values are different.
  • > checks whether the first value is greater.
  • < checks whether the first value is smaller.
  • >= means greater than or equal to.
  • <= means less than or equal to.
  • Comparison expressions are commonly used with If...Then.
  • Comparison operators can be combined with logical operators such as And and Or.
  • Numbers, text and dates can all be compared in VBA, subject to their data types and comparison rules.
  • Be careful when comparing values that may contain Null.
← Previous Lesson Operators & Expressions Next Lesson →

What's Next?

Now that you understand Comparison Operators, the next important topic is Logical Operators.

Logical operators allow you to combine multiple conditions. They are especially important when creating advanced If...Then statements and decision-making systems in VBA.

Learn Logical Operators →
```

Post a Comment

0 Comments