Status Bar with ETA calculation

[ExcelMonkey]

Can somebody tell me why this is not working. Iam trying to incorporate this
ETA calc into a statusbar. When I say ETA I mean I am tyring to calculate
how much time is left in running the loop. The status keeps displaying the
same number. IT should be getting smaller over time and eventually hit 0.

Thanks

Sub Main()
Dim Start As Date
Dim CurIteration As Double
Dim X As Double

Start = Now()

X = 100000

For CurIteration = 1 To X
Application.StatusBar = ETA(Start, X, CurIteration)
Next

End Sub


Function ETA(StartTime As Date, NumOfIterations As Double, CurrentIteration
As Double) As String
ETA = Format((Now() - StartTime) / (CurrentIteration / NumOfIterations)
+ StartTime, "h:mm:ss")
End Function

 

[Tom Ogilvy]

For CurIteration = 1 To X
Application.StatusBar = ETA(Start, X, CurIteration)
doevents
Next

 

[Peter T]

Unless I'm missing something this will always display the ETA, which over
time gets more 'accurate' until finally displaying the done time. Why do you
expect it to get "smaller over time and eventually hit 0" ?

A progress meter of this sort, whether it displays an ETA or %age can
dramatically increase the overall time of the main routine. Suggest only
calculate periodically and only update, including making the Format string,
if not same as previous update. Also I wouldn't use Now() except to initiate
the time, then use an API such as Gettickcount.

Regards,
Peter T

 

[ExcelMonkey]

Peter you are bang on the money. I realised after posting it that I asked
the wrong question. What I really want is it to calculate home much time is
left before the routine is finished. As written it estimates that that
actual finished time is.

EM

 

[ExcelMonkey]

Here is how to calc time left to completion.

Sub Main()
Dim Start As Date
Dim CurIteration As Double
Dim X As Double

Start = Now()

X = 1000000

For CurIteration = 1 To X
Application.StatusBar = ETA(Start, X, CurIteration)
Next

End Sub


Function ETA(StartTime As Date, NumOfIterations As Double, CurrentIteration
As Double) As String
Dim RightNow As Date
RightNow = Now()
ETA = Format(((RightNow - StartTime) / (CurrentIteration /
NumOfIterations) + StartTime) - RightNow, "h:mm:ss")
End Function