VBA Operators

What are Excel VBA Operators?

VBA operators are similar to Excel operators and include mathematical, string, comparison, and logical operators. They are used to perform different types of operations. To learn basic VBA operators, let us look at a simple example, i.e., 4*5=20. “*” is the multiplication operator, and 4&5 are the operands. The multiplication operator comes under the mathematical operator’s category in VBA Excel.

Now, to use this operator in VBA Excel, open VBA and click on the Insert tab and then open a Module. Next, write the sub-procedure and use the operator in an expression. Here, we directly write it in the message box to see the result.

Sub Multiplication_Ex ()
MsgBox 4*5
End Sub

List of Mathematical Operators

To understand each operator, let us look at some examples. For this, consider two variables, X and Y-the value of X=10 and Y=15.

The table below shows the list of mathematical VBA operators with examples.

Operator	Definition	Example
+ (Addition)	Two operands/variables are added	X+Y will give 25
− (Subtraction)	It subtracts the second variable from the first	X-Y will give -5
* (Multiplication)	Both variables are multiplied using this operator.	X * Y will give 150
/ (Division)	It is used to divide the numerator by the denominator	Y/X will give 1.5
% (Modulus)	To obtain the remainder after dividing one value from another	Y % X will give 0
^ (Power)	To get the exponent of one value to another.	Y ^ X will give 1014

List of Comparison Operators for VBA

Comparison VBA operators are used to compare two variables or expressions. Its output will be True or False, similar to the Boolean data type.

Following are the different types of comparison VBA operators supported by VBA Excel:

• Equal Sign (=)
• Greater Than Sign (>)
• Greater Than or Equal to Sign (>=)
• Less than Sign (<)
• Not Equal to Sign (<>)

Let us begin to understand each type of comparison operator with examples.

Example for Equal To (=) operator

Equal To VBA operator checks if the value of both variables is equal. If it is equal, the output is True. It supports string as well as integer data types. Let us see the use of this operator. Here we will consider X and Y as Integer data types.

Follow the below steps to understand this operator.

Step 1: Open the module in VBA, write the sub-procedure, and define two variables of Integer data type. Then, assign a random numeric value to them, as shown below.

Step 2: Now, we will use Equals to the VBA operator to see whether the variables are equal. For this, use the If loop to display messages based on whether they are equal.

Code:

Sub EqualTo()
Dim X, Y As Integer
X = 12
Y = 12
If X = Y Then
MsgBox “Both are Equal”
Else
MsgBox “Both are not Equal”
End If
End Sub

Step 3: Run the code using the Run option in the VBA Ribbon or press the F5 key. We will see the result in the message box as shown below.

Let us take the variable values X and Y as “Hello.” Let us check if the values are equal using the Equal To VBA operator. Now, let us check if the values are equal using the Equal To VBA operator.

Code:

Sub EqualTo()
Dim X, Y As String
X = “Hello”
Y = “Hello”
If X = Y Then
MsgBox “The answer is True”
Else
MsgBox “The answer is False”
End If
End Sub

Run the code and see the result.

As you can see, the result is True because the strings are equal.

Example for Greater Than (>) operator

The Greater Than (>) VBA operator checks if the left variable value exceeds the right variable value.

Step 1: Open a module in VBA and write the code as shown below:

• For writing the code in the VBA editor, start with Sub-procedure and define X and Y variables as Integer.
• As the integer data type supports only numeric values, assign suitable values to X and Y variables.
• Declare the Greater than >, comparison VBA operator for X>Y. Assign the message using the MsgBox.

Sub GreaterThan_Ex()
Dim X, Y As Integer
X = 13
Y = 10
If X > Y Then
MsgBox “The answer is True”
Else
MsgBox “The answer is False”
End If
End Sub

Step 2: To see the result in the message box, run the code, and you can see the output below,

Example for Greater Than or Equal to (>=) operator

Greater Than or Equal to (>=) VBA operator, like the o operator checks if the left-side variable is greater than or equal to the right-side variable.

Step 1: Write the below code in the VBA editor.

Sub GreaterThan_EqualTo_Ex()
Dim X, Y As Integer
X = 15
Y = 10
If X >= Y Then
MsgBox “The answer is True”
Else
MsgBox “The answer is False”
End If
End Sub

Step 2: Next, we will make X=Y by changing the value of X to 10. See the below code.

Code:

Sub GreaterThan_EqualTo_Ex()
Dim X, Y As Integer
X = 10
Y = 10
If X >= Y Then
MsgBox “The answer is True”
Else
MsgBox “The answer is False”
End If
End Sub

Step.3: Run the code to see the result in the message box. The result for the cases will come as True.

Example for Less than Sign (<) operator

This VBA operator checks if the left-side variable value is less than the right-side variable value. If the answer is yes, the result is True.

Step 1: Write the below code to see whether one variable is lesser than the other.

Step 2: Run the above code, and the result will be displayed in the message box below.

In the result, we will see that both the values are equal.

Code:

Sub LessThan_Ex()
Dim X, Y As Integer
X = 9
Y = 10
If X < Y Then
MsgBox “Yes, X is less than Y”
Else
MsgBox “No, X is not less than Y”
End If
End Sub

Example for Not Equal to (<>) operator

This operator is used to check if both the variable values are not equal. Then, the result is True.

Step 1: Write the below code in a VBA module starting with a sub-procedure:

Step 2: Run the VBA code to see the result in the message box.

Code:

Sub NotEqualTo_Ex()
Dim X, Y As Integer
X = 20
Y = 10
If X <> Y Then
MsgBox “Yes, X and Y are not equal”
Else
MsgBox “No, X and Y are not equal”
End If
End Sub

Important Things to Note

• VBA operators perform different functions such as mathematical, comparison, and logical operations.
• Other types of VBA operators include Assignment, Mathematical/Arithmetic, Comparison, Logical, and Concatenation operators.
• Mathematical VBA operators such as addition and multiplication are used for calculations and require numeric variables.
• Comparison VBA operators are used to compare two variables in VBA; such operators’ outcome is always Boolean type (True or False). 
• Comparison VBA operators work for numeric as well as text values of variables.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to VBA Operators. Here we explain the different types of comparison operators to compare two variables or expressions with examples. You may learn more from the following articles –

• VBA Variable Types
• VBA 1004 Error
• VBA FileSystemObject