Arithmetic Operators – Visual Basic

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:

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.