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.

Table 7.1: Arithmetic operators 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:

1. Exponentiation (^)
2. Multiplication (*)
3. Modulo arithmetic (Mod)

Listing 7.1: Example of arithmetic operators precedence.

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 Sub

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

Table 7.2: Using the Mod operator in VBA, you can calculate the remainder of a division operation.

Using the Mod operator in VBA (n Mod 3)

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

Using the Mod operator (n Mod 4)

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)
• Power: Returns a number raised to a power.
  • Syntax: Power(Number, Power)
• Abs: Returns the absolute value of a number.
  • Syntax: Abs(Number)

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, and
• Divide.

Listing 7.2: Example of the first three functions: 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 Sub

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

Table 7.3: Using the functions on Excel with two input values

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.

Table 7.4: Using the functions on Excel with three input values

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:

Function CalculateCircleArea(radius As Double) As Double
    'Add code here!
End Function

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 Sub

Make 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 Function

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

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

Option Explicit

Sub ExampleDebugAssert()
    Dim x As Integer
    x = 5
    
    ' Code will stop! Condition fails!
    Debug.Assert x > 10
    
    ' Rest of your code
    Debug.Print "Code continues to execute."
End Sub

7.6 Exercises

Add the logic in the functions below to make sure they pass their correspondent tests. See the examples below.

Warning

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.

Option Explicit

Function IsOdd(num As Integer) As Boolean
    
    'Example: num = 10
    IsOdd = num Mod 2 = 1
    'IsOdd = ((10 Mod 2) = 1)
    'IsOdd =      (0     = 1)
    'IsOdd =           False
    
End Function

Sub TestIsOdd()

    Debug.Assert IsOdd(4) = False
    Debug.Assert IsOdd(7) = True
    Debug.Assert IsOdd(0) = False

End Sub

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.

Option Explicit

Function ContainsChar(text As String, char As String) As Boolean

    ContainsChar = (InStr(text, char) > 0)

End Function

Sub TestContainsChar()

    Debug.Assert ContainsChar("programming", "g") = True
    Debug.Assert ContainsChar("hello", "z") = False

End Sub

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.

Option Explicit

Function IsEven(num As Integer) As Boolean
    'Add code here!
End Function

Sub TestIsEven()
    Debug.Assert IsEven(4) = True
    Debug.Assert IsEven(7) = False
    Debug.Assert IsEven(0) = True
End Sub

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.

Option Explicit

Function IsDivisibleBy3And5(num As Integer) As Boolean
    'Add code here!
End Function

Sub TestIsDivisibleBy3And5()

    Debug.Assert IsDivisibleBy3And5(15) = True
    Debug.Assert IsDivisibleBy3And5(9) = False
    Debug.Assert IsDivisibleBy3And5(10) = False

End Sub

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 Sub

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

Option Explicit

Function AreBothPositive(num1 As Double, num2 As Double) As Boolean
    'Add code here!
End Function

Sub TestAreBothPositive()

    Debug.Assert AreBothPositive(5, 7) = True
    Debug.Assert AreBothPositive(-1, 2) = False
    Debug.Assert AreBothPositive(0, 0) = False

End Sub

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 Sub

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

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

Option Explicit

Function IsNotZero(num As Double) As Boolean
    'Add code here!
End Function

Sub TestIsNotZero()

    Debug.Assert IsNotZero(5) = True
    Debug.Assert IsNotZero(0) = False
    Debug.Assert IsNotZero(-3) = True

End Sub

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.

Option Explicit

Function IsNotEmpty(inputString As String) As Boolean
    'Add code here!
End Function

Sub TestIsNotEmpty()

    Debug.Assert IsNotEmpty("Hello") = True
    Debug.Assert IsNotEmpty("") = False
    Debug.Assert IsNotEmpty("  ") = True

End Sub

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 Sub

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

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

Option Explicit

Function IsMultipleOf4And6(num As Integer) As Boolean

    'Add code here!

End Function

Sub TestIsMultipleOf4And6()
    Debug.Assert IsMultipleOf4And6(12) = True
    Debug.Assert IsMultipleOf4And6(24) = True
    Debug.Assert IsMultipleOf4And6(8) = False
End Sub

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 Sub

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

1. The string contains the character ‘A’ (case insensitive)
2. The boolean parameter is True.
3. 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

7.7.14 Create a sub that runs all tests

Sub RunAllTests()
    
End Sub