Arithmetic Operators In Excel VBA

Arithmetic operators are used to perform calculations. Table 3.2 lists the arithmetic operators available in VBA:

Division

The division operator / returns the expected decimal result. However, the data type of the variable that stores the result is important:

• In the first example, the result is stored in a Double variable and will display the exact division result.
• In the second example, the result is stored in an Integer variable, which causes rounding.

Example 1: Division without rounding

Sub DivisionWithoutRounding()
    Dim x As Double
    x = 13 / 5
    MsgBox x  ' Displays 2.6
End Sub

Example 2: Division with rounding

Sub DivisionWithRounding()
    Dim x As Integer
    x = 13 / 5
    MsgBox x  ' Displays 3 due to rounding
End Sub

Integer Division (\ Operator)

Integer division is performed in two steps:

1. Both the dividend and divisor are individually rounded.
2. The rounded numbers are divided.
3. The fractional part (digits after the decimal point) is discarded (truncated).

Excel has no direct equivalent for this operator.

Examples:

Modulo Operator (Mod)

The Mod operator returns the remainder of a division. The calculation is done in two steps:

1. Dividend and divisor are individually rounded.
2. The remainder of the integer division is returned.

Excel provides the REST() function, which calculates the remainder without rounding. For example, REST(19.5; 3.2) returns 0.3 instead of 2.

Examples:

Exponentiation (^ Operator)

Exponentiation uses the ^ operator in both Excel and VBA. The function POTENZ() also exists and produces the same results.

Examples:

Example Procedure Using Arithmetic Operators

The following VBA procedure demonstrates the use of several arithmetic operators:

Sub ArithmeticOperators()
    ThisWorkbook.Worksheets("Sheet1").Activate
    ' Basic arithmetic
    Range("C1").Value = 5 * 3 - 6 / 3
    Range("C2").Value = 5 * (3 - 6) / 3
    ' Integer division
    Range("C4").Value = 19 / 4
    Range("C5").Value = 19 \ 4
    Range("C6").Value = 19 \ 4.6
    Range("C7").Value = 19.5 \ 4.2
    ' Modulo operator
    Range("D1").Value = 19 Mod 4
    Range("D2").Value = 19.5 Mod 3.2
    ' Exponentiation
    Range("D4").Value = 2 ^ 5
    Range("D5").Value = 3 ^ 2 ^ 3
    Range("D6").Value = 2 ^ 5.4
    Range("D7").Value = -2 ^ 5
End Sub

Operator Precedence and Associativity

• Multiplication and division have the same precedence and are evaluated from left to right as they appear in an expression.
• The same applies to addition and subtraction.
• Parentheses can override this precedence, forcing specific subexpressions to be evaluated first.
• Operations inside parentheses follow the normal precedence rules again.