Your Guide to Using Operators in VBA
An introduction to operators
An operator is a symbol that works on two operands to provide an output.
Let us consider expressions below:
- A + B = C
A, B and C are called operands
+ and = are called operators.
2. C > D
C and D are called operands
“>” is called an operator.
Types of operators
Operators are classified into four types.
- Arithmetic operators
- Comparison operators
- Relational or logical operators
- Concatenation operators
Let us deep dive into each of these — along the way, I’ll give you a description and numerous examples.
These operators are mathematical symbols that are used in calculations. Here is a list of arithmetic operators supported by VBA.
|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|
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
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.
|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” .|
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.
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”.|
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
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
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.
|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.|
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
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.