Avatar of Rayne
Rayne
Flag for United States of America asked on

Progress Bar & automatic Calculation

Hello World of Experts,

Now I know what is automatic calculation from here:
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27446549.html


Now I know how to do a progress bar from here:
https://www.experts-exchange.com/Programming/Languages/Q_21397848.html


My question is: is there a way to set it up such that whenever the automatic calculationsa re happening, it displays the progress bar? Can anyone show that with code? Probably a worsheet change vent or something similar - I have no idea.

Thank You
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Rayne

8/22/2022 - Mon
Rayne

ASKER
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..
Rob Brockett

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
SOLUTION
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rayne

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rayne

ASKER
Rayne

ASKER
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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rory Archibald

The calculation has already occurred when that event runs.
you could hide the status bar completely, or assign your own caption to it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob Brockett

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?
Rory Archibald

"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.
Rob Brockett

Great, that makes sense to me.

Rayne, if you want any further help, let me know & I'll help if I can...
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rayne

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