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

--
Regards,
Tom Ogilvy


"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:72FA5579-52A9-4E64-AE87-868D96B2AE53@microsoft.com...
> 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
>

[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" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:72FA5579-52A9-4E64-AE87-868D96B2AE53@microsoft.com...
> 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
>

[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

"Peter T" wrote:

> 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" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:72FA5579-52A9-4E64-AE87-868D96B2AE53@microsoft.com...
> > 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
> >
>
>
>

[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


"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
>