Hello,

I have written a macro which performs some lengthy calculations. I run the
macro then tell excel to calculate. Running the macro takes about 10 seconds,
calculating takes up to 1 minute. I want to display a progress bar to show
the calculation progress so the user knows the program hasn't crashed.
Obviously this is displayed in the bottom left hand corner but is it possible
to display it more obviously?

Many Thanks

"Neil Hopkinson" schrieb im
Newsbeitrag ...
Hello,

I have written a macro which performs some lengthy calculations. I run the
macro then tell excel to calculate. Running the macro takes about 10
seconds,
calculating takes up to 1 minute. I want to display a progress bar to show
the calculation progress so the user knows the program hasn't crashed.
Obviously this is displayed in the bottom left hand corner but is it
possible
to display it more obviously?

Many Thanks

Hi Neil,

google is your friend, "excel vba progress bar" gets you started, e.g.
http://www.cpearson.com/excel/Progress.htm.

cheers,
Stephan

This is great, thanks! However I can write the progressbar but the issue is
how to pick up the percentage that has been calculated once automatic
calculation is switched back on. Any ideas on this?

Thanks


"Dr. Stephan Kassanke" wrote:


"Neil Hopkinson" schrieb im
Newsbeitrag ...
Hello,

I have written a macro which performs some lengthy calculations. I run the
macro then tell excel to calculate. Running the macro takes about 10
seconds,
calculating takes up to 1 minute. I want to display a progress bar to show
the calculation progress so the user knows the program hasn't crashed.
Obviously this is displayed in the bottom left hand corner but is it
possible
to display it more obviously?

Many Thanks

Hi Neil,

google is your friend, "excel vba progress bar" gets you started, e.g.
http://www.cpearson.com/excel/Progress.htm.

cheers,
Stephan

Neil Hopkinson wrote:
Hello,

I have written a macro which performs some lengthy calculations. I run the
macro then tell excel to calculate. Running the macro takes about 10 seconds,
calculating takes up to 1 minute. I want to display a progress bar to show
the calculation progress so the user knows the program hasn't crashed.
Obviously this is displayed in the bottom left hand corner but is it possible
to display it more obviously?

Many Thanks


The low-tech solution is to just write a status message out to a visible, unused
cell on your worksheet and update it each pass through the loop. Make it in
color if you wish to be more eye-catching. There are various reasons this won't
work for all cases, but when this approach does fit your problem it's dirt
simple to do.

Bill

Bill,

Chip Pearson has a progress bar at www.cpearson.com
--
Earl Kiosterud
www.smokeylake.com

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Neil Hopkinson wrote:
Hello,

I have written a macro which performs some lengthy calculations. I run
the macro then tell excel to calculate. Running the macro takes about 10
seconds, calculating takes up to 1 minute. I want to display a progress
bar to show the calculation progress so the user knows the program hasn't
crashed. Obviously this is displayed in the bottom left hand corner but
is it possible to display it more obviously?

Many Thanks


The low-tech solution is to just write a status message out to a visible,
unused cell on your worksheet and update it each pass through the loop.
Make it in color if you wish to be more eye-catching. There are various
reasons this won't work for all cases, but when this approach does fit
your problem it's dirt simple to do.

Bill

Earl Kiosterud wrote:
Bill,

Chip Pearson has a progress bar at www.cpearson.com
--
Earl Kiosterud
www.smokeylake.com

I understand, and I have played with it. It's great. It also requires more of
the user to know how to load it, declare variables, etc. If you're up to that
it's great. Some users here aren't always at that level though and a dirt
simple approach is occasionally useful as an alternative -- IMHO. Stephan had
already mentioned Chip's progress bar approach so I just thought I'd point out a
different one.

Bill

Thanks for this info. Very useful. One further question though. Do you know
if it is possible to pick up the percentage that has been calculated once
autocalculate is switched back on?

Thanks

"Bill Martin -- (Remove NOSPAM from addre" wrote:

Earl Kiosterud wrote:
Bill,

Chip Pearson has a progress bar at www.cpearson.com
--
Earl Kiosterud
www.smokeylake.com

I understand, and I have played with it. It's great. It also requires more of
the user to know how to load it, declare variables, etc. If you're up to that
it's great. Some users here aren't always at that level though and a dirt
simple approach is occasionally useful as an alternative -- IMHO. Stephan had
already mentioned Chip's progress bar approach so I just thought I'd point out a
different one.

Bill

Neil Hopkinson wrote:
Thanks for this info. Very useful. One further question though. Do you know
if it is possible to pick up the percentage that has been calculated once
autocalculate is switched back on?

I don't know what you're doing specifically, but in general the answer is yes.
I've often written macros where I turn off autocalc, then do a bunch of macro
manipulation, then briefly turn the autocalc back on then off again and use some
spreadsheet answer back into the macro again.

Specifically where I've written Monte Carlo analysis so at the end of a loop
autocalc is briefly turned on to get the result, compare it with the last pass,
and then go into the next analysis pass. After each pass I can see a bunch of
things the macro has updated on the spreadsheet get visibly updated that way and
tell how much progress the Monte Carlo is making. Whether to let it continue to
run a few more hours or to shut it down and tweak the algorithm somehow.

Bill

Bill,

Oops. My bad (whatever exactly that means <g). I thought I was replying
to the OP, which doesn't show up in my newsreader, OE, as happens sometimes.

While I'm here, since I can't reply to the OP anyway, here's a simple little
progress bar I made years ago. I admitted even then in my comments that
it's dorky. It could be put into text box on the worksheet or UserForm or a
cell (where it works best with a non-proportional font like Courier), or the
Status bar, etc.

WorkToDo = 20 ' 20 units of work - also sets length of char string of
progress bar
WorkDone = 5 ' update as routine runs, 0 to WorkToDo

ProgressIndicator = String$(WorkDone, "+") & String$(WorkToDo - WorkDone,
"-")

Range("PB") = ProgressIndicator
ActiveSheet.TextBox1.Text = ProgressIndicator : DoEvents
Application.StatusBar = ProgressIndicator

Maybe someone can use it.
--
Earl Kiosterud
www.smokeylake.com

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Earl Kiosterud wrote:
Bill,

Chip Pearson has a progress bar at www.cpearson.com
--
Earl Kiosterud
www.smokeylake.com

I understand, and I have played with it. It's great. It also requires
more of the user to know how to load it, declare variables, etc. If
you're up to that it's great. Some users here aren't always at that level
though and a dirt simple approach is occasionally useful as an
alternative -- IMHO. Stephan had already mentioned Chip's progress bar
approach so I just thought I'd point out a different one.

Bill