Progress Bar & automatic Calculation

Rayne
Rayne used Ask the Experts™
on
Hello World of Experts,

Now I know what is automatic calculation from here:
http://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:
http://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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2011
Commented:
you have no way of determining or intercepting Excel's calculation or its percentage, so I agree with Rob.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Author

Commented:

Author

Commented:
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
hi Rayne,

From memory, the code I'm thinking of was not a "real" progress bar it was more of a "splash screen" with random movement rather than a percentage indicator. So it probably isn't worth me finding it.

It may be best to close this question but here are some suggestions that could help speed up your calculation:
1) If you have any vlookup's that use the below syntax:
=if(isna(vlookup(xyz)),"",vlookup(xyz))
then change them to use this syntax which works in excel 2007 and newer:
=iferror(vlookup(xyz),"")

2) If your vlookups refer to full columns, I recommend changing them to only refer to the range which is populated with data by using Dynamic Named Ranges (DNR). Here is a link to Debra Dalgleish's page which explains DNR: http://www.contextures.com/xlNames01.html
The above link explains using Offset & Counta, however I prefer to use an Index & Counta approach which is discussed on: http://www.contextures.com/xlNames03.html

3) If your vlookups refer to closed files, open the other files first before drag-copying the formulae.

4) How large is your file?

5) Why do the users need to drag-copy vlookups?
Can the file be modified to prevent the need for users to do this?
This action could be incorporated into code, which turns the calculation off, places the formulae in the right files, & then turns the calculation back on.

6) I'm clutching at straws with these:
- Does any of your code use "application.stausbar = false"?
- Does your function use "application.volatile"?
- Is any of your vba "event code" which could be firing repeatedly?

Rory,
I know we can't identify the percentage complete but is there any chance that leveraging the "Workbook_SheetCalculate" event could allow for a message to be placed in the statusbar?
Is there a setting that could prevent the OP's file from showing the "calculate" messages?


Rob
Most Valuable Expert 2011
Top Expert 2011

Commented:
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?
Most Valuable Expert 2011
Top Expert 2011

Commented:
"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...

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial