Arithmetic Operators – Visual Basic
Job-ready Online Courses: Knowledge Awaits – Click to Access!
VBA Arithmetic Operators perform operations such as adding, subtracting, multiplying and dividing the numbers. In these operations, we calculate the numeric values and they are represented by variables, functions, constants, property calls and other expressions.
VBA Arithmetic Operators
Various VBA mathematical operators are:
Operation | Operator | Formula | Description |
Addition | + | =5+3 =8 Â | Summing of two or more values. |
Subtraction | – | =10-3 =7 | Subtracting the values. |
Multiplication | * | =5*2 =10 | Product of two values in the cells. |
Division | / | =10/2 =5 | Dividing the value of a cell by another value in the cell. |
Exponentiation | ^ | =2^3 =8 | Raising of the value by a power value. |
Negation | ~ | a=2 b=-a b=-2 | Negation applies on one number and it uses the subtraction operator. |
Modulus | mod | =11mod2 =1 | Divides the number and returns the remainder. |
VBA Arithmetic Operators Example
To perform arithmetic operations, follow the below steps:
1: Open an excel workbook and save it as an excel macro-enabled workbook.Â
2: Â Add a button using macros.Â
In order to create a macro,Â
- Go to the Developer tab and click on insert from the ribbon.
- Choose the command button.
- Assign a macro by providing a name and click on the new button.
3: Finally, press ok.Â
4: Once the code window appears, type the following code.
1. Addition Operator in VBA
Example of VBA Addition Operator
Sub Addition_Click() Dim a As Integer, b As Integer a = 5 b = 5 MsgBox a+b, DataFlair, "Addition Operator-DataFlair" End Sub
The user writes the code in the VBA editor.
Code Explanation:
Here, Sub Addition_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. The addition operator sums the a value, b value and this results in 10 as output.
The result will appear in a message box with the ‘Addition Operator – DataFlair’ title.
5: Click on save button or press ctrl+s.
6: Close the vba code editor window.
Your GUI appears as follows:
Output
2. Subtraction Operator in VBA
Example of VBA Subtraction Operator
Sub Subtraction_Click() Dim a As Integer, b As Integer a = 5 b = 5 MsgBox a-b, DataFlair, "Subtraction Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Subtraction_Click() is the sub procedure.Â
‘a’ and ‘b’ are integer data type variables. The subtract operator subtracts the b value from a and this results in 0 as output.
The result will appear in a message box with the ‘Subtraction Operator – DataFlair’ title.
The user writes the code in the VBA editor. Your GUI appears as below:
Output
3. Multiplication Operator in VBA
Example of VBA Multiplication Operator
Sub Product_Click()
Sub Product_Click() Dim a As Integer, b As Integer a = 5 b = 5 MsgBox a*b, DataFlair, "Product Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Product_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. Here, the output will be 25 as the a value is 5 and b value is 5. The product of a * b = 25.
The result will appear in a message box with the ‘Product Operator-DataFlair’ title.
The user writes the code in the VBA editor. Your GUI appears as below:
Output
4. Division Operator in VBA
Example of VBA Division Operator
Sub Division_Click() Dim a As Integer, b As Integer a = 5 b = 5 MsgBox a/b, DataFlair, "Division Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Division_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. The a value divides by b value and results the output i.e., (5/5 =1)
The result will appear in a message box with the ‘Division Operator – DataFlair’ title.
The user writes the code in the VBA editor. GUI Appears as below:
Output
5. Modulus Operator in VBA
Example of VBA Modulus Operator
Sub Modulus_Click() Dim a As Integer, b As Integer a = 13 b = 5 MsgBox a mod b, DataFlair, "Modulus Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Modulus_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. The output results in 3 because the reminder that the user would get after dividing 13/ 5 is 3.Â
The result will appear in a message box with the ‘Modulus Operator – DataFlair’ title.
The user writes the code in the VBA editor. Your GUI appears as below:
Output
6. Exponentiation Operator in VBA
Example of VBA Exponentiation Operator
Sub Exponent_Click() Dim a As Integer, b As Integer a = 5 b = 5 MsgBox a ^ b, DataFlair, "Exponentiation Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Exponent_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. The value of a is raised by the power of value b i.e., 5 ^ 5 = 3125.
The result will appear in a message box with the ‘Exponentiation Operator – DataFlair’ title.
The user writes the code in the VBA editor. Your GUI appears as below:
Output
7. Negation Operator in VBA
Example of VBA Negation Operator
Sub Negation_Click() Dim a As Integer, b As Integer a = 5 b = -a MsgBox b, DataFlair, "Negation Operator-DataFlair" End Sub
Code Explanation:
Here, Sub Negation_Click() is the sub procedure.
‘a’ and ‘b’ are integer data type variables. The b variable will hold the negation value of a i.e., – 5 here.
The result will appear in a message box with the ‘Negation Operator – DataFlair’ title.
The user writes the code in the VBA editor. Your GUI appears as below:
Output
Calculation Order in VBA
There are few calculations priority while performing the calculations in Excel sets. The priority order as follows:
1. Multiplications, Divisions, Exponentiation
2. Modulus
3. Addition and Subtraction
The performance of the operations takes place from Left to Right following the calculation property. Let’s look at a sample:
Example of the order of calculation in VBA
Sub Initialize() Dim calc As Integer calc = 5 + 5 * 2 / 5 MsgBox calc End Sub
Output
In this case 5+5*2/5, excel calculates the 5*2 first, followed by the 10/5 and only then the 5+2, resulting in 7.Â
Note: The variable assigns the value from right to left and the operation usually occurs from left to right.Â
Bit-Shift Operation in VBA
Performing an arithmetic shift on a bit pattern is known as bit-shift operation. Operand on the left holds the pattern and the operand on the right tells about the number of positions to be shifted in the pattern. There is no overflow exception in arithmetic shifts. You can shift the pattern to the right using >> operator and to the left using << operator.
The data type of the pattern operand is Byte, Signed Byte, Short, Unsigned Short, Integer, Unsigned Integer, Long or Unsigned Long.
Arithmetic shifts are not circular, which means the shifting of bits at one side does not reflect on the other side. The positions of the bit by a shift are as follows:
1. 0 for the arithmetic left shift.
2. 0 for the arithmetic right shift of a positive number, unsigned data type (byte, unsigned short, unsigned integer, unsigned long)
3. 1 for the arithmetic right shift of the negative number (Signed Byte, Integer, Short or Long).
Summary
1. Visual Basic Applications can also perform arithmetic calculations using arithmetic operators.
2. The user can also write user – defined functions utilizing the arithmetic operators.
Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google