If you are experiencing seemingly random Excel crashes, one cause can be an Excel bug to do with corrupted VBA code in your workbook.
To test if the VBA code is the problem, or if it's something else, you can create a copy of the problematic workbook and delete all of the VBA code from it. If it opens reliably without the VBA code, that is a good indicator that the problem is an Excel bug related to the VBA code.
With PyXLL you can replace all of your VBA code with Python. When working with legacy workbooks you may still need to deal with VBA code. If you find yourself writing new VBA code because you don't think it is possible to do what you need to in Python, get in touch with us and we will explain how to do what you need without any VBA.
How to fix corrupted VBA code
When you save your Excel workbook, Excel saves a compiled form of your VBA code which makes it is faster to load next time. Unfortunately, it appears that due to some Excel bugs this compiled VBA code is also exactly what can cause Excel to crash.
If you can open the workbook, go to the VBA Editor and compile the project. This will force Excel to recompile the VBA code and update what is saved in the workbook next time you save it.
An Automatic Solution
Excel has the ability to always recompile VBA code when loading a workbook. It needs a tweak to the registry to enable this feature.
Thanks to Charles Williams from Decision Models for letting me know about this!
- With RegEdit, locate the key "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options"
- Add a new DWORD value called "ForceVBALoadFromSource".
- Assign the value 1 to this DWORD
If you notice this slowing down opening your workbooks, after the corruption is fixed you can set it to 0 to disable this feature.