运行时错误‘栈溢出’求助：每小时导出数据透视图宏报错

Hey there, let's figure out why your macro is throwing that "Out of stack space" error—this usually boils down to recursive loops eating up memory or messy object handling. Let's walk through the fixes step by step:

1. Fix Accidental Recursive Loops (Most Likely Culprit) #

If your ActiveWorkbook.RefreshAll triggers worksheet events like Worksheet_Calculate or Workbook_SheetChange, and those events end up calling your saveFile macro again, you'll get an infinite recursive loop that blows up the stack.

Fix: Disable events at the start of your macro, and make sure you re-enable them even if an error occurs:

Sub saveFile()
    ' Disable events to stop recursive loops
    Application.EnableEvents = False
    ' Set up error handling to restore events no matter what
    On Error GoTo Cleanup

    With Application.ThisWorkbook
        Dim objChart As ChartObject
        Dim myChart As Chart
        Dim today As Variant
        Dim myFileName As String

        ' Replace RefreshAll with targeted pivot table refreshes (safer for timing)
        Dim ws As Worksheet
        Dim pt As PivotTable
        For Each ws In ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
                pt.RefreshTable
                DoEvents
            Next pt
        Next ws

        Set objChart = Sheets("Chart").ChartObjects(1)
        Set myChart = objChart.Chart
        today = Sheets("Date Range").Range("D1").Value
        myFileName = "IRF - Daily Receiving " & today & ".jpg"

        ' Use a full file path to avoid export issues
        myChart.Export Filename:=ThisWorkbook.Path & "\" & myFileName
    End With

Cleanup:
    ' Re-enable events
    Application.EnableEvents = True
    ' Clean up object references to free memory
    Set myChart = Nothing
    Set objChart = Nothing
    ' Show error details if something went wrong
    If Err.Number <> 0 Then
        MsgBox "Error encountered: " & Err.Description, vbExclamation
    End If
End Sub

2. Clean Up Object References #

VBA's garbage collection isn't perfect—if you run this macro hourly, leftover ChartObject and Chart references can pile up over time, eating into memory. Adding Set [object] = Nothing at the end ensures those resources are properly released.

3. Avoid Premature Execution After Refresh #

A single DoEvents after RefreshAll might not give Excel enough time to finish refreshing pivot tables. By looping through each pivot table and refreshing individually (with a DoEvents after each), you ensure everything is fully ready before you try to export the chart.

4. Declare All Variables Locally #

Make sure variables like today and myFileName are declared inside the macro (using Dim) instead of being global. Global variables can retain old values between runs, leading to unexpected behavior that might contribute to memory issues.

内容的提问来源于stack exchange，提问作者Chemdawg