Arithmetic Operators in VBA

VBA Operators

VBA operators are special tokens that instruct the compiler to “operate” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand. These are the values (i.e., literals) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

• Assignment Operator;
• Member Access Operators;
• Concatenation Operators;
• Arithmetic Operators;
• Comparison Operators;
• Logical Operators.

This article takes an in-depth look at the VBA arithmetic operators.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Arithmetic Operators

Arithmetic operators perform simple mathematic operations. Their operations include addition, subtraction, division, multiplication, exponentiation and modulus. The table below summarizes the different VBA arithmetic operators.

When several arithmetic operators with the same precedence appear together in an expression, evaluation proceeds in the order of appearance from left to right. Also, enclosing a part of an expression in parentheses causes it to be evaluated ahead of other parts.

Arithmetic Operators: Exponentiation (^)

Always put a space character right before the exponentiation operator, ^, to ensure that the VBA editor correctly interprets it, even in 64-bit environments.

That is because the operator creates LongLong data types in 64-bit environments. So, a compile error is thrown without the space character immediately before it.

The sample code below demonstrates the exponentiation (^) operator’s usage.

Arithmetic Operators: Negation (-)

The order of precision used by addition, subtraction, and negation differ from that used by multiplication. In the summary table above, the difference is subtle but it should be noted.

Did you spot the swapped positions of Double and Currency data types for the multiplication operator? Why the cheeky swap? Beats me.

The sample code below shows the negation (-) operator’s usage.

Arithmetic Operators: Multiplication (*), Division (/), and Integer Division (\)

The sample code below illustrates the multiplication (*), division (/), and integer division (\) operators’ usage.

As shown in the sample code above, for integer division the operands are rounded to Byte, Integer, or Long values before the division occurs. So, “14.4 \ 5” and “14.5 \ 5” both equate to “14 \ 5” which yields ‘2’. However, “14.6 \ 5” equates to “15 \ 5” which yields ‘3.’

Moreover, comparing the results of “14.5 / 5” and “14.5 \ 5,” shows that integer division truncates the result’s fractional part. On the other hand, the normal division does not.

Arithmetic Operators: Modulus (Mod)

The sample code below demonstrates the modulus (Mod) operator’s usage.

As shown in the sample code above, the modulus operator first rounds the operands (if floating-point or fractional values) to an Integer value before the dividing them and returning the remainder.

So, “5 Mod 3.4” equates to “5 Mod 3” which yields a remainder of ‘2.’ Likewise, “5.8 Mod 3” equates to “6 Mod 3” which yields a remainder of ‘0.’ Moreover, “4 Mod 5” yields ‘4’ as ‘5’ cannot divide into ‘4’ an integral number of times (i.e., “4 / 5” < 0).

Arithmetic Operators: Addition (+) and Subtraction (-)

The sample code below shows the addition (+) and subtraction (-) operators’ usage.