使用日期输入宏时出现Error 9（下标越界）问题求助

Hey there, I get that frustrating feeling when something works everywhere except your own setup—let’s figure this out together. Since the macro runs fine on other accounts/computers but throws Error 9 on your Excel 2016 instance, the issue is almost certainly tied to your local environment or user profile. Here’s a step-by-step breakdown of fixes to try:

1. Verify Object References in the Macro #

Error 9 usually pops up when the code tries to access an object that doesn’t exist (or can’t be found) in your current context.

• Double-check if the macro uses hardcoded worksheet/workbook names (like Sheets("CustomerBookings") or Workbooks("OldTemplate.xlsm")). If you renamed the new Excel 2016 file or its sheets, the code will fail to locate them.
• Switch to using worksheet CodeNames instead of display names for reliability. In the VBA Editor, look at the Properties window (F4) for your sheets—use the (Name) value (e.g., Sheet_Bookings) instead of the visible tab name. This way, renaming tabs won’t break the code.
• Ensure any named ranges referenced in the macro exist exactly as written in your 2016 file.

2. Reset Your Excel User Profile #

Corrupted user settings are a common culprit for weird VBA glitches. Let’s refresh your profile:

• Close all Excel windows completely.
• Open File Explorer and paste %appdata%\Microsoft\Excel into the address bar. Move any files in the XLSTART folder to a backup location (this removes startup add-ins that might interfere).
• Next, paste %localappdata%\Microsoft\Office into the address bar. Find the folder labeled Excel16 (for 2016), rename it to Excel16_old, then restart Excel. A fresh profile will be created automatically.

3. Adjust Excel Trust Center Settings #

Security restrictions might be blocking the macro from accessing necessary objects:

• Go to File > Options > Trust Center > Trust Center Settings.
• Under Macro Settings, temporarily select "Enable all macros (not recommended; potentially dangerous code can run)" (just for testing—switch back later if it works). Also, check the box for "Trust access to the VBA project object model".
• Add the folder containing your Excel 2016 file to Trusted Locations to avoid security-related access blocks.

4. Update Office and Check Compatibility #

Outdated Office versions can cause compatibility issues with older macros:

• Go to File > Account > Update Options > Update Now to install all pending Excel 2016 updates.
• If your original macro file was saved in a newer Excel format, try saving it as an Excel 97-2003 Workbook (.xls) first, then port the macro to your 2016 file. Sometimes older formats resolve compatibility quirks.

5. Debug to Pinpoint the Exact Error Line #

To get to the root of the problem, use the VBA debugger:

• Open the VBA Editor (Alt+F11), find your macro.
• Click on the first line of the macro and press F9 to set a breakpoint.
• Run the macro—it will pause at the breakpoint. Press F8 to step through the code line by line. Note exactly which line triggers Error 9. This will tell you if it’s a missing worksheet, invalid array index, or something else entirely.

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