VBA Operators

What is an operator?

Simple answer can be given using expression 4 + 5 is equal to 9. Here, 4 and 5 are called operands and + is called operator. VBA supports following types of operators:

• Arithmetic Operators
• Comparison Operators
• Logical (or Relational) Operators
• Concatenation Operators

The Arithmatic Operators

Following table shows all the arithmetic operators supported by VBScript language. Assume variable A holds 5 and variable B holds 10, then:

Operator	Description	Example
+	Adds two operands	A + B will give 15
-	Subtracts second operand from the first	A - B will give -5
*	Multiply both operands	A * B will give 50
/	Divide numerator by denumerator	B / A will give 2
%	Modulus Operator and remainder of after an integer division	B MOD 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 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

Following table shows all the Comparison operators supported by VBA. Assume variable A holds 10 and variable B holds 20, then:

Operator	Description	Example
=	Checks if the value of two operands are equal or not, if yes then condition becomes true.	(A = B) is False.
<>	Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.	(A <> B) is True.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(A > B) is False.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(A < B) is True.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(A >= B) is False.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes 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 table shows all the Logical operators 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 Expression becomes True.	a<>0 AND b<>0 is False.
OR	Called Logical OR Operator. If any of the two conditions is True, then condition becomes True.	a<>0 OR b<>0 is true.
NOT	Called Logical NOT Operator. It reverses the logical state of its operand. If a condition is True, then the Logical NOT operator will make it 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 True, result is True.	(a<>0 XOR b<>0) is false.

Example :

Try the following example to understand all the Logical operators available in VBA by creating a button and adding the below function:

Private Sub Constant_demo_Click()
  Dim a As Integer
  a = 10
  Dim b As Integer
  b = 0
      
  If a <> 0 And b <> 0 Then
     MsgBox ("AND Operator Result is : True")
  Else
     MsgBox ("AND Operator Result is : False")
  End If

  If a <> 0 Or b <> 0 Then
     MsgBox ("OR Operator Result is : True")
  Else
     MsgBox ("OR Operator Result is : False")
  End If

  If Not (a <> 0 Or b <> 0) Then
     MsgBox ("NOT Operator Result is : True")
  Else
     MsgBox ("NOT Operator Result is : False")
  End If

  If (a <> 0 Xor b <> 0) 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 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 table shows all the Concatenation operators supported by VBScript language. 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

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 below 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 below 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 Internet Explorer, then the above script will produce the following result:

Concatenated value:1 is MicrosoftVBScript

Concatenated value:2 is 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.

← Previous Page

Next Page →