How to Fix the End Sub Expected Error in an Excel Startup Macro
Question details
The user is encountering an 'End Sub expected' compile error when trying to run a VBA macro automatically at startup.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Deploying a VBA macro to run automatically when the Excel workbook opens.
- Observed behavior
- An 'End Sub expected' compile error prevents the code from executing, usually triggered by duplicate Sub declarations or an unmatched End Sub statement within the module.
Before modifying your VBA code, click 'OK' on the error dialog to stop the debugger and switch the VBA editor out of break mode.
Remove Duplicate Sub and End Sub Statements
Identify and delete extra Sub declarations or mismatched End Sub statements to fix the code's syntax structure.
The 'End Sub expected' compile error typically occurs when you have nested procedures (a Sub within a Sub) or when you accidentally paste code containing a new 'Sub [Name]()' line inside an existing procedure. Visual Basic for Applications requires exactly one closing statement for every opening declaration.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications editor.
In the Project Explorer pane on the left, double-click 'ThisWorkbook' or the specific module where your startup macro is stored to view the code.
Look for a nested or redundant declaration, such as 'Sub MacroTimeStamp()', located inside another Sub. Highlight the extra line and delete it.
Scroll to the bottom of your active procedure. Ensure there is only one 'End Sub' closing the routine. If you see two consecutive 'End Sub' lines, delete one of them.
Go to the top menu and click 'Debug' > 'Compile VBAProject'. If no error pops up, your syntax is corrected.

Debug and Run Macros Seamlessly with WPS Spreadsheet
WPS Spreadsheet features a powerful, built-in VBA editor, allowing you to write, debug, and execute startup macros with full format compatibility.
- 1. Open Your Workbook: Launch your macro-enabled workbook (.xlsm) using WPS Spreadsheet.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on the 'VBA Editor' icon.
- 3. Debug Your Code: Use the built-in code editor to step through your startup macro and remove the duplicate 'Sub' or 'End Sub' causing the error.
- 4. Save and Run: Save your changes and reopen the document to trigger the startup event without encountering any compile errors.

Frequently Asked Questions
What does 'End Sub expected' mean in VBA?
It means the VBA compiler found a 'Sub' statement but could not find a corresponding 'End Sub' to close it, or there is an improperly nested procedure (a Sub declared inside another Sub).
Where should Excel startup macros be placed?
Code that needs to run automatically when the workbook opens should be placed in the 'Workbook_Open' event inside the 'ThisWorkbook' object module, rather than in a standard module.
Why does the error highlight a different line than the actual mistake?
The VBA compiler reads the code sequentially. It only triggers the 'End Sub expected' error when it reaches the end of the module or encounters another declaration without finding the necessary closing statement for the previous one.




