Macro Complition CountDown

[HotNumbers2001]

Is it possible to have a countdown clock for Macro once it is in process? I have a very comlex matrixs and when some macros are run it can take up 10 minutes to complete them. Is it possible to write a code into the macro that will tell you approx. what the remaing time is?

 

[Damon Ostrander]

Hi Hot,

In general there is no way to do this because the run time is determined by data values that may or may not be under VBA control. For example, say you have a macro that processes every row containing data on a worksheet. The macro takes twice as long to process 10000 rows as it takes to process 5000 rows. But if it determines the number of rows to process at run time how can it possibly know in advance how long it will take?

All that being said, if your macro is not data-dependent so that the run time is very predictable, or even constant, then a countdown timer is very feasible because you can base the estimated run time on a previous run's run time. You can then use the VBA Timer function to calculate the time periodically during the run and subtract it from the estimated end time to get the time remaining.

 

[HotNumbers2001]

thanks for the quick reply. i was affraid of that. thank you for your answer.

 

[stevebausch]

If the process involves a UserForm (or it could...), you could add a ProgressBar.

If the process is now being done with a form, your job will be easy: just add the control, ensure the correct reference is added, and have your code increment .Value at points in the process.

If you aren't using a form, then the form you put up (in the course of your process) would be assigning .Value in the _Activate routine. .Value could reference a Public variable (Integer), or perhaps Property Get/Set could be used to advantage.

Don't ask me about Property Get/Set, I haven't attempted to comprehend them

The _Activate routine would loop through assigning the .Value, also testing if .Value equaled .Max. Including a delay might be nice.

If it did, then
Unload Me
Exit Sub


Regarding the difficulty of accurately predicting process time, just consider Microsoft and Windows Update downloads; the estimated time is always a bit suspect.
This message was edited by stevebausch on 2002-08-22 14:01