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


"ExcelMonkey" wrote:

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