VBA Arithmetic Operators are used to perform operations such as adding, subtracting, multiplying, and dividing of numbers. In these operations, numeric values are calculated and they are represented by variables, functions, constants, literals, property calls, and other expressions.
The following arithmetic operators are supported by VBA:
Addition (+): This operator is used to add up two or more numbers together.
For Example, assume variable A holds 5 and variable B holds 10, then
Dim x As Integer x= A * B. Where A=4 and B=10 x= 4 * 10 Output: 8
Subtraction ( – ): This operator is used to subtract numbers.
For Example, assume variable A holds 5 and variable B holds 3, then
Dim x As Integer x= A - B. Where A=5 and B=3 x= 5 - 3 Output: 2
Multiplication ( * ): This operator is used to multiply numbers.
For Example, assume variable A holds 4 and variable B holds 10, then
Dim x As Integer x= A * B. Where A=4 and B=10 x= 4 * 10 Output: 40
Division: This operator is used to divide numbers i.e, you can divide the numerator by the denominator.
For Example, assume variable A holds 8 and variable B holds 2, then
Dim x As Integer x= A/B. Where A=8 and B=2 x= 8/2 Output: 4
Exponentiation (^): This operator is used to raise a number to the power of another number.
For Example, assume variable A holds 4 and variable B holds 2, then
Dim x As Integer x= 4^2 Output: 16
Modulus Operator (Mod): modulus arithmetic is performed using the Mod operator. Divides a number and returns the remainder.
For example, 10 mod 3 Output: 1 OR A %=B means (A= A%B)
Bit-Shift Operation
Bit shifting is a process on all of the bits of a binary value that shifts them by a known number of places to the left or right, which is often used when the operand is treated as a sequence of bits rather than a whole. In other words, the operand is treated as individual bits that stand for something, and not as a value.
The pattern can be shifted 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, meaning the bits shifted off one end of the result are not redefined at the other end. Also, the arithmetic shift moves all the digits in the binary number along to the right. The vacated positions of the bit by a shift are set as follows:
- for the arithmetic left shift.
- for the arithmetic right shift of a positive number.
- for the arithmetic right shift of the unsigned data type (Byte, UShort, Uinteger, ULong).
- for the arithmetic right shift of the negative number (SByte, Integer, Short, or Long).
For Example, in the below example, shifts the Integer value left or right both.
Sub BitRightShiftt() Dim dValue As Double dValue = (2 ^ 31) + (2 ^ 30) + (200 / 0.0625) Debug.Print dValue dValue = Application.WorksheetFunction.Bitrshift(dValue, 3) Debug.Print dValue End Sub
Bitwise Operations
AND – requires 2 arguments
NOT – requires 1 argument and reverses all bits
OR – requires 2 arguments
XOR – requires 2 arguments
|
Operator |
Example |
Meaning |
|
AND |
A < B AND B < C |
The result is True if both A < B and B < C are true else false. |
|
OR |
A < B OR B < C |
The result is True if either A < B or B < C are true else false. |
|
NOT |
NOT (A > B) |
The result is True if A > B is false else true. |
VBA Arithmetic Operator Example
Step 1: First add a Button to the excel sheet as we have shown that before.
1. Change the name property or CommandButton1_Click.
2. Right-click on the button and select the View Code option. You will get the code window as shown in the screenshot below.
Step 2: Write the following code in between Private Sub CommandButton1_Click() and End Sub as follows:
Step 3: Proceed and click on the Save button. Close the code editor window and then turn off the Design Mode button. You can locate the design mode at the top of your screen.
Step 4: As shown in the screenshot below, the indicator will instantly change into a white background from the greenish-colored background.
Step 5: Click on the Add Operator button then you will get the output of the code as shown in the below screenshot.
