Chapter 3 – Operators

Chapter 3 – Operators

In this chapter, we will learn how to perform basic calculations using VBA. We will also see practical examples in each section.

Arithmetic Operators

Let’s consider we have two variables x and y where x is 5 and y is 10

Operator Description Example Result
+ Adds to numbers x + y 15
Subtract second number from first x – y -5
* Multiple two numbers x * y 50
/ Divides numerator by the denominator y / x 2

Example 1: Addition

Sub Operators_Demo_Add()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim iSum As Integer
    
    iNum1 = 10
    iNum2 = 30
    
    iSum = iNum1 + iNum2
    
    MsgBox "Addition result is " & iSum

End Sub

Result:
VBA for Beginners

Example 2: Subtraction

Code:

Sub Operators_Demo_Subtraction()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim iSubtraction As Integer
    
    iNum1 = 10
    iNum2 = 8
    
    iSubtraction = iNum1 - iNum2
    
    MsgBox "Subtraction result is " & iSubtraction

End Sub

Result:

VBA for Beginners

Example 3: Multiple

Code:

Sub Operators_Demo_Multiple()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim iMultiple As Integer
    
    iNum1 = 15
    iNum2 = 5
    
    iMultiple = iNum1 * iNum2
    
    MsgBox "Multiple result is " & iMultiple

End Sub

Result:

VBA for Beginners

Example 4: Division

Code:

Sub Operators_Demo_Division()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim dDivision As Double
    
    iNum1 = 21
    iNum2 = 6
    
    dDivision = iNum1 / iNum2
    
    MsgBox "Division result is " & dDivision

End Sub

Result:

VBA for Beginners

Explanation: We have defined dDivision variable with Double data type to store decimal values

String Operators

Let’s consider we have two variables x and y where x is Microsoft and y is Excel

Operator Description Example Result
& Concatenates two values x & y MicrosoftExcel

Example:

Code:

Sub Operators_Demo_String()

    Dim strFirstName As String
    Dim strLastName As String
    Dim strFullName As String
    
    strFirstName = "Kailash"
    strLastName = "Sharma"
    
    strFullName = strFirstName & " " & strLastName
    
    MsgBox "Fullname is " & strFullName

End Sub

Result:
VBA for Beginners

Comparison Operators

Let’s consider we have two variables x and y where x is 5 and y is 10

Operator Description Example Result
= Checks if the value of the two operands are equal or not x = y FALSE
<> Checks if the value of the two operands are equal or not x <> y TRUE
> Checks if the value of the left operand is greater than the value of the right operand x > y FALSE
< Checks if the value of the left operand is less than the value of the right operand x < y TRUE
>= Checks if the value of the left operand is greater than or equal to the value of the right operand x >= y FALSE
<= Checks if the value of the left operand is less than or equal to the value of the right operand x <= y TRUE

Example 1: Equal

Code:

Sub Operators_Demo_Equal()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 10
    iNum2 = 10
    
    If iNum1 = iNum2 Then
        MsgBox "Both numbers are equal"
    Else
        MsgBox "Both numbers are not equal"
    End If

End Sub

Result:

VBA for Beginners

Example 2: Not Equal

Code:

Sub Operators_Demo_Not_Equal()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 10
    iNum2 = 10
    
    If iNum1 <> iNum2 Then
        MsgBox "Both numbers are not equal"
    Else
        MsgBox "Both numbers are equal"
    End If

End Sub

Result:

VBA for Beginners

Example 3: Greater Than

Code:

Sub Operators_Demo_Greater_Than()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > iNum2 Then
        MsgBox "iNum1 is greater than iNum2"
    Else
        MsgBox "iNum1 is equal or less than iNum2"
    End If

End Sub

Result:

VBA for Beginners

Example 4: Less Than

Code:

Sub Operators_Demo_Less_Than()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 < iNum2 Then
        MsgBox "iNum1 is less than iNum2"
    Else
        MsgBox "iNum1 is equal or greater than iNum2"
    End If

End Sub

Result:

VBA for Beginners

Example 5: Greater Than or Equal

Code:

Sub Operators_Demo_Greater_Than_Or_Equal()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 12
    iNum2 = 15
    
    If iNum1 >= iNum2 Then
        MsgBox "iNum1 is greater than or equal to iNum2"
    Else
        MsgBox "iNum1 is less than iNum2"
    End If

