Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar with ETA calculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar with ETA calculation
For CurIteration = 1 To X
Application.StatusBar = ETA(Start, X, CurIteration) doevents Next -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar with ETA calculation
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar with ETA calculation
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Status Bar with ETA calculation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning calculation status | Excel Worksheet Functions | |||
reuest formula for auto update status & status date | Excel Worksheet Functions | |||
Calculation appear on status bar when in autocalc mode | Excel Discussion (Misc queries) | |||
STATUS BAR LENGHT OF CALCULATION BOX | Excel Discussion (Misc queries) | |||
Check calculation status | Excel Programming |