Blog

If you want Excel to “just do it” the moment a file opens, a cell changes, or a clock ticks over—this tutorial is your end-to-end blueprint. We’ll operationalize Auto Run VBA Code using event procedures (e.g., Workbook_Open, Worksheet_Change), scheduled jobs with Application.OnTime, and a few pro-level patterns (application-level events, Task Scheduler hand-off). You’ll get structured steps, real code, gotchas, and SEO deliverables—all the enterprise vibes, minus the enterprise chaos.

Who is this for?

• Beginners who know basic Excel and want practical, repeatable automation.
• Analysts who need hands-free refreshes, checks, alerts, and exports.
• Teams aiming to standardize reporting workflows with guardrails.

Prerequisites (keep it lean, keep it safe)

1. Excel Version: Microsoft Excel 2016 or Microsoft 365 (Windows recommended for Task Scheduler and COM automation).
2. File Format: Save as .xlsm (macro-enabled).
3. Developer Tab: Enabled (File → Options → Customize Ribbon → check Developer).
4. Macro Security:
   • File → Options → Trust Center → Trust Center Settings → Macro Settings.
   • Best practice: Disable VBA macros with notification + use Trusted Locations for your own files.
5. Basic VBA: How to open the VBA editor (Alt+F11), insert modules, find ThisWorkbook and worksheet code-behind.
6. Optional: Access to Windows Task Scheduler if you plan unattended daily runs.

Sample Dataset (you’ll use this end-to-end) Auto Run VBA Code

We’ll build a table tblSales on a worksheet named Sales.

Columns (8):

• OrderID (Text)
• OrderDate (Date)
• Region (Text; e.g., North, South, West)
• Salesperson (Text)
• Product (Text)
• Units (Number)
• UnitPrice (Currency)
• Revenue (Currency; formula)

Rows: 100–2,000 (start with 20 to test). Format as an Excel Table (Ctrl+T) and name it tblSales (Table Design → Table Name).

Core Excel formulas you’ll add to help demo automation:

1. Row revenue (in Revenue column):
   =[@Units]*[@UnitPrice]
2. High-value flag (new helper column HighValueFlag):
   =IF([@Revenue]>=5000,"High","Normal")
3. Region total (somewhere on a summary sheet, cell by region in H2):
   =SUMIFS(tblSales[Revenue],tblSales[Region],H2)
4. Today’s orders (helper measure cell):
   =SUMIFS(tblSales[Units],tblSales[OrderDate],TODAY())
5. Dynamic filter (spill):
   =FILTER(tblSales[Product],tblSales[Region]=H2)
6. Sanity check count:
   =COUNTA(tblSales[OrderID])

These formulas aren’t the star; they’re here to demonstrate that your auto run VBA can refresh, stamp, validate, and export reliably.

What “Auto Run” means in VBA (clarity first) – Auto Run VBA Code

• Event-driven: Code fires automatically in response to events—opening a workbook, changing a cell, recalculation, before saving, etc.
• Scheduled: Code fires at a time you schedule via Application.OnTime (Excel must be open), or via Windows Task Scheduler (can open Excel, run macro, close).
• Application-level: Listen across all sheets/workbooks using a class module to capture global Excel events.

Step-by-Step Walkthrough (numbered, with callouts)

1) Create and prep the workbook

1. Create Sales sheet, add header row, turn it into tblSales.
2. Add formulas above.
3. Save as AutoRunDemo.xlsm.

Callout: Always test with a copy; event procedures are powerful and can cascade quickly.

2) Your first auto-run: Workbook_Open (fires when the file opens)

Where to put it: ThisWorkbook module.

' ThisWorkbook module
Private Sub Workbook_Open()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' 1) Stamp a "last opened" note
    With ThisWorkbook.Sheets("Sales")
        .Range("J1").Value = "Last Opened:"
        .Range("K1").Value = Now
        .Range("J2").Value = "Row Count:"
        .Range("K2").Value = Application.WorksheetFunction.CountA(.ListObjects("tblSales").ListColumns("OrderID").DataBodyRange)
    End With

    ' 2) Refresh all connections (Power Query/Pivots if any)
    ThisWorkbook.RefreshAll

    ' Optional: wait briefly for asynchronous refreshes (basic pattern)
    DoEvents
    Application.Calculate

    ' 3) Kick off a daily schedule (we’ll define AutoDaily later)
    Call ScheduleDaily

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    ' Always fail safe: re-enable events/screen
    Resume CleanExit
