Excel工作簿宏占用内存过大，寻求保留宏的体积缩减及切换方案

Great question! Let's break down how to solve this—since you need to keep macros but shrink the file size when they're inactive, we have two solid approaches depending on what's causing that 80MB bulk.

First, figure out why your workbook is so big:

• If it's hidden data, shapes, or macro-generated cache in the worksheets: Go with Approach 1 (safer, no risk of losing code).
• If it's large VBA modules with tons of constants, arrays, or embedded resources: Go with Approach 2 (direct code management).

This method keeps all your code in the workbook but disables its execution and clears out volume-heavy elements when inactive.

1. Add a Global Switch Variable
   Open the VBA Editor (Alt+F11), double-click ThisWorkbook in the Project pane, and paste this:

Public MacroEnabled As Boolean ' Controls whether macros are active across the workbook

2. Update Your Existing Worksheet Macros
   Modify every worksheet macro (like Worksheet_Change, Worksheet_SelectionChange) to check the switch first:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Exit immediately if macros are disabled
    If Not ThisWorkbook.MacroEnabled Then Exit Sub
    
    ' Your original macro code goes here
    ' ...
End Sub

Repeat this for all worksheet-specific macros across the 4 sheets.

3. Create the Toggle Control Macro
   Add a new module (right-click your workbook in the Project pane → Insert → Module) and paste this:

Sub ToggleMacroStatus()
    ' Flip the enabled status
    ThisWorkbook.MacroEnabled = Not ThisWorkbook.MacroEnabled
    
    ' Clean up or restore redundant data per worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Sheet1"
                If ThisWorkbook.MacroEnabled Then
                    ' Restore macro-dependent elements (e.g., unhide cache columns, rebuild shapes)
                    ws.Columns("Z:Z").Hidden = False
                    ' Regenerate any macro-created temp data here if needed
                Else
                    ' Remove redundant elements to shrink file size
                    ws.Columns("Z:Z").Hidden = True
                    On Error Resume Next ' Avoid errors if the shape doesn't exist
                    ws.Shapes("TempMacroShape").Delete
                    On Error GoTo 0
                End If
            Case "Sheet2"
                ' Repeat the logic for Sheet2 (adjust columns/shapes as needed)
            Case "Sheet3"
                ' Repeat for Sheet3
            Case "Sheet4"
                ' Repeat for Sheet4
        End Select
    Next ws
    
    ' Notify the user
    If ThisWorkbook.MacroEnabled Then
        MsgBox "Macros enabled! Save the workbook now to restore the original 80MB size."
    Else
        MsgBox "Macros disabled! Redundant data cleared—save now to shrink the file for emailing."
    End If
End Sub

Customize the column/shape references to match what's actually bloating your worksheets.

4. Add a Toggle Button

• Enable the Developer tab (right-click the ribbon → Customize the Ribbon → Check "Developer").
• Go to Developer → Insert → Button (Form Control), drag it onto any worksheet.
• When prompted, select ToggleMacroStatus as the macro to assign, then rename the button to something like "Toggle Macro Status".

If the bulk comes from large VBA modules themselves, this method removes the modules when inactive (shrinking the file) and reimports them when you need macros.

⚠️ Note: You need to enable VBA project access first:
File → Options → Trust Center → Trust Center Settings → Macro Settings → Check "Trust access to the VBA project object model".

1. Export Your Existing Modules

• Open the VBA Editor (Alt+F11).
• Right-click each worksheet module (e.g., Sheet1, Sheet2) → Export File → Save to the same folder as your workbook (name them Sheet1.bas, Sheet2.bas, etc.).
• After exporting, right-click each module → Remove → Choose "No" when asked to export (you already did this). Your workbook will shrink immediately.

2. Create the Toggle Module
   Add a new module and paste this:

Sub ToggleMacroModules()
    Dim modulePath As String
    modulePath = ThisWorkbook.Path & "\" ' Use the workbook's folder for module files
    
    Dim wsName As Variant
    Dim moduleExists As Boolean
    Dim vbComp As Object
    
    ' Check if modules are already imported (macros enabled)
    moduleExists = False
    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        If vbComp.Type = vbext_ct_Document Then
            If vbComp.CodeModule.CountOfLines > 0 Then
                moduleExists = True
                Exit For
            End If
        End If
    Next vbComp
    
    If moduleExists Then
        ' Disable: Clear all worksheet module code
        For Each wsName In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
            On Error Resume Next
            ThisWorkbook.VBProject.VBComponents(wsName).CodeModule.DeleteLines 1, _
                ThisWorkbook.VBProject.VBComponents(wsName).CodeModule.CountOfLines
            On Error GoTo 0
        Next wsName
        MsgBox "Macros disabled! Modules cleared—save now to shrink the file."
    Else
        ' Enable: Re-import the saved modules
        For Each wsName In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
            On Error Resume Next
            ThisWorkbook.VBProject.VBComponents(wsName).CodeModule.AddFromFile _
                modulePath & wsName & ".bas"
            On Error GoTo 0
        Next wsName
        MsgBox "Macros enabled! Modules imported—save now to restore original size."
    End If
End Sub

3. Add the Toggle Button
   Follow the same button creation steps as Approach 1, assigning ToggleMacroModules instead.

• Always save after toggling: The file size change only becomes permanent when you save the workbook in the desired state.
• Approach 1 is safer because you never lose your code—you're just controlling execution and cleaning up data.
• Approach 2 requires keeping the exported .bas files in the same folder as your workbook; if you move the workbook, bring the .bas files with you.

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