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.
Next >