End Sub

Why it’s good: It’s predictable and runs every time the workbook opens—perfect for initial hygiene tasks: stamping, light validation, starting timers.

3) Auto-run on user edits: Worksheet_Change (fires when cell(s) change)

Scenario: When Status changes to Closed, stamp ClosedDate.
Where: Code-behind for the Sales sheet (right-click the sheet tab → View Code).

Assumptions: You added two extra columns to tblSales:

• Status (values like Open/Closed)
• ClosedDate (Date)

' Sales worksheet module
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lo As ListObject
    Dim statusCol As Range, closedCol As Range
    Dim rngHit As Range

    On Error GoTo SafeExit
    Set lo = Me.ListObjects("tblSales")
    Set statusCol = lo.ListColumns("Status").DataBodyRange
    Set closedCol = lo.ListColumns("ClosedDate").DataBodyRange

    ' Fire only when Status is edited
    Set rngHit = Intersect(Target, statusCol)
    If rngHit Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    If LCase$(Target.Value2) = "closed" Then
        closedCol.Cells(Target.Row - statusCol.Row + 1, 1).Value = Date
    Else
        closedCol.Cells(Target.Row - statusCol.Row + 1, 1).ClearContents
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

Callout: The EnableEvents on/off pattern prevents recursive event loops. It’s a must-have in change events.

4) Auto-run on a schedule while Excel is open: Application.OnTime

Goal: Run AutoDaily every day at 08:05 AM.

' Standard module (e.g., Module1)
Public nextRun As Date

Sub ScheduleDaily()
    nextRun = Date + TimeSerial(8, 5, 0)
    If nextRun <= Now Then nextRun = DateAdd("d", 1, Date + TimeSerial(8, 5, 0))
    Application.OnTime nextRun, "AutoDaily"
End Sub

Sub CancelSchedule()
    On Error Resume Next
    Application.OnTime nextRun, "AutoDaily", , False
End Sub

Sub AutoDaily()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Example: lightweight daily steps
    ThisWorkbook.RefreshAll
    DoEvents
    Application.Calculate

    ' Export a PDF summary (assumes a Summary sheet)
    With ThisWorkbook.Sheets("Sales")
        .Range("A1").CurrentRegion.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "\DailySales.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End With

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ' Re-schedule for tomorrow
    Call ScheduleDaily
    Exit Sub

CleanFail:
    Resume CleanExit
End Sub

Callout: OnTime needs Excel to be open. That’s why we also cover Task Scheduler later for true unattended runs.

5) Auto-run after recalculation: Worksheet_Calculate

Use this when a formula result (not a direct edit) should trigger logic, e.g., notify if today’s orders exceed a threshold.

' Sales worksheet module
Private Sub Worksheet_Calculate()
    On Error Resume Next
    Dim threshold As Double
    Dim todaysUnits As Double

    threshold = 200
    todaysUnits = Me.Range("K10").Value ' <-- Assume K10 holds =SUMIFS(...) for today's units

    If todaysUnits >= threshold Then
        ' Light-touch alert (replace with your action)
        Me.Range("M1").Value = "ALERT: Today's units crossed threshold @ " & Now
    End If
End Sub

Callout: Calculate can fire often; keep it fast and conditional.

6) Auto-run before close: Workbook_BeforeClose

Clean up state, cancel timers, and save gracefully.

' ThisWorkbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Call CancelSchedule
    ' Optional: confirm save
    ThisWorkbook.Save
End Sub

7) Application-level events (pro pattern across all workbooks)

Listen to events raised by the Excel Application—great for governance or global logging.

Step A — Add a Class Module (Insert → Class Module) and name it CAppEvents:

' CAppEvents class
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    Debug.Print "Opened: " & Wb.Name & " at " & Now
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Example: global change logging (keep it light)
    If Target.CountLarge > 1000 Then Exit Sub
    Debug.Print "Changed on " & Sh.Name & " at " & Now
End Sub

Step B — Wire it in ThisWorkbook:

