excel 2010 freezing- not responding with macro

[thereseexceljakubiak]

hello, i just wanted to see if anyone had a workaround for Excel freezing/ghosting out/shutting down/not responding when working with macros?


i currently use a Dell inspiron 1764 with CPU i5 8 GB of Ram Windows 7 64-bit OS.


Is it my computer issue or Excel issue??

 

[keymaster]

This probably has very little to do with your computer configuration and more to do with your macro code. To begin with, I suggest following checks:

• Break your macro in to smaller sub macros and run one after another
• If you have a loop that processes lots of rows, try running it for few hundred rows and see if everything works ok
• Disable screen updates etc.
• If you are selecting worksheet cells in the macro, replace them with direct updates of values. You do not need to select a cell to write to it.
• Try to minimize jumps from VBA to Excel as much as possible. That is, if your code accesses a range of values to do something, try to copy the range to an array internal to your code, do all the processing and then copy back.
  Close all other programs, memory hungry widgets etc. Turn of any services / processes you do not need.
  In your code at every important point, add below time logging statements. To begin with, use below code:
  

  debug.print now

  
  This tells you which set of statements may be taking too much time. You can then optimize that section.
  
  Try to run the same macro on more powerful computer. If that solves the problem, you may be able to say it is just your computer. From my experience, most day to day macros should run ok on almost every computer with the configuration you mentioned.
  Have a read of our VBA optimization guide and see if you have followed all the ideas.
  
  http://chandoo.org/wp/2012/03/22/vba-macros-optimization-techniques/
  

 

[thereseexceljakubiak]

Thank you Chandoo! I appreciate all of your input! You just saved me $2K in buying a new PC!