Excel high CPU usage and increasing memory leads to crash

[brenton]

I have a workbook with a lot of userforms, modules, OLE objects. 6.4 Mb file size.

I have successfully used the same template over and over. Now i have one template that shows calculate in the status bar every time a cell is selected. Nothing is even changed. When i update a cell value, cpu usage goes up to 50 and memory spikes to 120,000. Then when i open a userform, i cant even type text or anything into it.

I'm thinking the dependency limit was reached. But have been unable to solve that issue.

When i open the workbook and dont "enable content" it calculates just fine.
Also if i open the workbook and select manual calculation, it operates just fine.

My question is, if its a dependency issue wouldnt it still take forever to calculate even if macros were disabled? Or do macros count against the memory available to calculations?

I cannot provide the workbook unfortunatley. Also setting calculations to manual is not a viable solution since it would be bad if users forgot to update them.

 

[StephenCrump]

Q1: What version of Excel are you running?

 

[Cool blue]

Check for circular references in your code, for example if you have a class module for generating or managing other class modules which in turn wrap controls and then reference back to the parent class.
In this case, the VBA garbage collection will fail to release memory.

 

[PaddyD]

place to search for info / guidance:

decisionmodels.com

 

[brenton]

Thanks guys- tried all your suggestions-
then it hit me to delete all the modules and forms and it also worked- so no change events firing. It also explains why when i put the pause button on all subs that they were never triggered.
I deleted all the userforms one by one.... there were a bunch of them
turns out there were two textboxes on separate userforms with the same ctrl source cell. This was causing the glitchy behavior and calculate to appear every time i selected anything.

 

[jkpieterse]

This is one of the reasons I NEVER EVER use the control sources of Userform controls. Add the needed data at runtime using an init routine in the userform. That way you can use range names to point to the cells of interest and use the range names in code. This prevents rewrites of code should your spreadsheet layout change because Excel will do that chore for you.