Excel Snippets

Excel VBA Operators

Excel VBA has four categories of operators:

Mathematical Operators
- used to perform basic mathematical calculations
String (Concatenation) Operators
- used to join two strings
Comparison Operators
- used to compare two expressions using mathematical comparisons
Logical (Relational) Operators
- used to compare two objects

The first category is also referred to as functional operators, the second involves data manipulation of string variables, and the last two are also known as conditional operators.

Excel VBA Mathematical Operators

Mathematical operators are used in simple mathematical expressions, such as x + y. Here x and y are called operands.

+         Addition
- adds two operands
-         Subtraction
- subtracts the right operand from the left
*         Multiplication
- multiplies two operands
/         Division
- divides the numerator by the denominator. Denominator must be non-zero.
\         Integer division
- divides a numerator by a denominator and returns a truncated value. The operands can be integer or non-integer. Denominator must be non-zero.
Mod    Modulus
- returns the remainder in integer division. If either the numerator or denominator are not whole numbers, a value of zero is returned.
^         Exponentiation (Power)
- raises one operand to the power specified by another operand. Note: This does not always work as expected on some Excel installations. If you get an error message, check to see if there are spaces before and after the caret (^).

Integer Divide

Sub integerDivide()
Dim X, Y as integer

X = 11

Y = 4

Msgbox X/Y

Msgbox X\Y

End Sub

The first Msgbox returns the result of regular division, which is 2.75. The second returns a value 2, the result of integer division

Modulus

Sub modulusArith()
Dim X, Y, Z as integer

X = 11

Y = 4

Z = X Mod Y

Msgbox Z

End Sub

A value of 3 is returned, the remainder after dividing.

Exponentiation

Sub modulusArith()
Dim X, Y, Z as integer

X = 11

Y = 4

Z = X ^ Y

MsgBox Z

End Sub

The result returned is 14641.