43  Macro Basics for Excel Automation

43.1 Why Macros Matter

The fastest way to make a dashboard dishonestly slow is to do its monthly refresh by hand.

A weekly Excel report that takes the analyst 90 minutes to build will take the same 90 minutes every week — unless it is automated. Macros, written in Visual Basic for Applications (VBA), are Excel’s automation language. They turn the manual rituals of refreshing data, formatting reports, generating PDFs, and emailing recipients into one-click operations.

The standard practitioner references for Excel automation are Excel 2016 VBA and Macros by Bill Jelen & Tracy Syrstad (2017) and Excel 2019 Power Programming with VBA by Michael Alexander & Richard Kusleika (2018), which together cover virtually every automation pattern an analyst will need.

For a visualisation-focused book, this chapter is where the dashboards built in earlier Excel chapters (Chapters 40–42) become operational artefacts — refreshable, distributable, and resilient to human forgetfulness.

43.2 What Is a Macro

A Macro is a recorded or written piece of code that automates a sequence of Excel actions. The code is written in Visual Basic for Applications (VBA), a dialect of Visual Basic embedded in every Office application since 1993.

A workbook containing macros must be saved with the .xlsm (macro-enabled) extension. The traditional .xlsx extension does not preserve macros.

43.3 Recording Macros

Excel can record a sequence of clicks and translate them into VBA. This is the entry point most analysts take.

To record:

  1. Enable the Developer ribbon: File → Options → Customize Ribbon → tick Developer → OK.
  2. Developer → Record Macro.
  3. Give the macro a name (no spaces; descriptive: RefreshAndFormat).
  4. Optionally assign a shortcut key.
  5. Choose where to store it: This Workbook, New Workbook, or Personal Macro Workbook (a hidden workbook that loads on every Excel start).
  6. Click OK; perform the actions you want to automate (clicks, formatting, formulas).
  7. Developer → Stop Recording.

The recorded VBA appears in a new module in the VBA Editor. Recording is the fastest way to learn VBA — perform an action and read the code Excel wrote.

43.4 The VBA Editor

To open: Developer → Visual Basic or press Alt + F11. The VBA Editor (VBE) opens as a separate window with three principal panes:

  • Project Explorer (top-left) — tree of open workbooks with their objects (Workbook, Worksheets, Modules).
  • Properties Window (bottom-left) — properties of the selected object.
  • Code Window (right) — where the VBA code lives.

A VBA module belongs to one of three categories:

  • Standard module — general-purpose code; the default location for recorded macros.
  • Worksheet module — code attached to a specific worksheet (Worksheet_Change, Worksheet_SelectionChange events).
  • Workbook module — code attached to the workbook (Workbook_Open, Workbook_BeforeSave events).

The VBA Editor’s Immediate Window (Ctrl + G) is invaluable: type ?Range("A1").Value and press Enter to inspect the value of A1 immediately.

43.5 The Excel Object Model

Excel exposes its functionality through an Object Model — a hierarchy of objects each with properties and methods. Understanding this hierarchy is the gate to writing useful VBA.

TipThe Top of the Hierarchy
Object What It Represents
Application Excel itself
Workbooks Collection of open workbooks
Workbook A single open file
Worksheets Collection of sheets in a workbook
Worksheet A single sheet
Range A rectangular block of cells
Cells A range expressed as row/column indices
Charts / PivotTables / ListObjects Other contained objects

A typical reference: Application.Workbooks("Sales.xlsx").Worksheets("Data").Range("A1:D100"). Most code uses shorter forms via ActiveWorkbook, ThisWorkbook, ActiveSheet.

43.5.1 The Three Most-Used Objects

A few patterns cover the bulk of automation:

Range — the cells the macro operates on:

Range("A1").Value = "Hello"
Range("A1:D10").ClearContents
Range("A1").Font.Bold = True
Range("A:A").EntireColumn.AutoFit

Worksheet — the sheet on which Range operates:

Worksheets("Dashboard").Activate
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Summary"

Workbook — the file:

ThisWorkbook.Save
ActiveWorkbook.SaveAs "Backup.xlsx"
Workbooks.Open "C:\Data\Sales.xlsx"

43.6 Variables, Control Flow, Loops

VBA syntax is verbose but readable:

Variables (declared with Dim):

Dim totalSales As Double
Dim region As String
Dim lastRow As Long

