Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

VBA Performance Optimization

Learn practical techniques to make Excel VBA macros faster, process large datasets efficiently, and manage Excel application settings safely.


1. Introduction to Performance Optimization

Performance optimization is the process of reducing unnecessary work in a VBA procedure while preserving the accuracy of its output. Macros that process thousands of rows can become slow when they repeatedly access cells, select worksheets, or trigger recalculation.

Reduce Operations

Avoid unnecessary selections and repeated calculations.

Process in Bulk

Read and write suitable ranges in fewer operations.

Measure Results

Compare execution time before and after changes.

Key principle: Optimize the operations that consume the most time. Always verify that the optimized macro produces the same correct results.

2. Performance Optimization Workflow

A reliable optimization process starts with measurement, identifies unnecessary work, applies an improvement, and tests the result.

1. Measure Execution Time 2. Identify Bottlenecks 3. Optimize the Code 4. Test and Compare Repeat if the result is not satisfactory.

3. Avoid Select and Activate

Selecting a worksheet or cell is usually unnecessary when VBA can reference the object directly. Direct references can make code simpler and reduce unnecessary interface interactions.

Less Efficient
Sub WriteDataSlow()

    Worksheets("Sheet1").Activate
    Range("A1").Select
    Selection.Value = "Employee"

End Sub
Improved
Sub WriteDataDirect()

    ThisWorkbook.Worksheets("Sheet1") _
        .Range("A1").Value = "Employee"

End Sub

Why it helps: The second procedure writes directly to the intended cell and does not depend on which worksheet is currently active.

4. Disable Screen Updating When Appropriate

Excel may refresh the visible interface during worksheet changes. For macros that perform many updates, temporarily disabling screen updating can reduce unnecessary visual refreshes.

Sub ScreenUpdatingOptimization()

    Dim previousScreenUpdating As Boolean
    Dim ws As Worksheet

    previousScreenUpdating = Application.ScreenUpdating

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Range("A1").Value = "Employee ID"
    ws.Range("B1").Value = "Employee Name"
    ws.Range("C1").Value = "Department"

CleanExit:
    Application.ScreenUpdating = previousScreenUpdating
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit

End Sub
Important: Always restore the previous setting, including when an error occurs. Do not assume screen updating was originally enabled.

5. Optimize Calculation Settings

Workbooks with many formulas may recalculate frequently when cell values change. For suitable bulk operations, temporarily switching to manual calculation can reduce repeated recalculation.

Sub CalculationOptimization()

    Dim previousCalculation As XlCalculation
    Dim ws As Worksheet

    previousCalculation = Application.Calculation

    On Error GoTo ErrorHandler

    Application.Calculation = xlCalculationManual

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Range("A1").Value = 100
    ws.Range("A2").Value = 200
    ws.Range("A3").Formula = "=SUM(A1:A2)"

    ' Calculate explicitly when the result is required.
    ws.Calculate

CleanExit:
    Application.Calculation = previousCalculation
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit

End Sub

Explanation: The macro saves the original calculation mode, switches to manual calculation, performs its operations, calculates the required worksheet, and restores the original mode.

Choose the appropriate calculation scope for your workbook. Some formulas depend on other worksheets, external data, or volatile functions. Verify that all required results are current.

6. Use Arrays for Large Datasets

Reading and writing individual cells repeatedly can become slow for large datasets. Arrays allow VBA to read a range into memory, process the values, and write the results back in bulk.

Excel Range Read once VBA Array Process values in memory Calculate results Excel Range Write once Save results Fewer worksheet interactions for suitable bulk operations
Example: Increase Salary by 10%

Assume salary values are stored in column B from row 2 onward in a worksheet named Sheet1.

