Progress Bar for a slow macro

[High Plains Grifter]

Hello People,
I have a macro which takes a looong time to execute - opening 20+ files and copying information from each into one master file. I have used the

application.screenupdates = false

command to stop the screen jumping all over the place while this happens, which is a greaet improvement, but I have recently had a couple of users breaking the whole thing by thinking their computer had frozen up while the macro was running.

Is there any way in which a Msgbox might be made to appear which would show the number of files that have been sampled out of the total to sample? Even better, if this could display a status bar based on the percentage of the task completed?

Given that the core of the profgram is a For...To loop, counting up to the total number of workbooks to sample, the counter would not be a problem, but how do I make it remain in place without requiring input from users? How do I make it disappear when the program completes, and yet remain if other message boxes appear during the program?

I was thinking something along the lines of this for the box (this is extracted from the macro, missing out most of the code)

Dim Prog As Long
Dim Progress As String
 
For i = 0 To (t - 1)
    Prog = i / t * 10
    Sheets("Collate data").Activate
    Range("I1").Value = Prog
    Progress = Range("H1").Value
 
'all the gubbins of the actual macro - too long to include
 
     Msg = ("Progress:" & Progress)
     Ans = MsgBox(Msg)
Next i

The formula in H1 is Rept("|",I1). This works in a way... But the msg box causes the program to pause, and waits for a response from the user - I would like it to stay visible while the program runs in the background, and update the bar progress by an increment of i/t each time the For..Next loop restarts. Also, the bar is rudimentary to say the least - I could work on that if I knew how to make the Msgbox behave.

The box could disappear or change to Finished! when the macro completes. Is this possible? If someone were to show a way to make the Msgbox stay visible and not interfere with the program, I could probably work out the rest myself.

Any help gratefully received, and thanks for reading!

 

[Jon von der Heyden]

Hi

Note that a progress bar may actually slow the code up even more. Here's an article on how to.

Further more you can stop your code being interrupted with:

Application.EnableCancelKey = xlDisabled

Also, depending on what your code actually does turning it to manual calculation may speed it up, and force calculation when it is absolutely necessary:

Application.Calculation = xlManual

 

[jgrilly]

Rather than using fancy progress bars, I prefer to post updates in the Status Bar (lower left corner). I might put the following in the For...Next loop:

Application.Statusbar = "Analyzing File " & i & " / " & (t-1)
DoEvents

 

[High Plains Grifter]

Two excellent replies, I am sure - as always with these things the answers have opened up new ideas and possibilities for me more than the desperate scrabbling for an answer on my own ever could - lots of new things here for me to investigate, and I am sure that I will find a happy ending somewhere in the (currently) muddlesome world of code. Thank you people - I love writing on here even if only for the random directions it take my learning!

I will experiment with glee. Great tip about the status bar as well!

And then I'll work out how to speed that program up... manual calculation? that needs looking up, and maybe by the time I'm done I'll want to rewrite the whole thing anyway.

 

[pistonbroke]

J Grilly - what an awesome bit of code for the status bar. Beats the progress bar hands down. Thankyou for that excellent tip !

 

[Jon von der Heyden]

Statusbar is my preference too these days.

Just remember to reset the StatusBar at the end of your procedure:

Application.Statusbar = False

 

[Sandcastor]

Jon von der Heyden and jgrilly, the statusbar updating is excellent. Many of the wingnuts I'm developing for don't trust computers, and think it's broken when it's thinking (seriously, 4 seconds = panic??). Of course the only reasonable thing to display down there is "Reticulating Splines". Maybe that's just me!

I'm alot like High Plains Grifter, I'm new to VBA, and each time I look up a solution, I find five more things to mess with. This is great.

Thanks!