Why is Excel crashing when loading Macro-Based workbooks?

I have used the same macro-based workbooks for years. Just recently, I attempted to load a workbook I use every day, but this time, after the Excel splash screen appeared and the file began to load, Excel suddenly vanished off the screen. I attempted to reload the same workbook and a file recovery panel appeared asking which copy I would like to save. This was not what I was expecting.

The Fix:

You can read how I derived this short fix in the paragraphs that follow, however, for those in a hurry to get back to normal: Remove the .NET 5 Runtime from your computer.

If you installed Visual Studio 2019 and the latest .NET Desktop Development workload, you likely have .NET 5 running on your system. The easiest way to determine the active .NET runtime is to open a terminal (cmd.exe) and type the following at the dos prompt: dotnet –info

The first item to appear on the console will be the currently active .NET runtime followed by a lot more information regarding .NET options on your machine.

If you installed .NET 5 using the Visual Studio installer, you must remove it using the Visual Studio installer.

Trouble Shooting to the Fix

I went to another computer and loaded the same file from the Network server and everything worked as expected. The file itself was not corrupt and the server connection was working fine. The question is, why is Excel crashing or disappearing from my screen?

I searched for answers on the web and although none of the solutions addressed my specific issue, the suggestion to perform a Quick Fix or an Online Fix could not be ignored. The Quick Fix did not solve the problem, nor did the Online Fix.

I opened a blank workbook and attempted to start VBA from the Developer tab. Excel became unresponsive almost immediately. I uninstalled Microsoft Office for Enterprise and re-installed it again. I restarted the computer to make sure all the changes would be in effect. Attempts to load the macro-based Excel workbooks continued to fail.

I changed the security settings in Excel to disable macros and also turned off the “Compile on Demand” feature from within the VBA > Tools settings. The file loaded, but I couldn’t run the macros required to retrieve the latest data from the Server.

Something on my computer changed and the only possible culprit was the recently added Visual Studio 2019 (16.9.0) Preview 2.0. Why would that change what was happening with Excel? After reading about DPI awareness and settings that may affect other applications, I thought maybe that had something to do with what was going on. While I received an education on DPI awareness, the graphs and other features of Excel were performing as expected – except for VBA.

I decided to run the Visual Studio Installer again and noted the description below the “.NET desktop development” workload: Build WPF, Windows Forms, and console applications using C#, Visual Basic, and F# with .NET Core and .NET Framework.

The Universal Windows Platform development workload has a similar description: Create applications for the Universal Windows Platform with C#, VB, or optionally C++.

When these options are selected, the .NET 5.0 Runtime is also selected. I ran the dotnet –info command from a terminal screen and confirmed that .NET 5.0 was active.

Resolving The FIX

I discovered that Windows automatically runs the latest version of the .NET runtime installed on your computer even though this was installed using Visual Studio 2019. Although there was mention of using or modifying a global.json file, a search of my drives revealed that no such file existed on my system.

Getting back to the Visual Studio Installer, I unchecked the .NET 5.0 Runtime under the Individual Components tab and removed it from my system. After restarting my computer, I attempted to load the macro-based Excel workbook again. The file loaded successfully and everything was back to normal.

I advised Microsoft of the concern as I managed to repeat the results confirming successful loads and VBA functionality without .Net 5 and failure to load macro-based workbooks with .NET 5 active.

This process took a few days to resolve as the link to Visual Studio 2019 and .NET 5 was not immediately clear. No one else seems to have reported this error so Google was of little help this time around.

If you found this article, I’m hoping that it will save you from having to jump the hurdles I did. A most frustrating experience that became a moment of great learning.

Leave a Reply