Home
How to Operator in Excel VBA Easily (12 Examples)

How to Operator in Excel VBA Easily (12 Examples)

Excel VBA Macros September 15, 2022

Welcome to Excel Avon

What is Operator in Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

In this article we will see what Operator in Excel VBA. Operator in Excel VBA refers to the type of calculation to be performed on a given set of values. Here we can use operators on different data types other than numeric (for example, text/character data types). Below you can see that we are giving some examples so that you will learn to use all the operators.

To use operator May we have to go to VBA so by opening excel we will go to Developer tab

simple-operator

and then click on visual macros.

simple-operator1

After entering in VBE we will go to the Insert tab and insert the module

Operator in excel (2)

Addition Operator in Excel VBA (‘+’ Operator in Excel VBA)

The Excel Arithmetic Operators and the order in which they are evaluated are shown in the table below. First we will explain the addition operator here. It adds two or more numeric values and returns a numeric value as return.

First, we will write the subroutine

Sub Use_Operators()

End Sub

and then we will define x as variant

Sub Use_Operators()
Dim x As Variant 
End Sub

we will add a comment for the addition, we will take some numeric value for addition.

Sub Use_Operators()
Dim x As Variant

'Addition (Operation For '+')
x = 12 + 9 + 23 + 78 
End Sub

 then we will print x with the help of debug.print

Sub Use_Operators()
Dim x As Variant

'Addition (Operation For '+')
x = 12 + 9 + 23 + 78
Debug.Print x
End Sub

Then we will click run button which will run the code.

simple-operator2

On clicking the Run button, this additional calculation will be printed in the Immediate window.

simple-operator3

 

Subtraction Operator in Excel VBA (‘-‘ Operator in Excel VBA)

Using the subtraction operator, we can subtract two or more numerical values and in return a numerical value is given. First, we write subroutine and then we will define x as variant

Sub Use_Operators()
Dim x As Variant
End Sub

We will add a comment for subtraction, we will take some numerical value for subtraction.

Sub Use_Operators() 
Dim x As Variant 

'Subtraction (Operation For '-') 
x = 100 - 23 - 87 -22
End Sub

 then we will print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Subtraction (Operation For '-') 
x = 100 - 23 - 87 -22
Debug.Print x
End Sub

Then we will click run button which will run the code.

simple-operator2

On clicking the Run button, this Subtraction calculation will be printed in the Immediate window.

simple-operator3 (2)

Multiplication Operator in Excel VBA (‘*’ Operator in Excel VBA)

In multiplication operator we multiply one numerical value by another and in return a numeric value is returned. Again, I will write the subroutine and then I will define x as variant

Sub Use_Operators()
Dim x As Variant
End Sub

Add a comment for Multiplication and take some numerical value for Multiplication.

Sub Use_Operators() 
Dim x As Variant 

'Multiplication (Operation For '*') 
x = 12 * 67 * 23
End Sub

 then we will print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Multiplication (Operation For '*') 
x = 12 * 67 * 23
Debug.Print x
End Sub

Then we will click run button which will run the code.

simple-operator2

On clicking the Run button, this Multiplication calculation will be printed in the Immediate window.

Operator-in -excel

Division Operator in Excel VBA (‘/’ Operator in Excel VBA)

For the division operation, the numerator (must be numeric) divides the value by the denominator (must be numeric) and returns a numeric value in return. Again, I will write the subroutine and then I will define x as variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Division and take some numerical value for Division.

Sub Use_Operators() 
Dim x As Variant 

'Division (Operation For '/') 
x = 100 / 4 / 5
End Sub

print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Division (Operation For '/') 
x = 100 / 4 / 5
Debug.Print x 
End Sub

After writing the division code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Division calculation will be printed in the Immediate window.

Operator-in -excel1

Exponential operator in Excel VBA (‘^’ Operator in Excel VBA)

We use the Exponential operator when we want to increase the power of the first numerical value by the second numerical value. For Exponentiation when we write numerical value and do not give space to Numerical, Error. Again, I will write the subroutine and then I will define x as variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Exponentiation and take some numerical value for Exponentiation.

Sub Use_Operators() 
Dim x As Variant 

'Exponentiation (Operation For '^') 
x = 2 ^ 2 ^ 3 
End Sub

Print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Exponentiation (Operation For '^') 
x = 2 ^ 2 ^ 3 
Debug.Print x 
End Sub

After writing the Exponentiation code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Exponentiation calculation will be printed in the Immediate window.

Operator-in -excel2

