Progress Bar & automatic Calculation

The reason being  - I have no choice but the keep the calculation options as Automatic. My workbook is pretty dense with lots of macros. But when the calculations happens, it freezes the screen - so I am looking for a way to add visibility to users about this freezing, so to speak..

hi Rayne,

I believe in keeping calculation in automatic mode in spreadsheets (& only turning it off when intentionally runnning macros). This is because the calculation setting is an application level setting & can cause confusion & errors if files are mistakenly saved when excel is in manual calculation mode.

Which version of excel are you using?
Does your file not state "Calculate" at the bottom left of the screen, or "calculating ... x%" at the bottom right?

I think I've seen code that can provide a progress bar while calculations happen & I can try to find it for you if you want, but personally I think this "putting the ambulance at the bottom of the cliff". Instead of creating a progressbar are you open to fixing the cause of the slow calculations?
What is causing the calculation to take a long time?

Do you have any UDF's (User Defined Functions) in your code; are they volatile?
If so you may find some useful information on Charles Williams site eg http://www.decisionmodels.com/calcsecretsj.htm
There is a lot of useful information about improving calculation speed across his site.

hth
Rob

Thank you Rob & Royra,

I have replied here....


Which version of excel are you using?
The users will be using 2007 and 2010


Does your file not state "Calculate" at the bottom left of the screen, or "calculating ... x%" at the bottom right?
I have attached the image; I don’t see any calculating or any changing numbers where you mentioned.
In addition to macros, I have a lot of lookups across columns for multiple rows as well. When I drag the formulas down to copy the vlookups as well, all I see a “-|-” sign – the drag sign – and it stays at that drag sign for some time and then I see the formulas copied down.

I think I've seen code that can provide a progress bar while calculations happen & I can try to find it for you if you want,
Yes, that would be awesome – some indicator that tells me that excel is calculating and the status of it if possible. I am using some vba code – mostly public subs, maybe one function.

statusLook.png

Should I close this question if  [I think I've seen code that can provide a progress bar while calculations happen & I can try to find it for you if you want..] is not possible...let me know

Thank you

The calculation has already occurred when that event runs.
you could hide the status bar completely, or assign your own caption to it.

Thanks Rory,

I'm still a youngster on E-E, should the OP close this question?
Or should I/we just bend our comments around until we fix the underlying issue?

"No" is still a valid answer, so simply closing is not the right option, IMO. The question as asked has been answered - up to you and the asker if you want to try and rectify the slow calculation issue, and whether you do it here or as a new question.

Great, that makes sense to me.

Rayne, if you want any further help, let me know & I'll help if I can...

That's fine, at least you tried your best to guide me in this situation :)<br />Thank you Sirs...