Return to VBA Code Examples

VBA – All Operators Explained

This tutorial will explain the operators used in VBA.

There are a few types of operators in VBA – Arithmetic Operators, Comparison Operators, Logical Operators and Concatenation Operators.

Arithmetic Operators

These operators allow us to use VBA as a calculator – to add, subtract, multiply, divide and to calculate to the power of (exponentiation).

Arithmetic Operator

Explanation
+

Addition

Subtraction

*

Multiplication

/

Division
^

Exponentiation

Addition

The addition or plus sign (+) operator is used to add numbers together in VBA.

VBAOperators Add

Subtraction

The subtraction or minus sign () operator is used to subtract numbers from each other in VBA.

VBAOperators Minus

Multiplication

The Multiplication (*) operator is used to multiply numbers together in VBA.

Division

The Division (/) operator is used to divide numbers in VBA

VBAOperators Divide

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Exponentiation (to the Power of)

The exponentiation  (^) operator is used to calculate a value to the power of a number.

VBAOperators PowerOf

Combining Operators

If we end up having a calculation that combines these operators, remember that the BODMAS (or PEDMAS) order of operators will apply.  In other words the calculation will be done in the order of brackets (parenthesis), power of (exponentiation) , division, multiplication, addition and then subtraction.

VBAOperators Order

The answer above is 92 – first the 6^2 is calculated (36), then the 10 is divided by 4 (2.5).  Next the 2.5 is multiplied by the 36 and finally the 2 is added last.

If we were to add brackets to this calculation, the answer would differ:

VBAOperators Brackets

Comparison Operators

Comparison Operators allow you to compare values and return a Boolean True or False as a result.

Comparison Operator

Explanation
=

Equal to

<>

Not Equal to

>

Greater than

>=

Greater than or Equal to

<

Less than

<=

Less than or Equal to

VBA Programming | Code Generator does work for you!

Equal To (=)

This operator checks to see if 2 values are equal.

VBAOperators EqualTo

As 5 is clearly not equal to 6, the result returned is False.

Not Equal To (<>)

This operator does the opposite of the Equal To operator and check to see if the values are NOT equal.

VBAOperators NotEqualTo

So in this case the answer will be True as 5 is NOT equal to 6.

Greater Than (>)

The Greater Than operator will check if the first value is greater than the second value.

VBAOperators EqualTo

As 6 is not greater than 6, the result would be False.

Greater than or Equal to (>=)

The Greater Than or Equal To operator will check if the first value is greater than or equal to the second value.

VBAOperators NotEqualTo

In this instance, as 6 is equal to 6, the example would return True.

Less Than (<)

The Less Than operator does the opposite of the Greater Than Operator and tests to see if the first value is less than the second value.

VBAOperators EqualTo

So in this case, keeping the same values of 6 and 6, the result is once again False as 6 is not less than 6.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Less Than or Equal To (<=)

The Less Than or Equal To operator will check if the first value is less than or equal to the second value.

The result of which is of course True as 6 is equal to 6!

Logical Operators

Logical Operators also return a true or false result.  They allow us to combine 2 or more comparison operators.

Logical Operator

Explanation
AND

Tests to see if BOTH conditions are true

OR

Tests to see if ONE of the conditions is True

NOT

Tests to see if a condition if true, but if it is True, will return False

XOR

A combination of NOT and OR logical operators –  will return true if one of the conditions is true.

AND

This allows us to use more than one comparison operator and check that both the conditions tested are true.

In this instance, as only one condition is true, the result will be False.

OR

This allows us to use more than one comparison operator and check that one of the conditions tested are true.

In this instance, as one of the conditions is true (7 is greater than 6), the result will be True.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

NOT

This will test to see if a condition is True, and if it is True, it will return False, of if the condition is False, it will return True.

In this instance, as 8 is greater than 6 and 7 is also greater than 6, the AND operator would have returned True.  However adding in the NOT operator means that the result returned is FALSE.

XOR

This operator is the combination of NOT and OR. It is also called logical exclusion. This will return true only if one of the conditions equals true otherwise it returns false.

Concatenation Operators

Concatenation allows us to add 2 of more values together, or to combine strings of text.

Logical Operator

Explanation
+

Is the same as the Addition operator and will add two values or two strings of text

&

Will join together 2 strings of text

 

The example above will give us a message box combining string A and string B, and adding a space between them – this is done using the addition operator (we are adding the strings together).

This example above will do exactly the same thing!  However, we are using the ampersand (&) operator.

If we amend the code that uses the  above to use numbers, it will add the 2 numbers together as in the Arithmetic Operators above

VBAOperators Concatenate1

However, if we use the ampersand (&) operator to do this, we will get a very different result!

VBAOperators Concatenate2

This is due to the fact that the ampersand (&) operator reads the 5 and the 6 as strings (even through we have declared them to be integers)  and literally joins them together!