VBA Workbook.Open运行时错误'91'及全局最大化工作簿宏求助

Got it, let's break down why you're hitting that error and get your global maximization macro working reliably.

Why Your Current Code Fails #

The error Run-time error '91' happens because ActiveWindow might not be properly initialized when the Workbook_Open event fires in your PERSONAL.XLSB—especially if Excel is loading in the background or PERSONAL.XLSB itself is opening without a fully active window. Also, placing the code in PERSONAL.XLSB's ThisWorkbook only triggers it when PERSONAL.XLSB opens, not when you launch other workbooks.

The Correct Solution: Application-Level Events #

To make this work for every workbook you open, you need to use an application-level event that listens for any workbook opening. Here's how to set it up step-by-step:

1. Open the VBA Editor (press Alt + F11 in Excel)
2. Insert a Class Module: Right-click VBAProject (PERSONAL.XLSB) in the Project Explorer → Select Insert → Class Module
3. Rename the Class: In the Properties window (press F4 if it's hidden), change the Name property to AppEvents
4. Paste This Code into the Class Module:

   Public WithEvents App As Application
   
   Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
       ' Maximize the entire Excel application window first
       App.WindowState = xlMaximized
       ' Maximize the window of the newly opened workbook (no reliance on ActiveWindow)
       Wb.Windows(1).WindowState = xlMaximized
   End Sub
   

5. Initialize the Class in PERSONAL.XLSB: Open the ThisWorkbook module of PERSONAL.XLSB and replace your existing code with this:

   Private appEvents As AppEvents
   
   Private Sub Workbook_Open()
       ' Set up the application event listener when PERSONAL.XLSB loads
       Set appEvents = New AppEvents
       Set appEvents.App = Application
   End Sub
   

Why This Works #

• Application-Level Listener: The App_WorkbookOpen event triggers every time any workbook opens (including PERSONAL.XLSB itself).
• No Reliance on ActiveWindow: Instead of using ActiveWindow, we directly target the window of the newly opened workbook (Wb.Windows(1)), which eliminates the "object not set" error.
• Dual Maximization: We first maximize the Excel application window, then the specific workbook window to ensure everything is fully expanded.

Final Checks #

• Delete your original Workbook_Open code from PERSONAL.XLSB's ThisWorkbook to avoid conflicts.
• Ensure PERSONAL.XLSB is enabled in Excel's macro settings (go to File → Options → Trust Center → Trust Center Settings → Macro Settings, and check "Trust access to the VBA project object model").
• Test by closing all Excel windows, reopening Excel, and launching any workbook—both the Excel app and workbook window should maximize without errors.

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