Skip to content

Excel VBA – If Then Else Structure (7 Example Macros)

    Structure

    The structure of a If Then Else statement is described below.

    If Condition Then (required, repeat as required)
	Set of Instructions
ElseIf Condition Then (optional/ repeat as required)
	Set of Instructions
Else (optional)
	Set of instructions
End If

    In English this might read.

    If Weather Is Rainy Then
	Take Umbrella
Else If Weather is Snowy Then
	Take Snow Boots
Else
	Take Sunglasses
End If

    The If condition must evaluate to TRUE or FALSE.

    The ElseIf and Else statements are optional.  So your IF statement might read like this… Notice that when you place the instructions on the same line as the condition you don’t need to use End If.

    If Weather is Rainy Then Take Umbrella
If Weather is Snowy Then Take Snow Boots
If Weather is Sunny Then Take Sunglasses

    Sales Commission Example

    This procedure tests to see if each sales person has met the sales target of £65,000.  The code applies formatting based on the test, a value in the ‘Job Prospects’ field and calculates the commission due to the sales person.

    Sub CommissionCalculation()
Const CommisionPercentage As Single = 0.03
Const SalesTarget As Long = 65000
Dim SalesCell As Range, SalesField As Range, JobProspectsCell As Range, CommissionCell As Range
Set SalesField = Range("B2", Range("B2").End(xlDown))

For Each SalesCell In SalesField
Set JobProspectsCell = SalesCell.Offset(0, 1)
Set CommissionCell = SalesCell.Offset(0, 2)

    If SalesCell >= SalesTarget Then
        JobProspectsCell = "Good"
        SalesCell.Interior.Color = vbGreen
        CommissionCell = (SalesCell - SalesTarget) * CommisionPercentage
        CommissionCell.NumberFormat = "£#,##0"
    Else
        SalesCell.Interior.Color = vbRed
        JobProspectsCell = "Not so good"
        CommissionCell = 0
    End If

Next SalesCell

End Sub

    The code above relates to the data shown below.

    Budget Status Example

    This procedure checks to see whether each budget account has overspent. If it has, ’OVER BUDGET’ is displayed in the STATUS column.  Because the intruction for the If are on the same line as the condition, End If is not required.

    Sub BudgetStatus()

Dim BudgetCell As Range, BudgetField As Range, ActualCell As Range, StatusCell As Range
Set BudgetField = Range("C2", Range("C2").End(xlDown))

For Each BudgetCell In BudgetField
    Set ActualCell = BudgetCell.Offset(0, 1)
    Set StatusCell = BudgetCell.Offset(0, 2)
    
    If BudgetCell - ActualCell < 0 Then StatusCell = "OVER BUDGET"
    
Next BudgetCell

End Sub

    Mortgage Application Example Using AND Logical Operator

    This procedure tests to see whether a mortgage application has been approved.  The applicant must have a minimum credit score of 8 but must also meet the affordability criteria.  The affordability criteria is calculated as below.

    (Income – Expenditure) * 5 * 12 must be >= Loan Requested

    In this example Else and the Else instruction are on the same line.  This is possible if you separate the instruction from the condition with a colon :.

    As there are two conditions in this If statement, both of which must be met, we can use the AND logical operator. The OR logical operator can be used to return TRUE if any condition is met.

    Sub MortgageApplications()
Const BudgetMultiple As Byte = 60
Const MinCreditScore As Byte = 8
Dim LoanRequestCell As Range, LoanRequestField As Range, _
MonthlyIncomeCell As Range, MonthlyExpensesCell As Range, MortgageAgreedCell As Range, _
CreditScoreCell As Range
Dim MaxLoan As Single
Set LoanRequestField = Range("E3", Range("E3").End(xlDown))

