7 Operators
In programming, operators are special symbols that represent computations like addition, multiplication, and comparison. VBA provides a wide range of operators that can be used to perform various operations on variables and values.
In this chapter, we will cover the following types of operators:
- Arithmetic Operators
- Logical and Comparison Operators
7.1 Arithmetic Operators
Arithmetic operators are used to perform mathematical operations like addition, subtraction, multiplication, and division. In Table 7.1, you can see the list of arithmetic operators available in VBA.
| Function | Operator Symbol |
|---|---|
| Addition | + |
| Multiplication | * |
| Division | / |
| Subtraction | - |
| Exponentiation | ^ |
| String concatenation (join two strings together) | & |
| Integer division (the result is always an integer) | \ |
| Modulo arithmetic (returns the remainder of a division operation) | Mod |
7.1.1 Precedence
Operator precedence follows the standard mathematical order of operations (PEMDAS):
- Parentheses
- Exponentiation
- Multiplication and Division
- (Integer division)
- Modulo arithmetic
- Addition and Subtraction
See more on the Microsoft documentation for Operator Precedence in Visual Basic.
For example, in Listing 7.1, you can see how the order of operations affects the result of an expression. Without parentheses, the order of operations is as follows:
- Exponentiation (
^) - Multiplication (
*) - Modulo arithmetic (
Mod)
Sub ExamplePrecedence()
Dim result1 As Integer
Dim result2 As Integer
' Without parentheses
result1 = 15 Mod 4 * 3 ^ 2 '= 15 Mod (4 * (3 ^ 2))
'1st = 3 ^ 2 = 9
'2nd = 4 * 9 = 36
'3rd = 15 Mod 36 = 15
' With parentheses
result2 = (15 Mod (4 * 3)) ^ 2
Debug.Print "Result1 (without parentheses): " & result1 & vbCrLf & "Result2 (with parentheses): " & result2
End SubBesides the typical arithmetic operators, VBA also provides some additional operators like:
- Integer division (
\): Returns the integer part of the division operation. - Modulo arithmetic (
Mod): Returns the remainder of the division operation.
In Table 7.2, you can see how the Mod and the integer division operators work in VBA for different inputs.
Mod operator in VBA, you can calculate the remainder of a division operation.
Input (n) |
Result (n\3) |
Remainder (n Mod 3) |
|---|---|---|
| 0 | 0 | 0 |
| 1 | 0 | 1 |
| 2 | 0 | 2 |
| 3 | 1 | 0 |
| 4 | 1 | 1 |
| 5 | 1 | 2 |
| 6 | 2 | 0 |
| 7 | 2 | 1 |
| 8 | 2 | 2 |
| 9 | 3 | 0 |
| 10 | 3 | 1 |
| 11 | 3 | 2 |
Input (n) |
Result (n\4) |
Remainder (n Mod 4) |
|---|---|---|
| 0 | 0 | 0 |
| 1 | 0 | 1 |
| 2 | 0 | 2 |
| 3 | 0 | 3 |
| 4 | 1 | 0 |
| 5 | 1 | 1 |
| 6 | 1 | 2 |
| 7 | 1 | 3 |
| 8 | 2 | 0 |
| 9 | 2 | 1 |
| 10 | 2 | 2 |
| 11 | 2 | 3 |
7.1.2 Other Arithmetic Functions
In VBA, some arithmetic functions are available to perform specific operations. For example:
Sqr: Returns the square root of a number.- Syntax:
Sqr(Number)
- Syntax:
Power: Returns a number raised to a power.- Syntax:
Power(Number, Power)
- Syntax:
Abs: Returns the absolute value of a number.- Syntax:
Abs(Number)
- Syntax:
These functions can be used to perform more complex mathematical operations.
7.1.3 Exercises
7.1.3.1 Creating Functions for Arithmetic Operators
For each operator, in Table 7.1, create a function that takes on two numbers and return the resulting value. Make sure your function is tested on a Sub.
See below examples for the three first operators:
Add,Multiply, andDivide.
Add, Multiply, and Divide.
Function Add(num1 As Double, num2 As Double) As Double
Add = num1 + num2
End Function
Function Multiply(num1 As Double, num2 As Double) As Double
Multiply = num1 * num2
End Function
Function Divide(num1 As Double, num2 As Double) As Double
Divide = num1 / num2
End Function
Function Mode(num1 As Double, num2 As Double) As Double
Mode = num1 Mod num2
End Function
Sub TestAdd()
Debug.Print "Result (1+2) ="; Add(1, 2)
Debug.Assert Add(1, 2) = 3
Debug.Assert Add(4, 2) = 6
Debug.Assert Add(3, 3) = 6
End Sub
Sub TestMultiply()
Debug.Print "Result (1x2) ="; Multiply(1, 2)
Debug.Assert Multiply(1, 2) = 2
End Sub
Sub TestDivide()
Debug.Print "Result (3/2) ="; Divide(3, 2)
Debug.Assert Divide(3, 2) = 1.5
End Sub
Sub TestSqr()
Debug.Print "Result (4^(1/2)) ="; Sqr(4)
Debug.Assert Sqr(4) = 2
End Sub7.1.3.2 Using the Functions on Excel (2 Inputs)
Create a new Excel spreadsheet called “Arithmetic operators (2 inputs)”. Then, check if the functions you created are working as expected.
| Operand 1 | Operand 2 | Function Value | Expected Value |
|---|---|---|---|
| 1 | 2 | =Add(A2, B2) | =A1+B1 |
| 3 | 4 | =Multiply(A3, B3) | =A2*B2 |
| 5 | 2 | =Divide(A4, B4) | =A3/B3 |
7.1.3.3 Using the Functions on Excel (3 Inputs)
Create a new Excel spreadsheet called “Arithmetic operators (3 inputs)”. Then, check if the functions you created are working as expected.
| Operand 1 | Operand 2 | Operand 3 | Function Value | Expected Value |
|---|---|---|---|---|
| 1 | 2 | 3 | =Add(A2 +Add(B2, C2)) | =A1+B1+C1 |
| 3 | 4 | 5 | =Multiply(A3, Multiply(B3, C3)) | =A2B2C2 |
| 5 | 2 | 3 | =Divide(A4, Divide(B4, C4)) | =A3/B3/C3 |
7.1.3.4 Testing Functions
Add the programming logic to the functions defined below. Replace the Add code here! comment with the code that implements the function’s logic.
For each function, write useful tests (using Subs) to check if the function is working as expected.
For example, the template code:
Should become:
Option Explicit
Function CalculateCircleArea(radius As Double) As Double
CalculateCircleArea = radius ^ 2 * 3.14159265359
End Function
Sub TestCalculateCircleArea()
Debug.Assert Round(CalculateCircleArea(5), 2) = 77
End SubMake sure your functions are tested on different Subs.
Function CalculateAverageTwoNumbers( _
num1 As Double, _
num2 As Double) As Double
'Add code here!
End Function
Function CalculateHypotenuse( _
a As Double, _
b As Double) As Double
'Add code here!
End Function
Function ConvertPoundsToKgs(pounds As Double) As String
'Requirements:
' - The value in Kg should be rounded (2 decimal cases).
' - The returned value should be formatted as follows:
' Input: 200
' Output: "90,72 Kg = 200 lb"
'Add code here!
End Function7.2 Logical and Comparison Operators
Logical and comparison operators are used to create conditional expressions that evaluate to either True or False. These operators are primarily used in control structures like
- If statements,
- loops, and
- Select Case statements
to make decisions based on the truth or falsity of expressions.
| Function | Operator |
|---|---|
| Equal | = |
| Not Equal | <> |
| Greater Than | > |
| Less Than | < |
| Greater Than or Equal | >= |
| Less Than or Equal | <= |
| Logical AND | And |
| Logical OR | Or |
| Logical NOT | Not |
7.3 Truth Table for Logical Operators in VBA
7.3.1 AND (And) Operator
| Operand1 | Operand2 | Result |
|---|---|---|
| False | False | False |
| False | True | False |
| True | False | False |
| True | True | True |
7.3.2 OR (Or) Operator
| Operand1 | Operand2 | Result |
|---|---|---|
| False | False | False |
| False | True | True |
| True | False | True |
| True | True | True |
7.3.3 NOT (Not) Operator
| Operand | Result |
|---|---|
| False | True |
| True | False |
7.4 Precedence
All comparison operators have equal precedence, and all have greater precedence than the logical operators, but lower precedence than the arithmetic operators.
For logical operators the precedence order is:
- Negation (Not)
- Conjunction (And)
- Inclusive disjunction (Or)
7.4.1 Example
Option Explicit
Sub AssignmentVsComparison()
Dim x As Boolean
x = 1 = 1 '1 = 1 --> Evaluates to True, and True is assigned to x
End Sub
Sub PrecedenceExample()
Dim x As Integer
Dim y As Integer
Dim z As Integer
x = 5
y = 10
z = 15
Dim result1 As Boolean
result1 = Not x < y And y < z Or x < z
' (((Not True) And True) Or True)
' (( False And True) Or True)
' ( False Or True)
' True
Dim result2 As Boolean
result2 = Not (x < y And y < z Or x < z)
' Not (True And True Or True)
' Not ( True Or True)
' Not ( True )
' False
Debug.Print "Result 1: " & result1 & vbCrLf & "Result 2: " & result2
End Sub7.5 Testing with Debug.Assert
In VBA, Debug.Assert is a debugging tool used to add assertions to your code. An assertion is a statement that checks whether a particular condition is true, and if it’s not, it triggers an error or a breakpoint to help you identify and diagnose issues in your code during development and debugging.
7.6 Exercises
Add the logic in the functions below to make sure they pass their correspondent tests. See the examples below.
The exercises have to be answered WITHOUT using the If Then structure.
7.6.1 Examples
7.6.1.1 Example 1 - Check if a number is odd
Write a function that takes an integer as input and returns True if it’s an odd number, otherwise False.
7.6.1.2 Example 2 - Check if a string contains a specific character
Write a function called ContainsChar that takes a string and a character as parameters and returns True if the string contains the character and False otherwise.
7.7 Exercises
7.7.1 Check if a number is even
Write a function that takes an integer as input and returns True if it’s an even number, otherwise False.
7.7.2 Check if a number is divisible by both 3 and 5
Write a function that takes an integer as input and returns True if it’s divisible by both 3 and 5, otherwise False.
7.7.3 Check if a string contains a specific character
Write a function that takes a string and a character as input and returns True if the string contains the character, otherwise False.
Option Explicit
Function ContainsCharacter( _
inputString As String, _
character As String) As Boolean
'Add code here!
End Function
Sub TestContainsCharacter()
Debug.Assert ContainsCharacter("Hello, World!", "o") = True
Debug.Assert ContainsCharacter("VBA Programming", "z") = False
Debug.Assert ContainsCharacter("12345", "5") = True
End Sub7.7.4 Check if two numbers are both positive
Write a function that takes two numbers as input and returns True if both numbers are positive, otherwise False.
7.7.5 Check if a number is within a specific range
Write a function that takes a number and a range (minimum and maximum) as input, and returns True if the number is within that range, otherwise False.
Option Explicit
Function IsWithinRange( _
num As Double, _
minRange As Double, _
maxRange As Double) As Boolean
'Add code here!
End Function
Sub TestIsWithinRange()
Debug.Assert IsWithinRange(5, 1, 10) = True
Debug.Assert IsWithinRange(15, 1, 10) = False
Debug.Assert IsWithinRange(0, -1, 1) = True
End Sub7.7.6 Check if a string contains either “apple” or “banana”
Write a function that takes a string as input and returns True if it contains either “apple” or “banana” (case insensitive), otherwise False.
Option Explicit
Function ContainsFruit(inputString As String) As Boolean
'Add code here!
End Function
Sub TestContainsFruit()
Debug.Assert ContainsFruit("I love apples") = True
Debug.Assert ContainsFruit("Banana is my favorite fruit") = True
Debug.Assert ContainsFruit("Grapes are delicious") = False
End Sub7.7.7 Check if a number is not equal to 0
Write a function that takes a number as input and returns True if it’s not equal to 0, otherwise False.
7.7.8 Check if a string is not empty
Write a function that takes a string as input and returns True if it’s not empty, otherwise False.
7.7.9 Check if two boolean values are both True
Write a function that takes two boolean values as input and returns True if both are True, otherwise False.
Option Explicit
Function AreBothTrue(bool1 As Boolean, bool2 As Boolean) As Boolean
'Add code here!
End Function
Sub TestAreBothTrue()
Debug.Assert AreBothTrue(True, True) = True
Debug.Assert AreBothTrue(True, False) = False
Debug.Assert AreBothTrue(False, False) = False
End Sub
### Check if a number is greater than 10 and less than 20
Write a function that takes a number as input and returns
`True` if it's greater than 10 and less than 20, otherwise `False`.
```vb
Option Explicit
Function IsBetween10And20(num As Double) As Boolean
'Add code here!
End Function
Sub TestIsBetween10And20()
Debug.Assert IsBetween10And20(15) = True
Debug.Assert IsBetween10And20(5) = False
Debug.Assert IsBetween10And20(20) = False
End Sub7.7.10 Check if two numbers have the same fractional part
Write a function that takes two numbers as input and returns True if they have the same fractional part, otherwise False.
Option Explicit
Function HaveSameFractionalPart(num1 As Double, num2 As Double) As Boolean
'Add code here!
End Function
Sub TestHaveSameFractionalPart()
Debug.Assert HaveSameFractionalPart(3.25, 7.25) = True
Debug.Assert HaveSameFractionalPart(2.5, 2.75) = False
Debug.Assert HaveSameFractionalPart(1#, 2#) = True
End Sub7.7.11 Check if a number is a multiple of 4 and 6
Write a function that takes an integer as input and returns True if it’s a multiple of both 4 and 6, otherwise False.
7.7.12 Check if two strings are equal (case insensitive)
Write a function that takes two strings as input and returns True if they are equal (case insensitive), otherwise False.
Option Explicit
Function AreStringsEqual(str1 As String, str2 As String) As Boolean
'Add code here!
End Function
Sub TestAreStringsEqual()
Debug.Assert AreStringsEqual("Hello", "HELLO") = True
Debug.Assert AreStringsEqual("VBA", "excel") = False
Debug.Assert AreStringsEqual("ArArA", "ArArA") = True
End Sub7.7.13 Check if String Contains a Character and Meets Conditions
Write a function called IsValidString that takes three parameters: a string, a boolean, and a number. The function should return True if the following conditions are met:
- The string contains the character ‘
A’ (case insensitive) - The boolean parameter is
True. - The number parameter is greater than or equal to
10.
Otherwise, the function should return False.
Option Explicit
Function IsValidString( _
inputStr As String, _
flag As Boolean, _
num As Double) As Boolean
'Add code here!
End Function
Sub TestIsValidString()
Debug.Assert IsValidString("Sample String", True, 15) = True
Debug.Assert IsValidString("Another String", False, 5) = False
Debug.Assert IsValidString("A String with A", True, 8) = False
Debug.Assert IsValidString("ABC", True, 10) = True
End Sub