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.
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
Operator Precedence in VBA | Order of Arithmetic and Logical Operations with Examples
Variables in VBA | How to Declare Variables and Methods
VBA Code Editor | Create, Edit and Save VBA Codes
Introduction to VBA Code Structure: From Zero to Your First Function
Why VBA? | Advantages of Using and Learning VBA as a Programming Language
Where Did It All Begin? A Look at the Turbulent History of VBA and Its Future
Basic VBA Tutorial | Fundamental Concepts to Start Visual Basic Programming