' ThisWorkbook module
Public gApp As CAppEvents

Private Sub Workbook_Open()
    Set gApp = New CAppEvents
    Set gApp.App = Application
    ' Keep the rest of your open logic here
    Call ScheduleDaily
End Sub

Callout: Application-level events are powerful; do minimal work inside them to avoid global slowdowns.

8) Unattended automation: Windows Task Scheduler + VBScript

When Excel isn’t open, Task Scheduler can open the workbook, run a macro, and close.

Create RunAutoDaily.vbs (Notepad → Save as .vbs):

' RunAutoDaily.vbs
Dim xl, wb
Set xl = CreateObject("Excel.Application")
xl.Visible = False

Set wb = xl.Workbooks.Open("C:\Path\AutoRunDemo.xlsm")
xl.Run "AutoDaily"

wb.Close True
xl.Quit
Set wb = Nothing
Set xl = Nothing

Task Scheduler steps (Windows):

1. Open Task Scheduler → Create Task.
2. Triggers: Daily at 08:05.
3. Actions: Start a program → Program/script: wscript.exe
   • Add arguments: "C:\Path\RunAutoDaily.vbs"
4. Run whether user is logged on or not, and Run with highest privileges if needed.
5. (Optional) Set working directory so relative paths in VBA resolve correctly.

Callout: If your org blocks scripts/macros, partner with IT to sign code and use Trusted Locations.

Extra Snippets (real and useful)

DAX (if you export to Power BI / Data Model)

DAX

Revenue := SUM ( Sales[Revenue] )

Closed Orders :=
CALCULATE ( COUNTROWS ( Sales ), Sales[Status] = "Closed" )

High Value % :=
DIVIDE(
    CALCULATE(COUNTROWS(Sales), Sales[HighValueFlag] = "High"),
    COUNTROWS(Sales)
)

SQL (if Sales is sourced from a database)

sql

SELECT
    Region,
    SUM(Units * UnitPrice) AS Revenue
FROM Sales
GROUP BY Region
ORDER BY Revenue DESC;

Python (fire a macro from outside Excel; Windows)

python

# Requires: pip install pywin32
import win32com.client as win32

xl = win32.Dispatch("Excel.Application")
xl.Visible = 0
wb = xl.Workbooks.Open(r"C:\Path\AutoRunDemo.xlsm")
xl.Run("AutoDaily")
wb.Close(SaveChanges=True)
xl.Quit()

Troubleshooting: common errors, causes, and fixes

Performance tips & best practices (battle-tested)

• Target only what changed: Use Intersect and Target.CountLarge to exit fast.
• No .Select flex: Work with objects directly; it’s 10x faster and cleaner.
• Batch writes: Push arrays into ranges in one go.
• UI off during work: Application.ScreenUpdating=False, DisplayStatusBar=True.
• Calculation discipline: Temporarily switch to manual for big updates; revert afterward.
• Avoid volatile overload: Limit NOW(), TODAY(), OFFSET(), INDIRECT() explosions.
• Error hygiene: Always restore EnableEvents/ScreenUpdating in CleanExit.
• Structure: Keep event procedures thin; call out to small, testable helper subs.
• Security: Never hard-code credentials in VBA; use Windows auth or protected connections.
• Governance: Consider code signing and source control (yes, for VBA too).

Comparison Box — Workbook_Open vs Application.OnTime

Mini-Case Study: From Manual Grind to Hands-Free Reporting

Context: A sales ops analyst spent 45–60 minutes each morning: open workbook → refresh → filter → validate → export PDF → email. Errors were creeping in; reports sometimes landed late.

Solution:

• Workbook_Open stamps metadata and kicks off ScheduleDaily.
• AutoDaily refreshes queries, recalculates, performs threshold checks, exports DailySales.pdf, and saves.
• Windows Task Scheduler runs a .vbs at 08:00, opening the workbook and letting AutoDaily handle the rest.

Before → After (30-day average):

• Time per day: 50 mins → 3 mins of quick eyeballing (94% reduction).
• Late deliveries: 4 per month → 0.
• Validation failures: 3 per month → <1 (manual override only).
• Analyst focus time regained: ~19 hours/month.

Takeaway: Event-driven + scheduled automation delivers consistent SLAs with minimal human middleware.

