Operators in Excel VBA

The signs and keywords we use to operate variable in VBA are called VBA Operators. For example, in the lines below lines =, +, >, & are operators.

sub Examp()
a= 12
b= 10
c= a+b
debug.print "The sum of a and b is" & c
end sub

There are five types of operators in any programming language, so in VBA
1. Assignment Operator
2. Arithmetic Operator
3. Comparison Operator
4. Logical Operator
5. Concatenation Operator

Let us take a look at each type of Operator in Excel VBA.

1. Assignment Operator (=)

This is the first operator you are going to use in any programming language. In VBA, it is used to assign values to variables. It is "=" (equals to).

We use this operator to assign values to variables in excel VBA. It is also used as a comparison operator in  VBA. We will talk about it later in this tutorial.

One simple example is

sub test()
 a=10
 Range("A1").value=a
end sub

In the above example, we first use the assignment operator "=" to assign value to variable "a" and then use the "=" operator to assign value of "a" to value of Range("A1").

2. Arithmetic Operators

The arithmetic operators are the same operators that we are using since childhood to do simple calculations. In Excel VBA, these operators are used for doing calculations on variables and numbers.  They are:

(+) Arithmetic Addition: This operator is used for adding two or more numbers or values of two or more variables. The lines below sum ups the values of the two variables and prints it on the Cell "A1".

Sub Test()
a=10
b=12
Range("A1").value=a+b
End Sub

A1 will have 22. This operator also works as a concatenation operator. If both, a and b will have the string values then the + operator will work as a concatenation operator. We will see how, later in the article.

(-) Arithmetic Subtraction: This operator is used for subtracting one value from another value of  variables. The line below subtracts the value of a from b and prints it in the Cell "A1".

Sub Test()
a=10
b=12
Range("A1").value=b-a
End Sub

A1 on the sheet will have 2.

(*) Arithmetic multiplication: This operator is used for multiplying or getting product of two or more numbers or values of two or more variables. The below lines multiplies the values of the two variables and prints it on the Cell "A1".

Sub Test()
a=10
b=12
Range("A1").value=a*b
End Sub

Cell A1 will have value 120.

(/) Arithmetic Division: This operator is used dividing one value from another. The line below divides the value b by variable a and prints it on the Cell "A1".

Sub Test()
a=10
b=12
Range("A1").value=b/a
End Sub

Cell A1 will have value 1.2.

(Mod) Arithmetic Remainder Operator in VBA: While most PLs use the % (modulus) for getting the remainder, in VBA we use the keyword Mod.  This operator is used to get the reminder after dividing one value from another. The line below divides the value b by variable a and prints the reminder value in cell A1.

Sub Test()
a=10
b=4
Range("A1").value=a Mod b
End Sub

Cell A1 will have value 2.

(^) Arithmetic Exponential: This operator is used to get the exponent of one value to another.  The lines below get us the value of 3 for exponential 4.

Sub Test()
a=3
b=4
Range("A1").value=a^b
End Sub

Cell A1 will have value 81 (3x3x3x3).

These operators in VBA follow the BODMAS rule. There are only 6 arithmetic operators in VBA. There are some operators that act as two types of operators like +. You will learn about them as you go through this tutorial.

3. Comparison Operators

When we want to compare two values in VBA, we use the comparison operators. The result of comparison operator is always Boolean. If the statement is true then the result in TRUE. If the statement is false then the value is False. These operators are frequently used in decision making in VBA. Let's see what they are:

(=) Equals: Yes, the = (equals to) sign is also used as comparison operator in VBA. When we want to check if the two variables are equal or not then we use this comparison operator.

Sub Test()
a=3
b=4
If a=b then 'It will return false
 MsgBox "The a and b are equal." 'this won't get executed.
Else
 MsgBox "The a and b are not equal"
End IF
End Sub

In the above example, we use the If statement and check if the values of a and b are equal. They are clearly not. Hence, the Else statement is printed.

You can check it by simply using the statement.

a=3
b=4
debug.print a=b

This will print False in the immediate window.

(<) Less Than: This is used to check if the left value is less than right value or not.

Sub Test()
a=3
b=4
If a<b then 'It will return True
 MsgBox "The a is less than b." 'this will be executed.
Else
 MsgBox "The a is not less than b." 'this won't get executed.
End IF
End Sub

In the above example, we check if the value of a is less than b. Since this is True, the if statement gets executed and Else doesn't.

You can check it by simply using the statement.

a=3
b=4
debug.print a<b

This will print True in the immediate window.

(<=) Less Than or Equal to: This is used to check if the left value is less than or equal to the right value or not.

Sub Test()
a=4
b=4
If a<=b then 'It will return True
 MsgBox "The a is less than or equal to b." 'this will be executed.
Else
 MsgBox "The a is not equal to or less than b." 'this won't get executed.
End IF
End Sub

