Excel Macro Code Disappearing in Network Trusted Location

Question

This problem -may- have started around when I switched from Office 2019 to 365, however I've been doing so much upgrades lately (windows 11, server 2022, etc) that my memory is foggy.

Over the past 6 years, I have developed a plethora of Macro-Enabled excel sheets, most of which are templates that get duplicated for various projects. All of these files have their code password protected (Tools > VBAProject Properties > Protection > Lock project for viewing) They are all located on a central file share (We'll call it "\FileShare"). This location has been added to the Trusted Locations for years now ("Allow Trusted Locations on my network" = true, "Subfolders of this location are also trusted" = true).

I started having issues at first where seemingly random files would start getting "corrupted" or getting "Catastrophic Error" pop ups when trying to run macros, or save the file. I've found optimal work arounds for these:

• Adding "AccessibilityCplAdmin 1.0 type Library" to references
• Files > Options > Trust Center > Trust Center Settings > Macro Settings > Trust Access to the VBA project object model = True
• And in some cases, exporting the code modules, and creating a template from scratch.

However, there are a handfull of "overview" spreadsheets that I have auto-update (run a macro) every night through Task Manager. This is done through some simple VB script files that are called via cscript. Maybe 1-2 times a week, 1 or more of these files fails to run overnight. I then go to open the file, and find that all the Modules are still there, but clicking them reveals not code. I also found that attempting to run a macro freezes the file. Additinally, attempting to add a module freezes the file.

the only workaround I've found is to move the file to a non-trusted loction (desktop), open the file, open VBA window, unlock the macros, debug>complie code, save file, and move back to the \FileShare. I've even noticed when doing this, when moving the file back over, the file size always increases (like it suddenly "added back" the VBA code). however, this is not a permanent fix, and I've found the issue keeps reoccuring. And it's just frustrating that something that has been working without fail for years now, suddenly decides to start having issues.

I've tried re-installing Office. I've even taken a spare Desktop and migrated EVERYTHING to it just to make sure it wasn't a machine-specific issue. Problems still happening.

If this is the "new normal" for me, so be it. But I'd at least like to figure out WHY.

EDIT (03/13/24)

I now remember that the issue started when I switched from using x32 Excel to x64 Excel. The accepted answer addresses this exactly.

Answer 1

VBA in Workbooks Stops Working with Upgrade to Excel 365-VBA Compiling Issue

In my and my client’s continuing struggles with the extremely annoying Excel VBA file corruption issues (VBA code in workbooks stop working with upgrade to Excel 365), I think I may have found something new worth trying. See the Stackoverflow link below, with the relevant proposed solution printed out. I’ve just applied this change to my PC Registry, but I’m hesitant to recommend anyone else do this as I’m clearly no expert in making such PC system changes. https://stackoverflow.com/questions/69018012/vba-workbooks-stop-working-with-upgrade-to-excel-365-vba-compiling-issue
I think I perhaps found an answer in this thread: https://lnkd.in/gU3E2bfG

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 or Name pane, right-click ForceVBALoadFromSource (that you’ve just added), and then click Modify.
6. In the Value data box, type 1, and then click OK.