How can I reduce memory and CPU% used in Excel 2007?

I have tried the SaveAs method in VB between Excel function calls, where I save the Excel file under another name, then kill the Excel process, and then re-instantiate another Excel process for the next function.  I don't just use a Workbook.Quit command, I actually terminate the Excel application in the Task Manager so that the memory is freed up (I think).

However, when I re-instantiate Excel app based on the previously saved file, it starts up around 52,000K, then when I start the workbook, it jumps up another 79,000K.  Is there anything I can do to bring down both the memory used and the CPU % used?

Also, some of the Excel functions are extremely long, and there is no way I can break them up.  By the time the function is finished, the memory and CPU % used almost crash the pc.  I can only use the SaveAs method in VB outside the Excel function.  Within the Excel function I can't do anything to kill Excel and re-instantiate, so the memory used just keeps growing until the long loop is finished.

Any suggestions would be appreciated.
IndyJoe1958Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello IndyJoe,

there's probably no easy and simple answer to your question. It may actually take a lot of work to fix it.

Here is an excellent article by Charles Williams about Improving Performance in Excel 2007.
http://msdn.microsoft.com/en-us/library/aa730921.aspx

It touches on many aspects of Excel, from formulas to used ranges to VBA.

I'm sure you'll find something worth considering in there.

cheers, teylyn
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.