In previous tutorials, we learned about variables and constants in VBA. In this free VBA lesson, we’ll explore VBA operators and see how to perform arithmetic, comparison, and logical operations in our programs.
Operators are one of the fundamental building blocks of the VBA language. They are used for calculations, condition checks, string concatenation, and logical control. In other words, operators are the tools that allow you to work with data and produce meaningful results.
📑 Table of Contents
Types of Operators in VBA
VBA operators are divided into four main categories:
Arithmetic Operators
Arithmetic operators in VBA are used to perform mathematical operations such as addition, subtraction, multiplication, division, and exponentiation. The table below shows the most common arithmetic operators:
| Result | Example | Description | Operator |
|---|---|---|---|
| 6 | 2*3 | Multiplication | * |
| 8 | 2^3 | Exponentiation | ^ |
| 3 | 6/2 | Division | / |
| 3 | 11\3 | Integer division | \ |
| 2 | 11 Mod 3 | Remainder (Modulo) | Mod |
| 5 | 2+3 | Addition | + |
| 3 | 6-3 | Subtraction | – |
Example
Dim y
y = 2 * 2 ' 4
y = 2 ^ 3 ' 8
y = 10 / 4 ' 2.5
y = 11 \ 4 ' 2
y = 10 Mod 5 ' 0
y = 10 + 5 ' 15
y = 10 - 5 ' 5Example in Excel:
If Range("A1").Value Mod 2 = 0 Then
MsgBox "The number in cell A1 is even."
End IfComparison Operators
Comparison operators in VBA are used to compare two values or expressions. The result of a comparison is always either True or False.
| Result | Example | Description | Operator |
|---|---|---|---|
| True | 3 < 6 | Less than | < |
| True | 4 <= 21 | Less than or equal to | <= |
| True | 6 > 3 | Greater than | > |
| True | 23 >= 23 | Greater than or equal to | >= |
| False | 7 = 2 | Equal to | = |
| True | 7 <> 2 | Not equal to | <> |
| – | – | Object reference comparison | Is |
| – | – | String pattern comparison | Like |
Example
Dim MyResult, Var1, Var2
MyResult = (45 < 35) ' False
MyResult = (45 = 45) ' True
MyResult = (4 <> 3) ' True
MyResult = ("5" > "4") ' True
Var1 = "5": Var2 = 4
MyResult = (Var1 > Var2) ' True
Var1 = 0: Var2 = Empty
MyResult = (Var1 = Var2) ' TrueFor more details about the Like operator and string pattern comparison, see this page.
Concatenation Operators
In VBA, there are two operators used for joining strings: + and &. Both can concatenate text, but using & is recommended to avoid ambiguity with numeric addition.
| Result | Example | Description | Operator |
|---|---|---|---|
| “Hello World” | “Hello” + “World” | String concatenation | + |
| “Hello World” | “Hello” & “World” | String concatenation | & |
When combining strings and numbers in the same expression (for example, “Total = ” + 5), use & to ensure the result is displayed correctly.
Logical Operators
Logical operators in VBA are used to combine multiple conditions in statements such as If or While.
| Result | Example | Description | Operator |
|---|---|---|---|
| True | (2>1) And (4<5) | Both conditions must be True | And |
| True | (2>1) Or (4<3) | At least one condition is True | Or |
| False | Not (3=3) | Negates the result | Not |
| True | (2>1) Xor (4<3) | Only one condition must be True | Xor |
| True | (2>1) Eqv (4<5) | Both conditions are either True or False | Eqv |
| False | (2>1) Imp (4>5) | Returns False if the first is True and the second is False | Imp |
Example:
If (Range("A1").Value > 0) And (Range("B1").Value > 0) Then
MsgBox "Both numbers are positive."
End IfSummary
- Operators are the main tools for working with data in VBA.
- Use
&for string concatenation to avoid unexpected errors. - Logical operators let you evaluate complex conditions easily.
Frequently Asked Questions about VBA Operators
What are operators in VBA?
Operators in VBA are symbols used to perform mathematical calculations, compare values, or concatenate strings. They help you create logical relationships between variables and constants.
How many types of operators are there in VBA?
VBA has four main operator types: Arithmetic for calculations, Comparison for evaluating expressions, Concatenation for combining strings, and Logical for combining conditions.
What’s the difference between & and + in VBA string concatenation?
Both & and + can join strings in VBA, but since + can also perform numeric addition, using & is safer to avoid ambiguity and type conversion issues.
What does the And operator do in VBA?
The And operator in VBA combines two conditions and returns True only if both are True; otherwise, it returns False.
Reference
Read More
Your First VBA Programme | Put Your Knowledge into Practice
Immediate Window | Understanding the VBA Immediate Window
VBA Built-in Functions | Complete List of Functions in Visual Basic
VBA Procedures | Definition, Types & Usage in Visual Basic
Constants in VBA | Types, Scope, and How to Use Them Effectively
Variable Scope in VBA | How to Access Variables across Different Parts of a Project
VBA Modules | Types of Modules and the Difference Between a Module and a Class