End Sub

Result:

VBA for Beginners

Example 6: Less Than or Equal

Code:

Sub Operators_Demo_Less_Than_Or_Equal()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 <= iNum2 Then
        MsgBox "iNum1 is less than or equal to iNum2"
    Else
        MsgBox "iNum1 is greater than iNum2"
    End If

End Sub

Result:

VBA for Beginners

Logical Operators

Let’s consider we have two variables x and y where x is 5 and y is 10

Operator Description Example Result
AND Returns TRUE if both conditions are true else FALSE x=5 AND y=5 FALSE
OR Returns TRUE if any condition is true else FALSE x=5 OR y=5 TRUE
NOT It is used to change the state of condition result NOT(x=5 OR y=5) FALSE

Example 1: And

Code:

Sub Operators_Demo_AND_1()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 10 And iNum2 < 15 Then
        MsgBox "Both conditions are True"
    Else
        MsgBox "At least one condition is False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here both conditions are True hence If block of the code is executed.

Example 2: And

Code:

Sub Operators_Demo_AND_2()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 20 And iNum2 < 15 Then
        MsgBox "Both conditions are True"
    Else
        MsgBox "At least one condition is False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here first condition (iNum1 > 20) is False hence Else block of the code is executed.

Example 3: And

Code:

Sub Operators_Demo_AND_3()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 20 And iNum2 < 10 Then
        MsgBox "Both conditions are True"
    Else
        MsgBox "At least one condition is False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here both conditions are False hence Else block of the code is executed.

Example 4: And

Code:

Sub Operators_Demo_AND_4()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim iNum3 As Integer
    
    iNum1 = 18
    iNum2 = 12
    iNum3 = 5
    
    If iNum1 > 15 And iNum2 < 15 And iNum3 = 5 Then
        MsgBox "All three conditions are True"
    Else
        MsgBox "At least one condition is False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here all three conditions are True hence If block of the code is executed.

Example 5: Or

Code:

Sub Operators_Demo_OR_1()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 20 Or iNum2 < 15 Then
        MsgBox "At least one condition is True"
    Else
        MsgBox "Both conditions are False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here second condition (iNum2 < 15) is True hence If block of the code is executed.

Example 6: Or

Code:

Sub Operators_Demo_OR_2()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 15 Or iNum2 < 15 Then
        MsgBox "At least one condition is True"
    Else
        MsgBox "Both conditions are False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here both conditions are True hence If block of the code is executed.

Example 7: Or

Code:

Sub Operators_Demo_OR_3()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If iNum1 > 20 Or iNum2 < 10 Then
        MsgBox "At least one condition is True"
    Else
        MsgBox "Both conditions are False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here both conditions are False hence Else block of the code is executed.

Example 8: Or

Code:

Sub Operators_Demo_OR_4()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    Dim iNum3 As Integer
    
    iNum1 = 18
    iNum2 = 12
    iNum3 = 5
    
    If iNum1 > 20 Or iNum2 < 15 Or iNum3 < 5 Then
        MsgBox "At least one condition is True"
    Else
        MsgBox "Both conditions are False"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here second conditions (iNum2 < 15) is True hence If block of the code is executed.

Example 9: Not

Code:

Sub Operators_Demo_NOT_1()

    Dim iNum1 As Integer
    
    iNum1 = 18
    
    If Not (iNum1 > 10) Then
        MsgBox "iNum1 is not greater than 10"
    Else
        MsgBox "iNum1 is greater than or equal to 10"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here condition is True but due to Not operator, result is reversed into False hence Else block of the code is executed.

Example 10: Not

Code:

Sub Operators_Demo_NOT_2()

    Dim iNum1 As Integer
    Dim iNum2 As Integer
    
    iNum1 = 18
    iNum2 = 12
    
    If Not (iNum1 > 10 And iNum2 < 10) Then
        MsgBox "At least one condition is False"
    Else
        MsgBox "Both conditions are True"
    End If

End Sub

Result:

VBA for Beginners

Explanation: Here second condition (iNum2 < 10) is False hence And Operator resulted in False but due to Not operator, result is reversed into True hence If block of the code is executed.

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs

Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business