Thank you Paul Macoo and Tom Ogilvy for your advices. Unfortunately, i
do not have page breaks in my worksheet, and the worksheet/workbook is
saved when it is being created. i also noticed that it is not a memory
problem, as when i use the windows utility to check the performance of
the computer, it is the CPU processor that hits 100% all the time when
running the macro the second time (without restarting excel). It only
runs on normal CPU processor usage after I've restarted excel. But
even then, it will only run normally at acceptable speed the first time
after I've restarted it, not the consequent times.

Another problem i face with another macro that my colleague has written
is that excel crashes all the time, randomly, and before the macro is
being run. (It says "Excel.exe has generated a problem and will be
closed by Windows. You will need to restart the program.") It crash
the moment I do this: ToolsMacroMacro. This is suppose to bring up
the macro dialogue box for the me select which macro to run. This
happens absolutely random, and it only happen recently as this macro
grows in complexity and size. It's a poorly designed macro, with heaps
of loops that can run as many as 65,335 times, or scan to up 10,000
cells, with heaps of IF and Case statements everywhere. I know this
much, if the macro is poorly designed, it runs slowly and takes up a
lot of processing power and memory resources, but I am not sure why it
crash and kills excel.

Does anybody has any experience in this? Please advice.


---
Message posted from http://www.ExcelForum.com/

Lee,
This web page has some excellent tips on speeding up macros:
http://www.cpearson.com/excel/optimize.htm
Regards
Ian

"lee777 " wrote:

Thank you Paul Macoo and Tom Ogilvy for your advices. Unfortunately, i
do not have page breaks in my worksheet, and the worksheet/workbook is
saved when it is being created. i also noticed that it is not a memory
problem, as when i use the windows utility to check the performance of
the computer, it is the CPU processor that hits 100% all the time when
running the macro the second time (without restarting excel). It only
runs on normal CPU processor usage after I've restarted excel. But
even then, it will only run normally at acceptable speed the first time
after I've restarted it, not the consequent times.

Another problem i face with another macro that my colleague has written
is that excel crashes all the time, randomly, and before the macro is
being run. (It says "Excel.exe has generated a problem and will be
closed by Windows. You will need to restart the program.") It crash
the moment I do this: ToolsMacroMacro. This is suppose to bring up
the macro dialogue box for the me select which macro to run. This
happens absolutely random, and it only happen recently as this macro
grows in complexity and size. It's a poorly designed macro, with heaps
of loops that can run as many as 65,335 times, or scan to up 10,000
cells, with heaps of IF and Case statements everywhere. I know this
much, if the macro is poorly designed, it runs slowly and takes up a
lot of processing power and memory resources, but I am not sure why it
crash and kills excel.

Does anybody has any experience in this? Please advice.


---
Message posted from http://www.ExcelForum.com/