An introduction to operators

An operator is a symbol that works on two operands to provide an output.

For example,

Let us consider expressions below:

1.  A + B = C

here

A, B and C are called operands

And

+ and = are called operators.

2. C > D

Here

C and D  are called operands

And

 “>” is called an operator.

Types of operators

Operators are classified into four types.

1. Arithmetic operators
2. Comparison operators
3. Relational or logical operators
4. Concatenation operators

Let us deep dive into each of these — along the way, I’ll give you a description and numerous examples.

Arithmetic operators

These operators are mathematical symbols that are used in calculations. Here is a list of arithmetic operators supported by VBA.

S.no	Operator	Description	Example
1	+	Indicates addition of two operands	4 + 5 will give 9
2	–	Subracts the second operand from the first	8 – 5 will give 3
3	*	Multiplies two operands on its sides	4 * 6 will give 24
4	/	Divides the numerator by the denominator	9 / 3 will give 3
5	Mod	The modulus operator. It is the remainder value after a division operation	10 % 3  will give a reminder value of “1” post a division.
6	^	Exponentiation operator	10 ^ 3 will give 1000

Sample programs

One program utilizing all of the operators above

Sub arith_oper_demo()

' declare variables
Dim a, b, c

' assign values to variables
a = 6
b = 7

' Try different calculations and print them
' addition operator
c = a + b
Debug.Print "Addition : " & c

' subtraction operator ( positive and negative outcomes )
c = b - a
Debug.Print "Subraction : " & c

c = a - b
Debug.Print "Subraction : " & c

' multiplication operator
c = a * b
Debug.Print "Multiplication : " & c

' Division operator
c = a / b
Debug.Print "Division : " & c

' Modulus operator
c = a Mod b
Debug.Print "Modulus : " & c

' Exponentiation operator
c = a ^ b
Debug.Print "Exponentiation : " & c

End Sub

Calculating the volume of a sphere

This program accept a radius value as a user input, then calculates and displays the volume in a message.

Sub vol_sp()

' declaration of variables
Dim Pi, r, vol

' assign values to the variables
Pi = 3.14  'or (22/7)
r = InputBox(" Enter the radius of the sphere ")

' calculation
vol = (4 / 3) * Pi * (r ^ 3)

' Display the volume calculated
MsgBox "The volume of the sphere with a radius of " & r & " is " & vol & "."


End Sub

Comparison operators

These are symbols that are used to compare values. Here is a list of comparison operators offered by VBA that are used for validation during programming.

S/N	Operator	Description	Example	Output
1	=	Equal to	If x = z Then	Returns true if the values of the operands ( x and y ) are equal, else it returns false
2	<	Less than	If x < z Then	If value of x is less than value of z, “True” is returned. Else it returns “False” .
3	>	Greater than	If x > z Then	If value of x is more than value of z, “True” is returned. Else it returns “False” .
4	<>	Not equal to	If x <> z Then	If value of x is different from value of z, “True” is returned. Else it returns “False” .
5	<=	Less than or equal to	If x <= z Then	If value of x is less than or same as value of z, “True” is returned. Else it returns “False” .
6	>=	Greater than or equal to	If x >= z Then	If value of x is more than or same as value of z, “True” is returned. Else it returns “False” .

Sample programs

Program that compares the age of two people

Sub compare_demo()

' declare variables
Dim fath_age, uncl_age

' assign values
fath_age = InputBox("Enter your father's age :")
uncl_age = InputBox("Enter your uncle's age :")

' age of uncle and father are being compared here
If fath_age > uncl_age Then
    MsgBox "Your father is elder than your uncle. "
ElseIf fath_age = uncl_age Then
    MsgBox "You father and your uncle are of the same age. "
Else
    MsgBox "Your uncle is elder than your father. "
End If

End Sub

Program to check the weight of a passenger’s luggage before boarding a flight

Sub compare_weight()

' declare variables
Dim allwd_wt, curr_wt

' assign values in kg
allwd_wt = 10
curr_wt = InputBox(" Enter the weigth of your luggage")

' check if the weight is within limits 
If curr_wt <= allwd_wt Then
    flag = 1
Else
    flag = 0
End If

' display appropriate message to the passenger
If flag = 1 Then
    MsgBox "The weight of your luggage is within the permitted limit. No further action is required."
Else
    MsgBox "The weight of your luggage is more than the permitted limit. Please remove some luggage and recheck before boarding the flight."
End If
End Sub

The comparison in the above program can also be written using a greater than operator instead of a “less than or equal to” operator.

If curr_wt > allwd_wt Then
    flag = 0
Else
    flag = 1
End If

Relational or logical operators

When there is more than one condition to evaluate, we can use logical operators to see how many different conditions are satisfied.

For example:

