VBA Arithmetic Operators
VBA arithmetic operators are used to perform arithmetic operations. Which involve the calculation of numeric values represented by the variables, literals, function, constants, property calls, and other expressions.
You can perform arithmetic operations between two values in an expression together, such as adding, subtracting, multiplication, or division.
There are following arithmetic operators in VBA:
1. Addition (+): You can add two number in an expression together with the addition operator.
For example,
Output:
15
2. Subtraction (-): You can subtract the two numbers in an expression together with the subtraction operator.
For example,
Output:
5
3. Negation (~): Negation also uses the subtraction operator, but with only one number or operand.
For example,
Output:
-50
4. Multiplication (*): You can multiply the two numbers in an expression together with the multiplication operator.
For example,
5. Division (/): You can divide the two numbers in an expression together with the division operator.
For example,
Output:
10
Integer division returns the quotient, which means the integer that represents the number of times the divisor can divide into the dividend without consideration of any remainder.
The divisor and the dividend both must be integral types (Byte, SByte, Short, UShort, Integer, UInteger, Long, and ULong) for this operator. First, all other types must be converted to an integral type.
Output:
4
6. Exponentiation (^): Exponentiation operator is used to raising a number to the power of another number.
For example,
Output:
16
7. Modulus Operator (Mod): modulus arithmetic is performed using the Mod operator. This operator dividing the divisor into the dividend an integral number of times and returns the remainder.
If divisor and dividend both are integral types, then the return value is integral. And if the divisor and dividend both are the floating-point types then return value is also a floating-point.
For example,
Output:
4
OR
Output:
1.18
Bit-Shift Operation
A bit-shift operation is to perform an arithmetic shift on a bit pattern. This pattern is contained in the operand on the left. And the operand on the right specifies the number of positions to shift the pattern.
You can shift the pattern into the right with >> operator or into the left with << operator.
The data type of the pattern operand is Byte, SByte, Short, UShort, Integer, UInteger, Long, or ULong.
Arithmetic shifts are not circular, means the bits shifted off one end of the result are not redefined at the other end. The vacated positions of the bit by a shift are set as follows:
- 0 for the arithmetic left shit.
- 0 for the arithmetic right shift of a positive number.
- 0 for the arithmetic right shift of the unsigned data type (Byte, UShort, Uinteger, ULong).
- 1 for the arithmetic right shift of the negative number (SByte, Integer, Short, or Long).
Example, in below example, shifts the Integer value left or right both.
NOTE: Arithmetic shifts never generates overflow exceptions.
Bitwise Operations
In addition to being logical operators, And, Or, Not, and Xor also perform bitwise arithmetic when used on numeric values.
VBA Arithmetic Operator Example
Step 1: First add a Button to the excel sheet as we have shown earlier.
1. Change the name property such as btnAdd.
2. Right-click on the button.
3. Select View Code option.
4. You will get the code window as shown in the below screenshot.
Step 2: Write the following code in between Private Sub btnAdd_Click and End Sub as follows:
- Dim x As Integer, y As Integer
X = 4
Y = 5 - MsgBox x + y, vbOKonly, “Addition Operator”
Step 3: Click on the Save button.
Step 4: And close the code editor window.
Step 5: Then turn off the Design Mode button.
Step 6: The indicator is, it will change into a white background from the greenish coloured background, as shown in the below screenshot.
Step 7: Click on the Add Operator button.
Step 8: And you will get the output of the code as shown in the below screenshot.
