CODEWITHSUNDEEP
excelvbaexcel-365
James Howard
James Howard

Reputation: 61

VBA Workbooks stop working with upgrade to Excel 365 - VBA Compiling Issue?

Since upgrading to Excel 365, my company has been having all sorts of trouble with VBA-supported Excel workbooks crashing or not functioning properly. The issues have popped up in various workbooks and various departments, including seemingly simple VBA workbooks. I've discovered a fix (see below) but it's not sustainable.

The Issues

The various issues we have experienced are listed below. Note, these issues only occur when you open a file in Excel 365 desktop. The same files, when opened in the Excel 365 browser app or in Excel 2016 will work fine. Also, all these issues happen at random. A user may have been working in a file for weeks and then the next time they open the file they get one of these errors.

The Fix

Opening the VBA Editor window, then selecting Debug -> Compile VBAProject seemingly fixes the issue. I've yet to experience any of the above issues where the file had been manually compiled this way. The problem with this is that every time you add code to a file or any time you add a new tab to a file, you have to go in and perform this manual compile again.

Yes, apparently adding a single tab in a file changes the workbook structure enough that it is necessary to compile again. The Compiled VBAProject selection will be greyed out. But adding a tab (or adding new VBA) will un-grey it and require another manual compile or the problems start occurring again.

This fix also works to repair files where the above issues are occurring. To fix those files, you can:

  1. Make sure "Disable Trusted Documents" and "Disable VBA macros with notification" are toggled on in your security settings so that VBA doesn't automatically run when you open a file.
  2. Open the file with the errors but do not enable the VBA.
  3. Go to the VBA Editor window, select Debug -> Compile VBAProject.
  4. Save the file, close it, reopen it, enable the VBA, and everything works fine again.

But again, this isn't sustainable because all my users will have to remember to go compile VBA any time they add a new tab to a workbook.

Help?

Has anyone found a more sustainable fix to this issue? Is there a bug with the Excel 365 Desktop automatic compiler that Microsoft is working on?

Upvotes: 1

Views: 11517

Answers (1)

James Howard
James Howard

Reputation: 61

I think I perhaps found an answer in this MSDN thread:

Per that thread:

the cause is that Excel does not correctly save the compile state of the VBA code and 64 bit Excel cannot recover from that issue when opening the afflicted Excel file (32 bit usually can). A fix was released for only Excel 2016 and not for other versions

That would indeed confirm that it is a bug within Excel and explains why we only see the issue with 365 64-bit Excel.

That also explains why my manual compile fix works. Based on the article I found, there is a more sustainable fix. You can change Excel’s registry and force VBA to compile accurately.

To implement the permanent fix:

  1. Open the start menu and type “reg” and select the “Registry Editor”
  2. Navigate to: Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\options
  3. On the Edit menu, point to New, and then click DWORD Value.
  4. Type ForceVBALoadFromSource, and then press Enter.
  5. In the Details pane, right-click ForceVBALoadFromSource, and then click Modify.
  6. In the Value data box, type 1, and then click OK.

Upvotes: 5

Related Questions