Contents

## 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.

### 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
c = a + b
Debug.Print "Addition : " &amp; c

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

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

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

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

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

' Exponentiation operator
c = a ^ b
Debug.Print "Exponentiation : " &amp; 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 " &amp; r &amp; " is " &amp; vol &amp; "."

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.

### 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 &gt; uncl_age Then
ElseIf fath_age = uncl_age Then
MsgBox "You father and your uncle are of the same age. "
Else
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 &lt;= 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 &gt; 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 &gt;= 60 Or ( age &gt;= 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.

### 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
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 &gt; 200 Xor points &gt; 500 Then
Debug.Print ("You are winning a discount coupon_1")
End If

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

' XOR demo - no condition is satisfied
If amt &gt; 6500 Xor points &gt; 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.

### 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 "&amp;"
Debug.Print str1 &amp; str2
Debug.Print num1 &amp; 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.