Excel high CPU usage and increasing memory leads to crash

brenton

New Member
Joined
Aug 24, 2011
Messages
33
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
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.
 

brenton

New Member
Joined
Aug 24, 2011
Messages
33
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

Well-known Member
Joined
Dec 3, 2007
Messages
1,100
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,780
Members
416,880
Latest member
kyleswanson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top