Lesson 4: Operators in Excel VBA

Mastering the building blocks of VBA expressions

Operators are fundamental elements in VBA programming used to perform computations, make comparisons, and combine expressions. Understanding operators is crucial for writing effective VBA code. This lesson covers all three categories of VBA operators:

Arithmetic Operators - For mathematical calculations
Comparison Operators - For evaluating conditions
Logical Operators - For combining multiple conditions

4.1 Arithmetic Operators

Arithmetic operators perform mathematical operations on numerical values. They form the basis of calculations in your VBA programs.

Table 4.1: Arithmetic Operators in VBA
Operator	Description	Example	Result
^	Exponentiation (power of)	`3 ^ 2`	9
*	Multiplication	`5 * 4`	20
/	Division (floating-point)	`10 / 3`	3.333...
\	Integer division (discards remainder)	`10 \ 3`	3
Mod	Modulus (division remainder)	`10 Mod 3`	1
+	Addition or string concatenation	`"Hello " + "World"`	"Hello World"
&	String concatenation (recommended)	`"Total: " & 100`	"Total: 100"

Best Practice Tip:

Always use the & operator for string concatenation instead of + to avoid type mismatch errors when working with mixed data types.

Practical Example: Calculating Discounts

Sub CalculateDiscount()
    Dim originalPrice As Double
    Dim discountRate As Double
    Dim finalPrice As Double
    
    originalPrice = 150.99
    discountRate = 0.15  ' 15% discount
    
    ' Calculate final price with discount
    finalPrice = originalPrice * (1 - discountRate)
    
    ' Display result
    MsgBox "Original Price: $" & originalPrice & vbCrLf & _
           "Discount: " & discountRate * 100 & "%" & vbCrLf & _
           "Final Price: $" & Round(finalPrice, 2)
End Sub

4.2 Comparison Operators

Comparison operators evaluate relationships between values and return True or False. They're essential for decision-making in your code.

Table 4.2: Comparison Operators in VBA
Operator	Description	Example	Result
=	Equal to	`5 = 5`	True
<>	Not equal to	`5 <> 3`	True
<	Less than	`3 < 5`	True
>	Greater than	`5 > 3`	True
<=	Less than or equal to	`5 <= 5`	True
>=	Greater than or equal to	`5 >= 3`	True

Important Note:

When comparing strings, VBA performs a case-insensitive comparison by default. For case-sensitive comparisons, use StrComp() function with the vbBinaryCompare option.

Practical Example: Grade Evaluation

Sub EvaluateGrade()
    Dim score As Integer
    score = InputBox("Enter your score (0-100):")
    
    If score >= 90 Then
        MsgBox "Grade: A"
    ElseIf score >= 80 Then
        MsgBox "Grade: B"
    ElseIf score >= 70 Then
        MsgBox "Grade: C"
    ElseIf score >= 60 Then
        MsgBox "Grade: D"
    Else
        MsgBox "Grade: F"
    End If
End Sub

4.3 Logical Operators

Logical operators combine multiple conditions and return Boolean results. They're powerful tools for creating complex decision structures.

Table 4.3: Logical Operators in VBA
Operator	Description	Example	Result
And	True if both conditions are true	`(5 > 3) And (2 < 4)`	True
Or	True if either condition is true	`(5 > 3) Or (2 > 4)`	True
Not	Reverses the logical result	`Not (5 > 3)`	False
Xor	True if conditions are different	`(5 > 3) Xor (2 < 4)`	False

Operator Precedence:

When combining multiple operators, VBA evaluates them in this order: Arithmetic → Comparison → Logical. Use parentheses to explicitly control evaluation order.

Practical Example: Loan Approval System

Sub CheckLoanApproval()
    Dim creditScore As Integer
    Dim annualIncome As Currency
    Dim existingLoans As Integer
    
    creditScore = 720
    annualIncome = 85000
    existingLoans = 2
    
    ' Check loan approval criteria
    If creditScore >= 700 And annualIncome > 50000 And existingLoans < 3 Then
        MsgBox "Loan Approved!"
    Else
        MsgBox "Loan Denied. Please contact our office for details."
    End If
End Sub

Key Takeaways

Arithmetic operators perform mathematical calculations and string concatenation
Comparison operators evaluate relationships between values (return True/False)
Logical operators combine multiple conditions for complex decision-making
Use & instead of + for string concatenation to avoid errors
Operator precedence determines evaluation order (arithmetic → comparison → logical)
Parentheses can override default precedence when needed

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page