Control flow (If ... Then ... Else):

If totalSales > 100000 Then
    region = "High"
ElseIf totalSales > 50000 Then
    region = "Medium"
Else
    region = "Low"
End If

For loop:

Dim i As Long
For i = 1 To 100
    Cells(i, 1).Value = i * 2
Next i

For Each loop (over collections):

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

Do While loop:

Dim r As Long
r = 1
Do While Cells(r, 1).Value <> ""
    Cells(r, 2).Value = Cells(r, 1).Value * 2
    r = r + 1
Loop

The combination of these constructs covers the vast majority of macro-writing.

43.7 Common Automation Patterns

A handful of patterns recur in nearly every analyst’s macro library:

  • Refresh All Connections:

    ThisWorkbook.RefreshAll

  • Refresh a Specific Power Query:

    ThisWorkbook.Connections("Query - SalesData").Refresh

  • Save as PDF:

    Worksheets("Dashboard").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\Dashboard.pdf"

  • Email via Outlook:

    Dim outlook As Object, mail As Object
Set outlook = CreateObject("Outlook.Application")
Set mail = outlook.CreateItem(0)
With mail
    .To = "leadership@yuvijen.com"
    .Subject = "Daily Sales Snapshot"
    .Body = "Attached is today's sales dashboard."
    .Attachments.Add ThisWorkbook.Path & "\Dashboard.pdf"
    .Send
End With

  • Loop Over Files in a Folder:

    Dim file As String
file = Dir("C:\Reports\*.xlsx")
Do While file <> ""
    Workbooks.Open "C:\Reports\" & file
    ' do something
    ActiveWorkbook.Close SaveChanges:=False
    file = Dir
Loop

  • Find Last Used Row:

    Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

  • Refresh All Pivot Tables:

    Dim pt As PivotTable, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
Next ws

43.8 Macro Security

Macros run code; code can be malicious. Excel’s Macro Security settings (File → Options → Trust Center → Trust Center Settings → Macro Settings) control what runs:

  • Disable all macros without notification — strictest; macros never run.
  • Disable all macros with notification — default; user is prompted on each open.
  • Disable all macros except digitally signed macros — only signed macros from trusted publishers run.
  • Enable VBA macros (not recommended) — allows all macros; the most permissive setting.

For institutional deployments, digital signing (Microsoft documentation: Digital signatures and code signing in workbooks) lets the IT team sign macros so they run on every employee’s machine without prompting. Combined with Trusted Locations (designated folders where macros run automatically), the friction of macro-enabled workbooks is largely removed.

A practical rule: never enable macros from unknown senders, and never run a macro you have not read.

43.9 Modern Alternatives: Office Scripts and Power Automate

VBA is approaching its third decade. Microsoft has introduced two modern alternatives:

  • Office Scripts (TypeScript-based, Excel for the Web): A modern scripting language with a similar object model. Office Scripts run in the cloud, integrate with Power Automate, and do not require local installation. The language is JavaScript-derivative and easier to share across platforms.

  • Power Automate (low-code workflow): For workflows that span multiple applications (Excel + Outlook + SharePoint + Teams), Power Automate replaces VBA with a visual flow designer. The flow can read from Excel, transform data, send emails, post to Teams, and write back to Excel.

VBA remains the right answer when:

  • The automation runs entirely inside one Excel workbook.
  • The Excel installation is desktop-only and offline-capable.
  • The audience already has macro-enabled workflows.

Office Scripts and Power Automate are the right answer when:

  • The workflow spans multiple Microsoft 365 services.
  • The team works on Excel for the Web or expects cloud-native execution.
  • The automation should run on a schedule without anyone opening the workbook.

43.10 Best Practices for Macro Writing

  • Start with the recorder, then refine: Recorded code is verbose and brittle; clean it up to use named ranges, tables, and proper variable types.
  • Use Option Explicit: At the top of every module; forces every variable to be declared with Dim, catching typos at compile time.
  • Avoid Select and Activate: Recorded code uses these heavily; refactored code references ranges and worksheets directly for speed and clarity.
  • Turn off ScreenUpdating for long macros: Application.ScreenUpdating = False at the start, True at the end; the macro runs five to ten times faster.
  • Handle errors with On Error: On Error GoTo ErrHandler jumps to a labelled error handler; On Error Resume Next skips errors silently (use sparingly).
  • Use named ranges and tables: Range("Sales[Amount]") is clearer than Range("A2:A1000") and adapts to data growth.
  • Comment generously: VBA code is rarely revisited until it breaks; the next analyst (or your future self) will thank you.
  • Modularise: Break long procedures into smaller subs; each sub does one thing.
  • Source-control via export: Right-click a module → Export File; commit the .bas file alongside the .xlsm. This is the only practical way to track macro changes over time.
  • Test on representative data: Macros that work on three test rows often fail on three thousand real ones (off-by-one errors, locale issues, hidden rows).

