Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business
Are you a data enthusiast? Join us for a ride on your data and automate your stuffs

Chapter 3 – Operators

Chapter 3

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 *