Calculate remaining time of macro with average

[miicker]

Hello everyone,

I have a macro which can run a long time. I have build in code to estimate the time remaining. I did it like this:

Dim StartTime As DoubleDim SecondsElapsed As Double
RemainingTestcases = Range("TotalTestCases").Value

'Code within while loop:
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

RemainingTestcases = RemainingTestcases - 1
MsgBox "Time remaining: " & RemainingTestcases * SecondsElapsed & " Seconds ", vbInformation

So what this does is reset the timer every loop, and than multiple the time the code ran that loop by the number of testcases remaining.
This works good and gives a clear image of estimated time remaining, but when I run for example 800 testcases, which all take about 2-3 seconds per testcase, the time remaining variates from 1600-2400 seconds. I would like to solve this 'problem' by using the average time, so after the first loop, the average time is for example 2,4 seconds (the time the first loop took), after the second loop (2 seconds) the average time is 2,2 seconds, after the third loop (3 seconds) the average time is 2,47 seconds. This way I'm hoping for smaller differences each time the remaining time is calculated.

I have no idea on how to do this, so i'm hoping someone here can help me.

 

[offthelip]

try this ( untested)

Dim StartTime As Double
Dim SecondsElapsed As Double
Dim totalsec As Long
Dim cnt As Long
Dim avesec As Long


RemainingTestcases = Range("TotalTestCases").Value
cnt = 0
totalsec = 0


'Code within while loop:
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
cnt = cnt + 1
totalsec = totalsec + SecondsElapsed
avesec = totalsec / cnt
RemainingTestcases = RemainingTestcases - 1
MsgBox "Time remaining: " & RemainingTestcases * avesec & " Seconds ", vbInformation

 

[miicker]

Thanks for the help, minutes before you posted this I thought of this solution, just testing it, and it works fine, thanks!