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

When we execute the above code, the first message is displayed and the second message is skipped. Because a is less than 15 and b is not. It only takes one condition to be True with the OR operator to get the output as true.

You write the statement as:

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

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

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

When the value of a is 10 and b is 5. Write down in the comments section below.

VBA NOT Operator (Not): The Not operator is used to inverse the boolean value. In other words, the statement with Not operator only returns True if the statement before it is False. For example, if you use the Not keyword before a True Statement, it will result in False and vise-versa. In VBA, we use the Not keyword to check if something is not what True or False (?).

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

In the above code, statement Not a = b will return false. Initially, a=b is true but as we have used Not before it, the result is inversed and statement turns into False. In the above example, we have used the only statements before it. You can have as many statements as you want before Not operator. Just enclose them into parenthesis.

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

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

When the value of a is 10 and b is 5. Write down in the comments section below.

5. Concatenation Operators ( & or +)

These operators are used to concatenate strings. The symbol & is used to concatenate texts. It is the recommended operator to concatenate strings. However, you can also use the + sign to concatenate.

The plus sign only concatenates two variables when both of them are strings. If any of the string is non-string the + sign will work as an addition operator.

See the below code:

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

In the above code, the first two lines will work perfectly fine.  The third line will run into an error because we are trying to add text with numbers. To concatenate numbers we always use the & (amp).

Multifunctional Operators in VBA

Through the above examples, you must have learned that there are many operators that work differently in different situations.

The + sign works as the addition operator while working with numbers. While working with boolean values, the plus sign works as Or operator in VBA. When used with string values the plus operator works as a concatenation operator. The lines below are valid:

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.

The asterisk (*) sign works as a multiplication operator when operands are numeric. When the operands are boolean the asterisk works as And Operator.

The below lines of code work perfectly fine.

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.

So yeah guys, this all about the operators in VBA. I hope it was helpful. If you have any doubts regarding the operators in VBA, ask in the comment section below.

Related Articles:

The If ElseIf Statement in VBA|In If ElseIf statement, the next condition is only checked when the previous condition falls. When a condition is matched, the code in that block is executed and the control exits the If block.

Excel VBA Variable Scope | VBA too has scope specifiers. These scope specifiers can be used to set the visibility/scope of a variable in Excel VBA.

VBA Select Case Statement | The Select Case Statements are useful when you have too many conditions to check. They are excellent replacements of multiple If ElseIf statements.

Using Loop in VBA in Microsoft Excel | The loops in VBA enable us to do a similar task over and over without repetition of code. There are 3 types of loops in Excel VBA.

ByRef and ByVal Arguments |When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in original argument.

Display A Message On The Excel VBA Status Bar The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering

Turn Off Warning Messages Using VBA In Microsoft Excel 2016|This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube