Home » Tutorials » VBA Tutorials » VBA – Operators

VBA – Operators

VBA - Operators Shout4Education
An Operator can be defined using a simple expression – 4 + 5 is equal to 9. Here, 4 and 5 are called operands and + is called operator. VBA supports following types of operators −
  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical (or Relational) Operators
  4. Concatenation Operators

The Arithmetic Operators

Following arithmetic operators are supported by VBA.
Assume variable A holds 5 and variable B holds 10, then −
Operator Description Example
+ Adds the two operands A + B will give 15
Subtracts the second operand from the first A – B will give -5
* Multiplies both the operands A * B will give 50
/ Divides the numerator by the denominator B / A will give 2
% Modulus operator and the remainder after an integer division B % A will give 0
^ Exponentiation operator B ^ A will give 100000

VBA – Arithmetic Operators

Following arithmetic operators are supported by VBA.
Assume variable A holds 5 and variable B holds 10, then −
Operator Description Example
+ Adds the two operands A + B will give 15
Subtracts the second operand from the first A – B will give -5
* Multiplies both the operands A * B will give 50
/ Divides the numerator by the denominator B / A will give 2
% Modulus operator and the remainder after an integer division B % A will give 0
^ Exponentiation operator B ^ A will give 100000

Example

Add a button and try the following example to understand all the arithmetic operators available in VBA.
Private Sub Constant_demo_Click()
   Dim a As Integer
   a = 5
Dim b As Integer
   b = 10
Dim c As Double
c = a + b
   MsgBox ("Addition Result is " & c)
c = a - b
   MsgBox ("Subtraction Result is " & c)
c = a * b
   MsgBox ("Multiplication Result is " & c)
c = b / a
   MsgBox ("Division Result is " & c)
c = b Mod a
   MsgBox ("Modulus Result is " & c)
c = b ^ a
   MsgBox ("Exponentiation Result is " & c)
End Sub
When you click the button or execute the above script, it will produce the following result.
Addition Result is 15
Subtraction Result is -5 
Multiplication Result is 50 
Division Result is 2
Modulus Result is 0
Exponentiation Result is 100000

The Comparison Operators

There are the following comparison operators supported by VBA.
Assume variable A holds 10 and variable B holds 20, then −
Operator Description Example
= Checks if the value of the two operands are equal or not. If yes, then the condition is true. (A = B) is False.
<> Checks if the value of the two operands are equal or not. If the values are not equal, then the condition is true. (A <> B) is True.
> Checks if the value of the left operand is greater than the value of the right operand. If yes, then the condition is true. (A > B) is False.
< Checks if the value of the left operand is less than the value of the right operand. If yes, then the condition is true. (A < B) is True.
>= Checks if the value of the left operand is greater than or equal to the value of the right operand. If yes, then the condition is true. (A >= B) is False.
<= Checks if the value of the left operand is less than or equal to the value of the right operand. If yes, then the condition is true. (A <= B) is True.
Related:  VBA - Strings

VBA – Comparison Operators

There are the following comparison operators supported by VBA.
Assume variable A holds 10 and variable B holds 20, then −
Operator Description Example
= Checks if the value of the two operands is equal or not. If yes, then the condition is true. (A = B) is False.
<> Checks if the value of the two operands is equal or not. If the values are not equal, then the condition is true. (A <> B) is True.
> Checks if the value of the left operand is greater than the value of the right operand. If yes, then the condition is true. (A > B) is False.
< Checks if the value of the left operand is less than the value of the right operand. If yes, then the condition is true. (A < B) is True.
>= Checks if the value of the left operand is greater than or equal to the value of the right operand. If yes, then the condition is true. (A >= B) is False.
<= Checks if the value of the left operand is less than or equal to the value of the right operand. If yes, then the condition is true. (A <= B) is True.

Example

Try the following example to understand all the Comparison operators available in VBA.
Private Sub Constant_demo_Click()
   Dim a: a = 10
   Dim b: b = 20
   Dim c
If a = b Then
      MsgBox ("Operator Line 1 : True")
   Else
      MsgBox ("Operator Line 1 : False")
   End If
If a<>b Then
      MsgBox ("Operator Line 2 : True")    
   Else
      MsgBox ("Operator Line 2 : False")    
   End If
If a>b Then
      MsgBox ("Operator Line 3 : True")    
   Else
      MsgBox ("Operator Line 3 : False")    
   End If
If a<b Then
      MsgBox ("Operator Line 4 : True")    
   Else
      MsgBox ("Operator Line 4 : False")    
   End If
If a>=b Then
      MsgBox ("Operator Line 5 : True")    
   Else
      MsgBox ("Operator Line 5 : False")    
   End If
If a<=b Then
      MsgBox ("Operator Line 6 : True")
   Else
      MsgBox ("Operator Line 6 : False")
   End If
End Sub
When you execute the above script, it will produce the following result.
Operator Line 1 : False
Operator Line 2 : True
Operator Line 3 : False
Operator Line 4 : True
Operator Line 5 : False
Operator Line 6 : True

The Logical Operators

Following logical operators are supported by VBA.
Assume variable A holds 10 and variable B holds 0, then −
Operator Description Example
AND Called Logical AND operator. If both the conditions are True, then the Expression is true. a<>0 AND b<>0 is False.
OR Called Logical OR Operator. If any of the two conditions are True, then the condition is true. a<>0 OR b<>0 is true.
NOT Called Logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true, then Logical NOT operator will make false. NOT(a<>0 OR b<>0) is false.
XOR Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True. (a<>0 XOR b<>0) is true.
Related:  VBA - Macro Comments

VBA – Logical Operators

Following logical operators are supported by VBA.
Assume variable A holds 10 and variable B holds 0, then −
Operator Description Example
AND Called Logical AND operator. If both the conditions are True, then the Expression is true. a<>0 AND b<>0 is False.
OR Called Logical OR Operator. If any of the two conditions are True, then the condition is true. a<>0 OR b<>0 is true.
NOT Called Logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true, then Logical NOT operator will make false. NOT(a<>0 OR b<>0) is false.
XOR Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True. (a<>0 XOR b<>0) is true.

Example

Try the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.
Private Sub Constant_demo_Click()
   Dim a As Integer
   a = 10
   Dim b As Integer
   b = 0
If a <>  And b <>  Then
      MsgBox ("AND Operator Result is : True")
   Else
      MsgBox ("AND Operator Result is : False")
   End If
If a <>  Or b <>  Then
      MsgBox ("OR Operator Result is : True")
   Else
      MsgBox ("OR Operator Result is : False")
   End If
If Not (a <>  Or b <> ) Then
      MsgBox ("NOT Operator Result is : True")
   Else
      MsgBox ("NOT Operator Result is : False")
   End If
If (a <>  Xor b <> ) Then
      MsgBox ("XOR Operator Result is : True")
   Else
      MsgBox ("XOR Operator Result is : False")
   End If
End Sub
When you save it as .html and execute it in the Internet Explorer, then the above script will produce the following result.
AND Operator Result is : False
OR Operator Result is : True
NOT Operator Result is : False
XOR Operator Result is : True

The Concatenation Operators

Following Concatenation operators are supported by VBA.
Assume variable A holds 5 and variable B holds 10 then −
Operator Description Example
+ Adds two Values as Variable. Values are Numeric A + B will give 15
& Concatenates two Values A & B will give 510
Assume variable A = “Microsoft” and variable B = “VBScript”, then −
Operator Description Example
+ Concatenates two Values A + B will give MicrosoftVBScript
& Concatenates two Values A & B will give MicrosoftVBScript
Note − Concatenation Operators can be used for both numbers and strings. The output depends on the context, if the variables hold numeric value or string value.

VBA – Concatenation Operators

Following Concatenation operators are supported by VBA.
Assume variable A holds 5 and variable B holds 10 then −
Operator Description Example
+ Adds two Values as Variable. Values are Numeric A + B will give 15
& Concatenates two Values A & B will give 510
Related:  VBA - Variables

Example

Try the following example to understand the Concatenation operator available in VBScript −
Private Sub Constant_demo_Click()
   Dim a as Integer : a = 5
   Dim b as Integer : b = 10
   Dim c as Integer
c = a + b  
   msgbox ("Concatenated value:1 is " &c) 'Numeric addition
c = a & b
msgbox ("Concatenated value:2 is " &c) 'Concatenate two numbers 
End Sub
Try the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.
Concatenated value:1 is 15

Concatenated value:2 is 510

Concatenation can also be used for concatenating two strings. Assume variable A = “Microsoft” and variable B = “VBScript” then −
Operator Description Example
+ Concatenates two Values A + B will give MicrosoftVBScript
& Concatenates two Values A & B will give MicrosoftVBScript

Example

Try the following example to understand all the Logical operators available in VBA by creating a button and adding the following function.
Private Sub Constant_demo_Click()
   Dim a as String : a = "Microsoft"
   Dim b as String : b = "VBScript"
   Dim c as String
c = a + b 
   msgbox("Concatenated value:1 is " &c) 'addition of two Strings
c = a & b
msgbox("Concatenated value:2 is " &c) 'Concatenate two String
End Sub
When you save it as .html and execute it in the Internet Explorer, then the above script will produce the following result.
Concatenated value:1 is MicrosoftVBScript
Concatenated value:2 is MicrosoftVBScript

Leave a Comment