Progress Bar During Sheet Recalculation

[Mumfy]

Hi all

I need to put a progress bar on a form to indicate the progress of a worksheet recalculation. I have a worksheet with 12 columns and up to 10,000 rows containing formulas that are recalculated twice per run. I use the .ENABLECALCULATION=TRUE to trigger the recalculation. During testing with 1,600 rows the recalculation takes approximately 15 seconds, so with 10,000 rows it is going to take much longer. I tried setting iteration and precision to suitable minimums but this had no significant impact on the recalculation time. The recalculation is not contained within a loop so I cannot use the usual “label” progress bar method as I have no access to any code while the recalculation is running. Users will need visual feedback to indicate the recalculation progress to see that the program is not in an indefinite loop.
Is there any way to overcome this? I am using Excel 97.

Thanks in anticipation for any ideas you may have to assist me.

 

[Chattin]

Try these (amongst others)

http://www.cpearson.com/excel/Progress.htm

http://j-walk.com/ss/excel/tips/tip34.htm

HTH

 

[Mumfy]

Hi Chattin
Thanks for your response and suggestions.

I have visited the links you posted, however, I don't believe they fit my need. Both solutions require you to know how far through the process you are AND have access to progress bar code from within a loop, unless I misunderstand.

When I execute the enablecalculation=true statement in my code I lose control of my code for 15 seconds (during test) until I regain control after Excel has finished the sheet recalculation. During this time I need the progress bar to be activated and running. When I get control back from Excel there is no longer a need for a progress bar.

Maybe I have not clearly understood the solutions, could you please clarify where I am losing it.

Regards
Mumfy.

 

[xlbo]

does the calculation not give a
"Calculating 10%" message in the status bar at the bottom ???

If not, this won't give the progress but will let users know that something is going on

with application
.statusbar = "Calculating Formuale - please wait"
.enablecalculation = true
.statusbar = "Calculation Complete"
.statusbar = false
end with

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Chattin]

Another example:

http://www.erlandsendata.no/english/vba/display/statusbarmsg.php

Sorry but I don't know where your error is!

 

[Mumfy]

Hi xlbo

No, there is no "calculating %" message in the status bar at the bottom.

Thanks for the suggestion of displaying messages in the status bar. I was looking for something more visible but as you say it will let users at least know something is going on.

Regards
Mumfy

 

[JohnAcc]

Hi There:

Have you thought about putting in a macro that runs from a timer.

i.e before enablecalculation = true you could call another sub routine that displays a userform with a cumstom built status bar that updates every x seconds.

Might work?

Rgds, John

 

[Mumfy]

Hi JohnAcc

Thanks for the suggestion. I will look into it as it may be another way of addressing the issue.

Regards
Mumfy.