FAQ: Auto Run VBA Code in Excel

1) Why doesn’t my Workbook_Open run?
It must live in ThisWorkbook (not a standard module). Also confirm macros are enabled and the file is .xlsm.

2) Can I trigger code when a formula result changes?
Yes—use Worksheet_Calculate. Keep logic lightweight because it fires frequently.

3) Will Application.OnTime run if Excel is closed?
No. Use Windows Task Scheduler + a small VBScript to open Excel and run your macro.

4) How do I prevent event loops in Worksheet_Change?
Wrap edits in Application.EnableEvents = False/True, and always restore in a CleanExit block.

5) Is it safe to send emails automatically from Excel?
Technically yes (via Outlook automation), but secure recipients, handle errors, and consider organizational policies.

6) What if my refresh takes forever?
Use connection-specific .Refresh BackgroundQuery:=False, trim queries, and run heavy work off-peak with OnTime or Task Scheduler.

7) Can I listen to events across all workbooks?
Yes—Application-level events in a class module with WithEvents Application.

8) How do I cancel a scheduled OnTime run?
Call Application.OnTime nextRun, "AutoDaily", , False (store nextRun globally).

9) Are volatile formulas bad for automation?
They’re fine in moderation. Too many (NOW, TODAY, OFFSET, INDIRECT) can trigger heavy recalculation—avoid in event-dense models.

10) How do I recover if events got turned off after a crash?
Open the Immediate Window (Ctrl+G) in the VBE and run: Application.EnableEvents = True.

Deliverable: code roll-up (copy/paste ready)

ThisWorkbook

Public gApp As CAppEvents

Private Sub Workbook_Open()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Stamp metadata
    With Sheets("Sales")
        .Range("J1").Value = "Last Opened:"
        .Range("K1").Value = Now
        .Range("J2").Value = "Row Count:"
        .Range("K2").Value = Application.WorksheetFunction.CountA(.ListObjects("tblSales").ListColumns("OrderID").DataBodyRange)
    End With

    ' Start app-level events
    Set gApp = New CAppEvents
    Set gApp.App = Application

    ' Refresh + schedule
    ThisWorkbook.RefreshAll
    DoEvents
    Application.Calculate
    Call ScheduleDaily

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
CleanFail:
    Resume CleanExit
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Call CancelSchedule
    ThisWorkbook.Save
End Sub

Sales worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lo As ListObject
    Dim statusCol As Range, closedCol As Range
    Dim rngHit As Range

    On Error GoTo SafeExit
    Set lo = Me.ListObjects("tblSales")
    Set statusCol = lo.ListColumns("Status").DataBodyRange
    Set closedCol = lo.ListColumns("ClosedDate").DataBodyRange

    Set rngHit = Intersect(Target, statusCol)
    If rngHit Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False
    If LCase$(Target.Value2) = "closed" Then
        closedCol.Cells(Target.Row - statusCol.Row + 1, 1).Value = Date
    Else
        closedCol.Cells(Target.Row - statusCol.Row + 1, 1).ClearContents
    End If
SafeExit:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    On Error Resume Next
    If Me.Range("K10").Value >= 200 Then
        Me.Range("M1").Value = "ALERT: Today's units crossed threshold @ " & Now
    End If
End Sub

Standard module (Module1)

Public nextRun As Date

Sub ScheduleDaily()
    nextRun = Date + TimeSerial(8, 5, 0)
    If nextRun <= Now Then nextRun = DateAdd("d", 1, Date + TimeSerial(8, 5, 0))
    Application.OnTime nextRun, "AutoDaily"
End Sub

Sub CancelSchedule()
    On Error Resume Next
    Application.OnTime nextRun, "AutoDaily", , False
End Sub

Sub AutoDaily()
    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ThisWorkbook.RefreshAll
    DoEvents
    Application.Calculate

    With Sheets("Sales")
        .Range("A1").CurrentRegion.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "\DailySales.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End With

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Call ScheduleDaily
    Exit Sub
CleanFail:
    Resume CleanExit
End Sub

Class module CAppEvents

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    Debug.Print "Opened: " & Wb.Name & " at " & Now
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1000 Then Exit Sub
    Debug.Print "Changed on " & Sh.Name & " at " & Now
End Sub