43.11 Common Pitfalls

  • Recorder Output as Production Code: Brittle, slow, full of .Select and absolute cell references; re-write before relying on.
  • No Option Explicit: Typos in variable names create silent new variables; debugging becomes painful.
  • Hard-Coded Paths: C:\Users\Anita\file.xlsx works on Anita’s laptop only.
  • Hard-Coded Cell References: Range("A2:A1000") breaks when the data grows past 1000 rows; use Tables.
  • No Error Handling: An unhandled error stops the macro mid-flight; users see the VBA debugger and lose confidence.
  • ScreenUpdating Left Off: Macro completes but Excel feels frozen because screen updating was never re-enabled.
  • Macro Security Disabled Globally: Convenient for the analyst but exposes every other workbook to risk.
  • Saving as .xlsx and Losing Macros: A macro-bearing workbook saved as .xlsx silently drops every macro.
  • Too Much in One Macro: A single 500-line procedure that does five different things; impossible to maintain.
  • VBA Where Power Query Would Suffice: Hand-rolling data import in VBA when Power Query (Chapter 35) is faster, more reliable, and refreshable.
  • No Version Control: Years of patched workbooks with no audit trail of who changed what when.

43.12 Illustrative Cases

A 90-Minute Weekly Report Reduced to a Click

A finance team’s weekly variance report takes 90 minutes to build manually — refresh data, format the table, build the PDF, email leadership. A 100-line VBA macro does all four steps in under 30 seconds. The team now devotes the recovered time to actual analysis.

A Folder Loop That Consolidates Branch Reports

A retail head office receives 50 branch sales files each week. A macro loops over the folder, opens each file, extracts the relevant range, and stacks the results into a master workbook. The same job, previously done by an intern, runs unattended overnight.

A Migration from VBA to Office Scripts

A team’s Excel-based weekly report runs as a VBA macro. As the firm shifts to Excel for the Web, the macro stops working. The team rewrites the same logic in Office Scripts; the new script runs both on the desktop and in the browser, schedules via Power Automate, and emails the result to leadership. VBA was the right tool for the desktop era; Office Scripts is the right tool for the cloud era.

43.13 Hands-On Exercise: Building an Excel Refresh-and-Email Macro

Aim: Build a VBA macro for Yuvijen Stores that refreshes a Power Query connection, refreshes all PivotTables, exports a dashboard sheet to PDF, and emails the PDF via Outlook — turning a 30-minute manual ritual into a one-click operation.

Deliverable: A macro-enabled Excel workbook (yuvijen-refresh-email.xlsm) with one production-ready VBA module and a button on the Dashboard sheet that runs the macro.

43.13.1 Step 1 — Enable Developer and Open the VBA Editor

  1. Enable the Developer ribbon: File → Options → Customize Ribbon → tick Developer → OK.
  2. Developer → Visual Basic (or press Alt + F11) to open the VBE.

43.13.2 Step 2 — Insert a Standard Module

  1. In the VBE Project Explorer, right-click VBAProject (yuvijen-refresh-email.xlsm) → Insert → Module.
  2. Rename the module mainAutomation (in the Properties window).
  3. At the top of the module, type Option Explicit to enforce variable declaration.

43.13.3 Step 3 — Write the Refresh-and-Email Procedure

Option Explicit