Concatenation Operator in Excel VBA (‘&’ Operator in Excel VBA)

Excel also has an operator that helps to join two or more text strings and print a single text line when the code is run. We can use the ampersand (&) operator to join two or more strings and get a single text line. Again, I will write the subroutine and then I will define x as variant. 

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Concatenation Operation and take some Text for Concatenation.

Sub Use_Operators() 
Dim x As Variant 

'Concatenation (Operation For '&') 
x = "James is " & "Divining " & "a car"
End Sub

Print x with the help of Debug.print

PrintSub Use_Operators() 
Dim x As Variant 

'Concatenation (Operation For '&') 
x = "James is " & "Divining " & "a Car."
Debug.Print x
End Sub

After writing the Concatenation code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Concatenation calculation will be printed in the Immediate window.

Operator-in -excel3

Greater Operator in Excel VBA (‘>’ Operator in Excel VBA)

This operator compares two values (provided through cells or separately) and return a logical output, i.e., TRUE or FALSE. Greater Operators returns TRUE if the value in cell A1 (First Value) is greater than the value in cell B1 (Second Value) else returns FALSE. Again, I will write the subroutine and then I will define x as variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Greater Operation and take some numeric for Greater.

Sub Use_Operators() 
Dim x As Variant 

'Greater (Operation For '>') 
x = 2 > 3
End Sub

Print x with the help of Debug.print

Sub Use_Operators() 
Dim x As Variant 

'Greater (Operation For '>') 
x = 2 > 3
Debug.Print x
End Sub

After writing the Greater code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Greater calculation will be printed in the Immediate window.

Operator-in-excel4

Here it is false printed we wrote is 2 is greater than 3 which is absolutely wrong, so it is false printed

Less Operator in Excel VBA (‘<‘ Operator in Excel VBA)

Less like Greater also compares two values (provided through cells or separately) and returns a logical output, i.e., TRUE or FALSE. If the value in the first value is less than the value in the second value, it returns TRUE; Otherwise returns FALSE. Again, I would write the subroutine and then I would define x as the variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Less Operation and take some numeric for Less.

Sub Use_Operators() 
Dim x As Variant 

'Less (Operation For '<')
x = 2 < 3 
End Sub

Print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Less (Operation For '<')
x = 2 < 3
Debug.print x
End Sub

After writing the code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Less calculation will be printed in the Immediate window. True is printed, because 3 is greater than 2

Operator-in -excel5

Less Than or Equal operator in Excel VBA

It returns TRUE if the value in cell A1 (First Value) is less than or equal to the value in cell B1 (Second Value); else return FALSE. I would write the subroutine and then I would define x as the variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Less than or equal Operation and take some numeric.

Sub Use_Operators() 
Dim x As Variant 

'Less than or equal (Operation For '<=')
x = 2 <= 3 
End Sub

Print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Less than or equal (Operation For '<=')
x = 2 <= 3
Debug.print x
End Sub

After writing the code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Less than Comparison will be printed in the Immediate window. 2 is Smaller than 3 hence true print on Immediate Window. 

Operator-in -excel5

Not Equals To operator

Not Equals to It compares two values and returns TRUE and FALSE if both the values are not equal to each other. I would write the subroutine and then I would define x as the variant.

Sub Use_Operators() 
Dim x As Variant 
End Sub

Add a comment for Not Equal to Operation and take two numeric.

Sub Use_Operators() 
Dim x As Variant 

'Does Not Equal to(Operation For '<>')
x = 2 <> 3 
End Sub

Print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

'Does Not Equal to (Operation For '<>')
x = 2 <> 3
Debug.print x
End Sub

After writing the code, the code will run from the play button.

simple-operator2

On clicking the Run button, this Less than Comparison will be printed in the Immediate window. 2 Does not Equal to 3 hence true print on Immediate Window. 

Operator-in -excel5

Now we will solve a calculation

Sub Use_Operators() 
Dim x As Variant 
End Sub

take a Calculation.

Sub Use_Operators() 
Dim x As Variant 

x = ((2 - 1 +( 21 / 3) * 9) ^2)
End Sub

Print x with the help of Debug. Print

Sub Use_Operators() 
Dim x As Variant 

x = ((2 - 1 +( 21 / 3) * 9) ^2)
Debug.print x
End Sub

After writing the code, the code will run from the play button.

simple-operator2

Now Result printed

Operator-in -excel6

So, I hope you have understood How to use Operator in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

Learn More Topic in VBA here

You can also see well-explained video here

Related Posts

Leave a Reply