How to determine the progress of a macro?

[DamageZ]

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.

 

[Blade Hunter]

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]

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]

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]

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