Public Sub RefreshAndEmail()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' Step 1: Refresh all data connections (Power Query + others)
    ThisWorkbook.RefreshAll
    DoEvents  ' allow refresh to complete

    ' Step 2: Refresh all pivot tables (in case any were not auto-refreshed)
    Dim ws As Worksheet, pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' Step 3: Export Dashboard sheet to PDF
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "\Yuvijen-Dashboard-" & _
              Format(Now, "yyyy-mm-dd") & ".pdf"
    Worksheets("Dashboard").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=pdfPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    ' Step 4: Email via Outlook
    Dim outlook As Object, mail As Object
    Set outlook = CreateObject("Outlook.Application")
    Set mail = outlook.CreateItem(0)
    With mail
        .To = "leadership@yuvijen.com"
        .Subject = "Yuvijen Daily Sales Snapshot - " & Format(Now, "dd-mmm-yyyy")
        .Body = "Attached is today's refreshed dashboard." & vbCrLf & _
                "Refresh time: " & Format(Now, "yyyy-mm-dd hh:nn") & vbCrLf & _
                "- Automated via VBA"
        .Attachments.Add pdfPath
        .Send
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Refresh and email completed.", vbInformation
    Exit Sub

ErrHandler:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

43.13.4 Step 4 — Save as Macro-Enabled

  1. File → Save As.
  2. Choose Excel Macro-Enabled Workbook (*.xlsm) as the file type.
  3. Name: yuvijen-refresh-email.xlsm.
  4. Save.

The .xlsm extension preserves the VBA. Saving as .xlsx would silently drop it.

43.13.5 Step 5 — Add a Button to the Dashboard Sheet

  1. On the Dashboard sheet, Developer → Insert → Form Controls → Button.
  2. Draw the button on the sheet.
  3. The Assign Macro dialog opens; select RefreshAndEmail.
  4. OK.
  5. Right-click the button → Edit Text → label it “Refresh & Email”.
  6. Format with a clear colour and an explicit caption.

The dashboard now has a single button that runs the entire automation.

43.13.6 Step 6 — Test the Macro

  1. Click the button.
  2. Watch the status bar — Excel runs the refresh, regenerates pivots, exports the PDF, and triggers Outlook.
  3. Verify the PDF appears in the workbook’s folder and the email arrives.

If anything fails, the error handler shows the error number and description; debug from there.

43.13.7 Step 7 — Add Macro Security and Trusted Location

For the team to run the macro without security prompts each time:

  1. Save the workbook in a designated folder (e.g., \\fileserver\YuvijenReports).
  2. File → Options → Trust Center → Trust Center Settings → Trusted Locations → Add new location.
  3. Add the folder path; tick Subfolders are also trusted.

For broader rollout (multiple machines), the IT team can sign the macro with a code-signing certificate and distribute via Group Policy.

43.13.8 Step 8 — Schedule via Windows Task Scheduler (Optional)

To run the macro on a schedule without anyone opening Excel:

  1. Save a small RunMacro.vbs file with code to open the workbook and call the macro:

    Dim xl
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "C:\Reports\yuvijen-refresh-email.xlsm"
xl.Run "RefreshAndEmail"
xl.Quit

  2. In Task Scheduler, create a daily 7:00 AM trigger that runs RunMacro.vbs.

The dashboard now refreshes and emails leadership automatically each weekday morning.

43.13.9 Step 9 — Connect to the Visualisation Layer

The hands-on illustrates the closing of the visualisation loop:

  • The dashboards from Chapters 40–42 (Power Pivot model, advanced charts, what-if analysis) are now operational artefacts.
  • A single button drives refresh, regeneration, PDF export, and email distribution.
  • Combined with macro security and Task Scheduler, the workbook becomes a self-running reporting system without Power BI Service licences.

For institutional Excel-based reporting, this VBA-driven pattern is the lowest-friction path to a fully automated dashboard. As organisations migrate to cloud-native platforms, the same logic translates to Office Scripts and Power Automate.

TipFiles and Screen Recordings

Macro-enabled Excel workbook (yuvijen-refresh-email.xlsm), the VBA module exported as mainAutomation.bas, and screen recordings of the macro running and the email arriving will be embedded here.

Summary

Concept Description
Foundations
Why Macros Matter Manual rituals consume analyst time; macros turn 90-minute weekly tasks into one-click operations
Macro Recorded or written code that automates a sequence of Excel actions
VBA Visual Basic for Applications; the dialect of Visual Basic embedded in Excel since 1993
xlsm Extension Macro-enabled workbook extension; xlsx silently drops macros
Recording Macros
Recording a Macro Developer Record Macro; performs actions and translates them into VBA
Personal Macro Workbook Hidden workbook that loads on every Excel start; for personal-library macros
The VBA Editor
VBA Editor (VBE) Alt+F11 opens the VBA Editor with three principal panes
Project Explorer Tree of open workbooks with their objects
Properties Window Properties of the selected object
Code Window Where the VBA code lives
Immediate Window Ctrl+G; type expressions and inspect values immediately
Standard Module General-purpose code; default location for recorded macros
Worksheet Module Code attached to a specific worksheet via Worksheet_Change events
Workbook Module Code attached to the workbook via Workbook_Open and similar events
The Excel Object Model
Excel Object Model Hierarchy of objects each with properties and methods
Application Object Excel itself
Workbook Object A single open file
Worksheet Object A single sheet within a workbook
Range Object Rectangular block of cells
Cells Object Range expressed as row and column indices
ActiveWorkbook and ThisWorkbook Shorter forms for the active and current workbook
Range Property Setting Set Range value, font, alignment, formula via property assignment
Worksheet Activation Worksheets Activate brings the sheet to front; Add inserts a new one
Workbook Save and Open ThisWorkbook.Save, ActiveWorkbook.SaveAs, Workbooks.Open
VBA Language
Dim Variable Declaration Variables declared with Dim and a type
If Then Else Branching with If Then ElseIf Else End If
For Loop For i = 1 To 100 ... Next i
For Each Loop For Each item In collection ... Next item
Do While Loop Do While condition ... Loop for unbounded iteration
Common Automation Patterns
RefreshAll Pattern ThisWorkbook.RefreshAll triggers all data connections
Specific Connection Refresh ThisWorkbook.Connections(name).Refresh for one connection
Save as PDF Pattern ExportAsFixedFormat with Type:=xlTypePDF
Outlook Email Pattern CreateObject Outlook.Application then CreateItem(0) for an email
Folder Loop Pattern Dir() function returns one filename per call
Find Last Row Cells(Rows.Count, col).End(xlUp).Row finds the last used row
Refresh All Pivots Loop over Worksheets and PivotTables and call RefreshTable
Macro Security
Macro Security Settings Trust Center Macro Settings control what runs
Digital Signing Code-signing certificate lets macros run on every employee machine without prompting
Trusted Locations Designated folders where macros run automatically without prompts
Modern Alternatives
Office Scripts TypeScript-based modern alternative for Excel for the Web
Power Automate Low-code workflow that spans multiple Microsoft 365 services
VBA versus Modern Alternatives VBA for desktop only inside one workbook; Modern alternatives for cloud and cross-app
Best Practices
Start with Recorder Then Refine Recorded code is verbose and brittle; clean it up with named ranges and proper types
Use Option Explicit Forces every variable to be declared with Dim, catching typos at compile time
Avoid Select and Activate Recorded code uses Select heavily; refactored code references ranges directly
Turn off ScreenUpdating Application.ScreenUpdating False at start, True at end; macro runs much faster
On Error Handling On Error GoTo handler jumps to labelled error handler when something fails
Use Named Ranges and Tables Range(SalesAmount) is clearer than Range(A2:A1000) and adapts to data growth
Comment Generously VBA code rarely revisited until it breaks; future maintainers benefit from comments
Modularise into Subs Break long procedures into smaller subs each doing one thing
Source-Control via Export Right-click module Export File and commit the .bas file alongside the .xlsm
Test on Representative Data Macros that work on three test rows often fail on three thousand real ones
Common Pitfalls
Recorder Output as Production Pitfall of using verbose recorder output without refactoring
No Option Explicit Pitfall of typos creating silent new variables and painful debugging
Hard-Coded Paths Pitfall of paths that work only on the original developer's machine
Hard-Coded Cell References Pitfall of cell references that break when data grows past the hard-coded limit
No Error Handling Pitfall of unhandled errors stopping the macro mid-flight and showing the debugger
ScreenUpdating Left Off Pitfall of macro completing but Excel feeling frozen because screen updating was off
Global Macro Security Disabled Pitfall of disabling macro security globally to avoid prompts; risk to all workbooks
Saving as xlsx and Losing Macros Pitfall of saving a macro-bearing workbook as xlsx and silently losing every macro
Too Much in One Macro Pitfall of a single 500-line procedure doing five different things; unmaintainable
VBA Where Power Query Suffices Pitfall of hand-rolling data import in VBA when Power Query is faster and refreshable
No Version Control Pitfall of years of patched workbooks with no audit trail