For Each LoanRequestCell In LoanRequestField
Set MonthlyIncomeCell = LoanRequestCell.Offset(0, -2)
Set MonthlyExpensesCell = LoanRequestCell.Offset(0, -1)
Set MortgageAgreedCell = LoanRequestCell.Offset(0, 1)
Set CreditScoreCell = LoanRequestCell.Offset(0, -3)
MaxLoan = (MonthlyIncomeCell - MonthlyExpensesCell) * BudgetMultiple
    
    If MaxLoan >= LoanRequestCell And CreditScoreCell >= MinCreditScore Then
        MortgageAgreedCell = "Yes"
    Else: MortgageAgreedCell = "No"
    End If
    
Next LoanRequestCell

End Sub

    The code above relates to the data shown below.

    Restock Example Using AND Logical Operator

    This procedure works out whether you need to restock on products.  Different product categories have different restock thresholds, so there are always two criteria for each product: it’s category and its current stock level.  When there is more than one criteria we can use the AND or OR logical operators.

    Sub RestockQuestion()
Dim StockCell As Range, StockField As Range, RestockCell As Range, CategoryCell As Range
Set StockField = Range("B2", Range("B2").End(xlDown))

For Each StockCell In StockField
    Set CategoryCell = StockCell.Offset(0, -1)
    Set RestockCell = StockCell.Offset(0, 1)
    
    If CategoryCell = "A" And StockCell < 40 Then RestockCell = "Yes"
    If CategoryCell = "B" And StockCell < 80 Then RestockCell = "Yes"
    If CategoryCell = "C" And StockCell < 10 Then RestockCell = "Yes"

Next StockCell
    
End Sub

    The code above relates to the data shown below.

    Average Sales Example Using ElseIf

    This procedure calculates average sales achieved between a start and end date.  The calculation is only performed if an end date has been entered, otherwise a message is displayed.

    Sub AverageSalesCalculation()

Dim SalesCell As Range, SalesField As Range, _
AverageSalesCell As Range, StartCell As Range, EndCell As Range
Dim AverageCellsCalc As Currency
Set SalesField = Range("D2", Range("D2").End(xlDown))

For Each SalesCell In SalesField
    Set StartCell = SalesCell.Offset(0, -2)
    Set EndCell = SalesCell.Offset(0, -1)
    Set AverageSalesCell = SalesCell.Offset(0, 1)
    On Error Resume Next
    AverageCellsCalc = SalesCell / (EndCell - StartCell)

        If IsDate(EndCell) Then
            AverageSalesCell = AverageCellsCalc
        ElseIf IsEmpty(EndCell) Then
            AverageSalesCell = "Awaiting end date"
        ElseIf WorksheetFunction.IsText(EndCell) Then
            AverageSalesCell = "End date not entered correctly"
        End If
        
    Next SalesCell

End Sub

    The code above relates to the data shown below.

    Automatically Save Workbook When Cell Values Change Example

    This procedure automatically saves the workbook if any cell in the specified range changes.  The If’s condition uses the Intersect method. Intersect, as it name suggests returns the intersecting range of cells that result when two or more ranges overlap.  If no cells overlap intersect will return nothing.

    Intersect(FirstRange, SecondRange etc)

    The procedure is automatically run with the Worksheet Change event. Target is the range that has been changed. The Target range is automatically returned by the Worksheet Change event.

    Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:D10")) Is Nothing Then
    Exit Sub
Else: ThisWorkbook.Save
End If

End Sub

    This version will save the workbook if any cell is changed.

    Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, ActiveSheet.Cells) Is Nothing Then
    Exit Sub
Else: ThisWorkbook.Save
End If

End Sub

    Message Box Example

    This procedure increases salary amounts by 5%, but warns the user with a message box that the results can’t be undone. If the user clicks on the message box’s Cancel button the procedure ends before the salary changes are made.

    Sub MessageBoxExample()

Dim SalaryCell As Range, SalaryField As Range
Dim UserAnswer As Integer
Set SalaryField = Range("B2", Range("B2").End(xlDown))
 
UserAnswer = MsgBox("This will change the current salaries and you will not be able to undo." _
& vbNewLine & "Do you want to continue?", vbOKCancel)
If UserAnswer = vbCancel Then Exit Sub

For Each SalaryCell In SalaryField
    SalaryCell = SalaryCell * 1.05
Next SalaryCell

End Sub