Excel Slow down and Memory hog!!

Hi guys, I've built an excel file that eats memory until it crashes. I have set calculations to manual, but the problem persists so I'm left with it being my macros. Is there any way to test my vba to see if the code is causing the memory use. It's very strange I can watch task manager and see the memory usage climb as I click from one tab to the next until it crashes. If I stop ... the excel processes keeps the memory and doesn't release it.

Any help is appreciated!

Hi cp

You don't mention if you are using lots of objects (and their associated Set Object=something), but if you are, and are not releasing them, you may be running into memory problems.

Also, if you have heavily recursive structures (recursive procedures or functions) these can sometimes create problems.

Easiest to get help would be to post a copy of your spreadsheet..although I don't know how many people would like to deliberately slow their machines down that much

HTH

Tony

Thanks for the responses guys!!

I have attached a stripped down copy of the file. I deleted a bunch of tabs, but the original will only slow down excel if you click from tab to tab, but it doesn't slow down the rest of the computer because of the 2gb cap on 32 bit applications.
Time Card Template Take 2.xlsm


Tony -- I'm not using any objects in my VBA, I have some variables, but I set them to nothing at the end. I do have a recursive custom function in Macro 2, but in my previous tests I had eliminated this and nothing

Ford -- I had eliminated formulas as a contributor by setting calculations to manual. After I did this the problem still persisted.

Unless I'm missing something.

Caleb

Hi Caleb

The spreadsheet you sent seems to use an awful lot of hyperlinks to get to a simple cell on a different sheet. Is there some underlying reason for this?

Tony

Oh gosh!! I forgot about the hyper links. I suppose the links aren't really necessary. I will try to remove them and see if that helps. The idea was to make it easy for the manager to view an overview and drill down if necessary, but it really only avoids a couple of clicks. Although I have other files that have the hyperlinks that work just fine.

Removed the hyperlinks and no help

Thank you! Thank you! Thank you!

Izandol -- do I call this sub or just run it periodically? Perhaps on activate a new worksheet?

Wow!!! that worked like a charm! Would you indulge a nubie and explain what happened.

I have a similar problem with thousands and thousands of hidden shapes in my sheet. Deleting all shapes isn't an option as i do need many shapes to be there.
Is there a non-vba way to identify hidden shapes and delete them manually (en-masse if necessary).

Regards

Ken

Hi,

when an entire row is deleted, the shape on this row isn't deleted but remains as a ghost shape :
its dimensions are set to zero !

So to delete ghost shape, just test its height is equal to zero !