How to Fix Excel VBA Sheet Errors for XLSB Workbooks on Mac
Question details
The user is encountering application-defined errors (such as Error 40036) when attempting to access or delete the sixth or later worksheet in an XLSB workbook using VBA on a Mac.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- Running a VBA macro to delete or manipulate specific worksheets (e.g., Sheet 'F') within an XLSB (Excel Binary Workbook) file.
- Observed behavior
- Excel for Mac throws an error and fails to execute the VBA code on the targeted worksheet, despite the syntax being correct, indicating potential workbook corruption or path issues.
Create a secure backup copy of your XLSB workbook to a different folder to prevent any accidental data loss while troubleshooting corruption and testing VBA macros.
Rename the Problematic Worksheet and Restart Excel
This method forces Excel to rebuild its internal metadata and references for the specific worksheet, bypassing temporary glitches or minor corruption.
Often, VBA errors such as Error 40036 occur not because of incorrect code, but because Excel's internal reference to that specific sheet has become corrupted. Renaming the sheet and restarting the application completely can clear out the cached error state.
Double-click the tab of the problematic worksheet (e.g., Sheet 'F') at the bottom of the window, type a temporary new name, and press Enter.
Click 'File' in the top menu bar and select 'Save' to apply the new worksheet name to the file.
Press Command-Q on your Mac keyboard to completely quit Microsoft Excel, ensuring the application does not remain running in the background.
Relaunch Excel, open your XLSB workbook, double-click the renamed sheet tab, and change the name back to its original title before running your macro again.
Clean Up File Paths and Remove Special Characters
Fixes VBA pathing failures by removing unsupported characters from your file, folder, and startup volume names.
Transfer Data to a New Workbook
Bypasses deep-rooted workbook corruption by copying your data into a completely new, clean file.
Try WPS Office for Seamless Spreadsheet Management on Mac
If you continue to encounter persistent VBA errors or corrupted workbooks in Microsoft Excel for Mac, switching to WPS Office provides a stable, free, and lightweight alternative. It offers a familiar interface that makes spreadsheet management and data migration effortless.
- 1. Download and install: Download WPS Office for Mac from the official website and complete the quick installation process.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and open your existing .xlsb or .xlsx file directly without any format conversion.
- 3. Manage your worksheets: Easily add, delete, and manipulate worksheets using a stable platform that prevents common metadata corruption issues.
Frequently Asked Questions
Why does my Excel VBA macro return Error 40036 when deleting a worksheet?
Error 40036 is an application-defined or object-defined error. On Excel for Mac, it frequently happens when the workbook's metadata is corrupted, or the worksheet object cannot be found due to broken internal references or unsupported characters in the file's directory path.
How can I tell if the issue is a VBA syntax error or workbook corruption?
You can isolate the issue by creating a brand-new test workbook with the exact same sheet names (e.g., a sheet named 'F'). Run your deletion macro on this new file. If the macro works perfectly, your code syntax is correct, and the issue lies in the corruption of the original workbook.
Does saving an XLSB file as an XLSM fix VBA worksheet errors?
It can. The XLSB (Excel Binary Workbook) format stores data differently than XML-based formats. Saving the file as an XLSM (Excel Macro-Enabled Workbook) forces Excel to rewrite the file structure, which can occasionally strip away the corruption causing the worksheet access errors.
