Long run time Macro's

[gheyman]

I have a pretty big program that has some sizable Macros that take some time to process. When they are running (in the background) some of my users get impatient and think the programs not doing anything, then they start clicking all over causing errors.

On a separate note, when some of these lengthy macro’s run, our screens go white and then return to normal after the macros/VB code completes.

Is there a way to show the macro is running? I read something about using the graphics, something like you see when you are saving, but the code they used determined percentage complete as it processed. The example used filling a page with random numbers. So it was kind of easy to figure percentage complete. I can’t do that with my macros. I can’t figure percentage complete on these macros. They are clearing cells, copying and pasting, putting values in TextBoxes….

Suggestions?

Thanks

 

[Norie]

If you search for progress bars or status bar you should find plenty of examples and/or links on the board.

As well as that if your code is taking what seems like too long it might be worth posting it so we can maybe give you some pointers.

One of which is to use this.

Application.ScreenUpdating = False
' the code here
Application.ScreenUpdating = True

Another would be to turn off calculation.

 

[Lewiy]

For lengthy macros, I tend (after having tried to speed them up first!!) to use a custom built progress bar. You can control the text in the status bar in the bottom left corner but I find that no-one notices this anyway!! So here is my method:

In its simplest form, this comprises of a UserForm from which I fire the macros under it’s Activate Event. On the UserForm is a Label which I usually set to Width 400 and make it’s Caption blank and it’s BackColor yellow.

You then need to add bit’s of code to control the width of the Label. For example, if I had the following loop (simple for demonstration purposes):

A = 50
For B = 1 to A
       C = B*A
Next B

I would modify this to:

UserForm1.Label1.Width = 0
DoEvents
A = 50
For B = 1 to A
       C = B*A
       UserForm1.Label1.Width = (B/A) * 400
       DoEvents
Next B

This should make Label1 increase in width until it finally reaches width 400 on the final iteration of the loop.

You can then embellish a little by adding another Label on the UserForm and change it’s caption to display the action currently being undertaken for each loop in your macro.

Hope that makes sense and gives you some ideas!!

 

[gheyman]

Thanks!! That works