This is more of a rant out of frustration than a question I suppose.
I am working in Excel 2016 (16.0.4966.1000 32-bit)
I have a workbook that I update every month. Each month I make a copy of the previous month's workbook and update it with new data. It works for a time and then at some point every thing I do causes a "Excel has stopped working" message. Every single month this is happening. Performing the below steps seems to fix until the next month. Step 9 never produces any compile errors.
Is this because my company is still using Excel 2016 and it's been fixed in 365? Why can't it just work????
- Open a new Excel workbook, not your crashing workbook
- Go to the Developer Tab and click “Macro Security” in the Code section of the Ribbon
- Click the bullet option entitled “Disable all macros with notification”
- Go to the Trusted Locations tab on the left and check the “Disable all Trusted Locations” box at the bottom
- Go to Trusted Documents and check the “Disable all Trusted Documents” box
- Click OK and close the new workbook
- Open your Excel file that was crashing
- Do not click "Enable Macros" - instead go to the Developer Tab and open the Visual Basic Editor
- In the VB Editor, save the project and then click Debug > Compile VBAProject
- Correct any compile errors that occur
- Save the VB project again and then save the Excel workbook
- Revert any changes you made to Macro Security in steps 3-5
- Close Excel, re-open the previously crashing workbook, and Enable Macros
