How to determine the progress of a macro?

DamageZ

New Member
Joined
Aug 20, 2010
Messages
25
Hi All,

I currently am using a very lengthy macro that opens and updates 30 other spreadsheets. It currently takes between 9 and 15 minutes depending on what is doing.

I have a very basic screen update built into it that shades a progressive cell in black to show its progress.

I have looked at many other forums and web pages depicting progress bars and showing how to update the status bar, however, I haven't been able to figure out how to get excel to guesstimate how much longer it will take to finish running the macro. Is this even possible?

Thanks to everyone in advance.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
All you need to do is log the time you start the macro. As you loop through the sheets subtract the time started from the time now, Divide the result by the number of sheets you have processed so far then multiple the result by the total number of sheets you are processing.

The result is how long it should take all up. To calculate the remaining time simply subtract the time taken so far from this figure.
 

DamageZ

New Member
Joined
Aug 20, 2010
Messages
25
All you need to do is log the time you start the macro. As you loop through the sheets subtract the time started from the time now, Divide the result by the number of sheets you have processed so far then multiple the result by the total number of sheets you are processing.

The result is how long it should take all up. To calculate the remaining time simply subtract the time taken so far from this figure.
Thanks for the quick reply :)

I had the same idea, but some of the workbooks are larger than the others. So your suggestion is along the same lines as my current solution. Which only really gives me a guide as to how many workbooks have been processed so far.

I was hoping that VB some how estimated the time it would need to complete the macro.
 

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Unfortunately no because it doesn't know how long something it hasn't done will take. I have used the method i mentioned before but ended up canning the whole thing and going back to a simple "XX% complete" because of the potential to be so inaccurate for the reasons you mentioned.
 

DamageZ

New Member
Joined
Aug 20, 2010
Messages
25
Bugger! I thought that was going to be the case. I was hoping against all odds that there was some sort of super brain built into it.

Thansk for your help BH :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

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