Long run time Macro's

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,940
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
Application.ScreenUpdating = False
' the code here
Application.ScreenUpdating = True
Another would be to turn off calculation.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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):
Code:
A = 50
For B = 1 to A
       C = B*A
Next B
I would modify this to:
Code:
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,940
Office Version
  1. 365
Platform
  1. Windows
Thanks!! That works
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,520
Members
425,479
Latest member
Neerajcool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top