Sub IncreaseSalaryUsingArray()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim salaryData As Variant
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    salaryData = ws.Range("B2:B" & lastRow).Value2

    For i = 1 To UBound(salaryData, 1)

        If Not IsError(salaryData(i, 1)) Then
            If IsNumeric(salaryData(i, 1)) _
                And Len(CStr(salaryData(i, 1))) > 0 Then

                salaryData(i, 1) = CDbl(salaryData(i, 1)) * 1.1

            End If
        End If

    Next i

    ws.Range("B2:B" & lastRow).Value2 = salaryData

    MsgBox "Salary update completed.", vbInformation

End Sub
Test carefully: This procedure changes the original salary values. Run it on a copy of the workbook, and verify that column B contains salaries before executing it.

7. Use the With Statement

A With block groups multiple operations on the same object. This improves readability and avoids repeatedly writing the same object reference.

Sub FormatReportHeader()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws.Range("A1:C1")
        .Font.Bold = True
        .Font.Size = 12
        .HorizontalAlignment = xlCenter
        .Interior.Color = RGB(217, 225, 242)
    End With

End Sub

Explanation: Every property inside the block applies to the range A1:C1.

8. Find the Last Used Row Efficiently

Avoid scanning an entire worksheet row by row when you only need to locate the end of a continuous data column. The End(xlUp) pattern is commonly used for this purpose.

Sub FindLastRow()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow = 1 And Len(CStr(ws.Range("A1").Value)) = 0 Then
        MsgBox "Column A is empty."
    Else
        MsgBox "Last used row in column A: " & lastRow
    End If

End Sub

This pattern assumes that column A is the column used to identify the data's last row. Choose a column that is populated for every record you want to count.

9. Measure Execution Time

Measuring execution time helps you verify whether a change actually improves performance. Use the same input data and workbook conditions for a fair comparison.

Sub MeasureExecutionTime()

    Dim startTime As Double
    Dim elapsedTime As Double
    Dim i As Long
    Dim total As Double

    startTime = Timer

    For i = 1 To 1000000
        total = total + i
    Next i

    elapsedTime = Timer - startTime

    MsgBox "Total: " & Format$(total, "#,##0") & vbCrLf & _
           "Execution time: " & Format$(elapsedTime, "0.000") & _
           " seconds", vbInformation

End Sub
Note: Timer measures seconds since midnight. If a measured procedure can cross midnight, handle the day boundary when calculating elapsed time.

10. Compare Optimization Techniques

Technique When It Helps Important Consideration
Direct object references When code unnecessarily selects cells or worksheets. Reference the intended workbook and worksheet explicitly.
Screen updating off Macros that perform many visible changes. Restore the previous setting.
Manual calculation Large formula-heavy workbooks with many updates. Recalculate required formulas before using results.
Arrays Large blocks of data that can be processed in memory. Validate data types and preserve required formatting.
With statement Multiple operations on the same object. Improves code clarity; speed gains may be small.
Execution-time measurement Comparing two implementations. Use equivalent test conditions.

11. Practice Exercises

Exercise 1: Direct References

Rewrite a macro that uses Select and Activate so it writes directly to the required cells.

Exercise 2: Bulk Data Processing

Read 5,000 numeric salary values into an array, calculate a 10% increment, and write the results to a separate column.

Exercise 3: Calculation Settings

Create a macro that stores the current calculation mode, performs multiple cell updates, and restores the original mode.

Exercise 4: Performance Comparison

Compare cell-by-cell processing with array-based processing using the same sample dataset.

Important Points to Remember

  • Measure performance before and after optimization.
  • Avoid unnecessary Select and Activate operations.
  • Use arrays for suitable large-scale data processing.
  • Restore Excel application settings after temporary changes.
  • Ensure formula results are current when required.
  • Use explicit worksheet references to avoid active-sheet mistakes.
  • Test optimized code with realistic data and edge cases.

Summary

Performance optimization improves the efficiency of Excel VBA macros by reducing unnecessary operations, processing suitable datasets in arrays, managing screen updating and calculation settings, and measuring execution time.

Always preserve correctness, restore application settings, and test changes before using optimized macros in important workbooks.