Males need to be 60 years old or more to be a senior citizen.

But females are considered to be senior citizens over 58 years of age.

If a program needs to be written to check the eligibility criteria, we have to validate both gender and age to conclude if the person is a senior citizen or not. Here is where logical operators come into the picture.

In the code snippet below, two logical operators OR and AND are used.

If age >= 60 Or ( age >= 58 and gender = ”Female” ) then
Debug.Print “ The person is a senior citizen .”
Else
Debug.Print “The person is not a senior citizen .“
End if

Below are the six logical operators offered by VBA.

S.no	Operator	Description	Example	Output and explanation
1	AND	This is the logical AND operator. It returns true if the conditions on its either sides return true.	Len( “ABC”) = 3 AND 5 + 3 = 3	First condition returns true but second condition returns false. As per the rule of this operator, even if one condition returns false, the final answer is false
2	OR	This is the logical AND operator. It returns true if any one of the conditions on its either sides returns true.	Len( “ABC”) = 3 OR (5 + 3) = 3	First condition returns true but second condition returns false. As per the rule of this operator, atleast one condition should return true for the final answer to be true. Hence the final output is TRUE.
3	NOT	This is called the Logical NOT Operator. It reverses  the logical state of its operand. If a condition returns true, then Logical NOT operator will return its opposite value( “False” is the opposite value in this case.)	NOT(Len( “ABC”) = 3 OR (5 + 3) = 4)	First condition returns true but second condition returns false. As per the rule of this operator, atleast one condition should return true for the final answer to be true. Hence the final output is true. The NOT operator reverses the answer true to FALSE.
4	XOR	The XOR operator is called “Logical Exclusion”. It is the combination of NOT and OR Operators.If only one  of the expressions evaluates to be True, the result is True. If none of the conditions returns true or more than one condition return true, the XOR operator returns false.	Len( “ABC”) = 3 XOR (5 + 3) = 3	Only one of the conditions returns true. Hence the final output is TRUE.
5	IS	This logical operator compares two object variables and returns true only if they have stored the same object in them.	Workbooks.Sheets(“ABC”) IS Workbooks.Sheets(“DEF”)	Both the worksheets are different and hence the output result would be “False”.
6	LIKE	This logical LIKE operator can compare two strings for inexact matches. It can check for patterns or strings within another string.	string1= “I love my country” if string1 LIKE “*love*” then	The condition checks if the string one starts with some characters, has the word “love” in it and again ends with some characters. Since these conditions are satisfied, the condition returns “True”.

Sample programs

Using the like operator

' validate directly without variables
    If "Tamil Nadu is in India" Like "Tamil*" Then
        Debug.Print "The string starts with the expected text"
    Else
        Debug.Print "The string does not start with the expected text"
    End If

Output

The string starts with the expected text.

Using the NOT operator

' checking for existence of a key in a dictionary object
    If Not( MyDict.Exists ("Class I") ) Then
        Debug.Print “Does not exist”
    End If

Using the XOR operator

Sub xor_demo()

' variable declaration
Dim amt, points

' Assign values to variables
amt = 2000
points = 400

' XOR demo - only one condition is satisfied
If amt > 200 Xor points > 500 Then
    Debug.Print ("You are winning a discount coupon_1")
End If

' XOR demo - both conditions are satisfied
If amt > 1500 Xor points > 200 Then
    Debug.Print ("You are winning a discount coupon_2")
End If

' XOR demo - no condition is satisfied
If amt > 6500 Xor points > 500 Then
    Debug.Print ("You are winning a discount coupon_3")
End If

End Sub

Concatenation operators

These are operators that can join two expressions.

Note: If both expressions are numbers, they get added up when you use “+”.

VBA offers the two operators below for concatenation purposes.

S.no	Operator	Description
1	+	Concatenates two values if one of them or both of them are strings. If both are numeric values, they get added up.
2	&	Concatenates (joins) the two expressions.

Sample program

Sub concat_demo()

' declaration of variables
Dim str1, str2
Dim num1, num2

' assigning values to the variables
str1 = "Would you like to "
str2 = "eat cake"

num1 = 32
num2 = 5

' using the concatenation operator "+"
Debug.Print str1 + str2
Debug.Print num1 + num2

' using the concatenation operator "&"
Debug.Print str1 & str2
Debug.Print num1 & num2

End Sub

Conclusion

These four types of operators are indispensable to build logic and divert the flow of control in any program. Many of us stick to only basic operators and fail to remember all that are available in VBA.

For example, you might need to do something only if a key value pair is missing in a dictionary object. In this case we use the “Exists” keyword to check for existence. However, if that keyword is used in combination with the NOT operator, the number of lines of code can be reduced ( an “else” block of code can be avoided). We need to make the best use of these operators based on the situation.