Excel crashes when trying to save one particular xlsm only after running macro

Well, I have been having certain moments experienced when my excel file used to get stuck up with no response from the application for minutes and sometimes hours - with no more options I used to KILL the process using task manager.

As per my experience, there are many obvious reasons which can effect the performance of a macro. Below are the listings:

1. Calculations : We know that - after making any changes to any cell in the sheet, if there are other cells dependant on the changed cell - those dependant cells would reflect the changes - due to Application.Calculation setting set to Automatic.

2. Triggering of Events due to Changes : Macros tend to make changes in the cells, so if there are events defined to get triggered when changes happen, they get fired along with each changes that are made to the sheets. Very likely, we do define certain events like Worksheet_Change(), so those events get triggered everytime the values in any cell in the sheet is changed.

3. Screen Updates : Well, some macros do involve opening / closing / switching files / sheets - these kind of updates when done by processor while running macro is just too fast that even the outputs that are sent over to the display unit gets the images piled up and sometimes stops responding. This again, does take good amount of display memory and CPU usage.

4. Alerts Thrown by Application (Optional, not actually slows down but prompts for annoying actions): Macro sometimes performs tasks that requires user interventions like merging of cells. Though it doesn't create a harm in saying "yes! let's do this", but what if we have to do this for a hundred cells, where I just want columns A should merge with B for next 100 rows? You'll have to say, "Yes!" for next 100 times.

So you can boost up your macro speed if you set a control over these things. If you know when to perform what.. you can easily direct the flow and speed of your macro. Each of these feature has its own importance. Its just that one should know what to do and when to do.

here's a piece of code to Boostup your macro speed (My personal favorite method which I incorporate in every reporting tool of mine). Do make sure when to Enable / Disable them.

Please Login or Register  to view this content.

Add this method and call it as

BoostMacroPerformance True

in the beginning of your code and when about to end

BoostMacroPerformance False

Do Make use of :
1. Application.Calculate() : When you want the sheet to calculate each cell before performing any task, and
2. Application.EnableEvents = True / False : Whenever you need the sheet to raise / avoid an event if you've defined them to run when values are changed.

if none of the above things work out for you.. then you need to go through the logic of your codes. And must avoid lot of calculations in the sheet. Especially like, SUMPRODUCT, COUNTIFS, COUNTIF, SUMIF, SUMIFS and other conditional type functions. Better use functions through macros for if there are thousands of cells performing just those heavy calculations and if only values in the cell can satisfy your needs.

I believe using the above tips efficiently, you will be able to cater your issues with Not-Responding thing. After I have started implementing these into my reports and works, I have observed "ZERO" crashing of Excel application till date - even if I have a hundred thousands of cells filled, and my macros run at good speed too.

Another possible reason can be that you have that file open which the macro is trying to open..