In the above example, we check if the value of a is less than b. Since this is True, the If statement gets executed and Else doesn't.

You can check it by simply using the statement.

a=4
b=4
debug.print a<-b

This will print True in the immediate window.

(>) Greater Than: This is used to check if the left value is greater than the right value or not.

Sub Test()
a=3
b=4
If a>b then 'It will return False
 MsgBox "The a is greater than b." 'This won't get executed.
Else
 MsgBox "The a is not greater than b." 'This will be executed.
End IF
End Sub

In the above example, we check if the value of a is greater than b. Since this is False, the if statement won't get executed and Else does.

You can check it by simply using the statement.

a=3
b=4
debug.print a>b

This will print False in the immediate window.

(>=) Greater Than: This  is used to check if the left value is greater than or equal to the right value or not.

Sub Test()
a=3
b=4
If a>=b then 'It will return False
 MsgBox "The a is greater than or equal to b." 'This won't get executed.
Else
 MsgBox "The a is not greater than or equal to b." 'This will be executed.
End IF

In the above example, we check if the value of a is greater than or equal to b. Since this is False, the if statement won't get executed and Else does.

You can check it by simply using the statement.

a=3
b=4
debug.print a>=b

This will print False in the immediate window.

(<>) Not Equal To: This is used to check if the left value is not equal to the right value.

Sub Test()
a=3
b=4
If a<>b then 'It will return True
 MsgBox "The a is not equal to b," 'This will be executed.
Else
 MsgBox "The a and b are equal." 'This won't get executed.
End IF
End Sub

In the above example, we check if the value of a is not equal to b. Since this is True, the If statement will get executed and Else won't.

You can check it by simply using the statement.

a=3
b=4
debug.print a<>b

This will print True in the immediate window.

The above six operators are called comparison operators as we use them to compare two values or variables. You will be using them a lot in VBA to make decisions in VBA.

4. Logical Operators

Logical operators are used for doing logical operations on one or more variables. The result of such operations always results in TRUE or FALSE. The logical operators often (not always) are used to check more than one condition.

Below are the logical operators we use in VBA:

VBA AND Operator( And or *): The VBA AND operator are used to ensure that the conditions on its left and right are True. If any of the conditions is False then the whole statement will result in False. We use the keyword And or the sign * (asterisk) for AND operations.

Check the below statement:

Sub Test()
a = 10
b = 20
If a < 15 And b < 15 Then
 MsgBox "a and b both are less than 15." 'This doesn't get executed.
Else
 MsgBox "Either a or b is greater than or equal to 15." 'This gets executed.
End If
End Sub

When we run the above snippet, the second if statement doesn't get executed. Because the first statement on the left is true, but the statement on the right is False. Hence the whole statement returns False.

In most of the languages, the symbol & is used as AND operator but not in VBA. In VBA you can use the multiplication operator * (asterisk) as AND operator to ensure that both conditions are true.

In the above example, you can use the below if statement to do the same.

If (a < 15) * (b < 15) Then

You must use parenthesis to separate the conditional statements. Otherwise, the statements will follow the BODMAS rule and the result will inaccurate.

Q1: What will be the output of the below statement? Let me know in the comments section below:

debug.print a<>10 AND a=(b/2) AND a<b

When the value of a is 15 and b is 30. Write down in the comments section below.

VBA OR Operator (Or or +): The OR operator is used when we want to ensure that either the Left condition is TRUE or the Right condition is TRUE. If any of the two conditions is true then the result will be true. We use the OR keyword between two boolean statements. You can also use the + (plus) sign for OR operation. Just make sure that you use parenthesis properly to make statements clear while using + sign.

Examine the below code:

Sub Test()
a = 10
b = 20
If a < 15 Or b < 15 Then
 MsgBox "Either a or b is less than 15." 'This gets executed.
Else
 MsgBox "Neither a nor b is less than 15." 'This doesn't get executed.
End If
End Sub

If (a < 15) + (b < 15) Then

debug.print a<>10 Or a=(b/2) or a<b

Sub test()
a = 10
b = 10
If Not a = b Then
 MsgBox "a and b are not equal."
Else
 MsgBox "a and b both are equal."'This gets executed
End If
End Sub

debug.print a<>10 Or Not (a=(b/2) * a<b)

Sub test()
a="Exceltip"
b=" is number "
c= 1
d= 2
Debug.print a & b & c 'this will print "Exceltip is number 1"
Debug.print a + b & c & d 'this will print "Exceltip is number 12
'Debug.print a + b + c 'this will be an error.
End Sub

Debug.Print 10+20 ' prints 30. + works as addition operator.
Debug.Print (10<20) + (10>20) ' prints True. + works as Or operator.
Debug.Print "10" + "20" 'Prints 1020. The + sign works as concatenation operator.

Debug.Print 10*20 ' prints 200. The * works as multiplication operator.
Debug.Print (10<20) * (10>20) ' prints False. The + sign works as And operator.