Hey there! Let's sort out this formula performance tracking problem together. Dealing with a 19MB Excel file with 40 sheets is tricky when calculations lag, so I’ll share a polished, error-free VBA solution based on the Microsoft guidance you referenced, plus break down common syntax pitfalls new VBA users hit.

If you’re getting syntax errors, here are the most likely culprits:

• Missing Option Explicit: Forgetting this at the top of your module leads to undeclared variable errors. Always include it to catch typos.
• Incorrect Object References: Accidentally referencing Workbooks instead of ActiveWorkbook, or misspelling sheet names (case matters in VBA!).
• Disabled Macros: Make sure your Excel trust settings allow macros to run—otherwise, the code won’t execute at all.
• Unclosed Statements: Forgetting to add End If, Next, or End Sub will throw immediate syntax errors.

This code will loop through every sheet in your workbook, time each formula’s calculation, and output results to a new "Performance Log" sheet. Copy-paste this into a new module:

Option Explicit

Sub TrackFormulaPerformance()
    Dim ws As Worksheet
    Dim perfWs As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim startTime As Double
    Dim endTime As Double
    Dim rowNum As Integer
    
    ' Turn off settings to speed up code execution
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    ' Create or clear performance log sheet
    On Error Resume Next
    Set perfWs = ThisWorkbook.Worksheets("Performance Log")
    If Err.Number <> 0 Then
        Set perfWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        perfWs.Name = "Performance Log"
    End If
    On Error GoTo 0
    
    perfWs.Cells.Clear
    ' Set up log headers
    perfWs.Range("A1:D1").Value = Array("Sheet Name", "Cell Address", "Formula", "Calculation Time (Seconds)")
    rowNum = 2
    
    ' Loop through each worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Performance Log" Then ' Skip our log sheet
            ' Get all formula cells in the sheet
            On Error Resume Next
            Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            
            If Not rng Is Nothing Then
                ' Loop through each formula cell
                For Each cell In rng
                    ' Skip array formulas if needed (uncomment below)
                    ' If Not cell.HasArray Then
                        ' Reset calculation to ensure we're timing a fresh compute
                        Application.CalculateFullRebuild
                        startTime = Timer
                        ' Calculate just this cell
                        cell.Calculate
                        endTime = Timer
                        
                        ' Log the results
                        perfWs.Cells(rowNum, 1).Value = ws.Name
                        perfWs.Cells(rowNum, 2).Value = cell.Address
                        perfWs.Cells(rowNum, 3).Value = cell.Formula
                        perfWs.Cells(rowNum, 4).Value = Round(endTime - startTime, 6)
                        
                        rowNum = rowNum + 1
                    ' End If
                Next cell
            End If
        End If
    Next ws
    
    ' Format the log sheet for readability
    perfWs.Columns("A:D").AutoFit
    perfWs.Range("D:D").NumberFormat = "0.000000"
    
    ' Restore Excel settings
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    MsgBox "Performance tracking complete! Check the 'Performance Log' sheet.", vbInformation
End Sub

1. Open your large Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the "Project Explorer" pane > Insert > Module.
4. Paste the code above into the new module window.
5. Press F5 to run the macro, or click the green "Run" button in the editor.
6. Once finished, a new "Performance Log" sheet will appear with all formula timings—sort the "Calculation Time (Seconds)" column from largest to smallest to find your slowest formulas.

• CalculateFullRebuild: This ensures we’re timing a fresh calculation each time, not relying on cached results.
• Skipping Array Formulas: If you have lots of array formulas, uncomment the If Not cell.HasArray Then block to exclude them (they can skew timing results since they calculate in batches).
• Manual Calculation: The code switches to manual calculation to prevent Excel from recalculating everything in the background while we’re timing individual cells.

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