PDA

View Full Version : timer, counting down



Ago
02-23-2008, 04:31 PM
i just started a new project just for fun.
my goal is to make a "progressbar" that counts down the estimated time left while its running a macro.

am i stupid or whats wrong with this code?


Sub test()
Dim time
Dim t2
time = 5
While time > 0
t2 = Timer
time = time + (t2 - Timer)
Range("B1").Value = time
Wend
End Sub


Timer is a built in counter that counts up, to reverse it i used a OldTimerValue (t2) - CurrentTimerValue (Timer), that should result in a negative number of seconds.

so if time is the amount of seconds to count down it should be:
time = time + (OldTimerValue - CurrentTimerValue)
just as in the code?

the problem is the code takes ages to finnish.
any ideas why?

tstav
02-23-2008, 10:37 PM
Hi Ago,
just move the <t2 = Timer> out of the While...Wend loop, otherwise
(t2 - Timer) will always be zero and you will get an infinite loop.


Sub test()
Dim time as Single
Dim t2 as Single
time = 5
t2 = Timer
While time > 0
time = time + (t2 - Timer)
Range("B1").Value = time
Wend
End Sub

Ago
02-24-2008, 01:47 AM
thanks for your reply!

but this doesnt seem to work.
if i put time to 20 it still takes less than a second to run the script,
and i think that is because t2 is out of the whileloop.

t2 and TImer is used as the relative timediffrence.
so if t2 is outside of the loop, t2 will stay on let say 100 while timer keeps growing.
so the first time you run the loop its correct,
t2= 100
Timer = 101
time= -1

next loop
t2 = 100
Timer = 105
time = -6 (keeps the -1 since last loop and now adds -5)

the result in the second loop should be -5.
if you run one more loop the diffrence will be -6 (the result of the second loop)

tstav
02-24-2008, 02:19 AM
The following works.
I have added a t3 variable which fixes the problem.

Sub CountDown()
Dim time, t2, t3 As Single
time = 5
t2 = Timer
While time > 0
t3 = Timer
time = time + (t2 - t3)
t2 = t3
Range("B1").Value = CInt(time)
Wend
End Sub

Bob Phillips
02-24-2008, 02:21 AM
I don't think a variable called time is a good ide, but why not just use a simple Ontime ticker



Public Sub Test()
TickDown Start:=True
End Sub

Public Sub TickDown(Optional Start As Boolean)
Const t2 As Single = 5
Static mytime As Double
If Start Then mytime = 0
If mytime <= t2 Then
Range("B1").Value = t2 - mytime
mytime = mytime + 1
Debug.Print mytime
Application.OnTime Now + TimeSerial(0, 0, 1), "TickDown"
End If

End Sub

Ago
02-24-2008, 09:12 AM
LOL, i now understand why you shouldnt have a variable called time.
if anyone used the code above please check your windowsclock :-)

had some problems following your code xld, didnt really understand what it did.
i saw that the result was what i wanted but i dont understand how. lol.

but tstav, that worked perfect!
thanks alot!

the code is done, and it works. might no be a sexy code but remember it works :-)


Sub Estimated_time()
Dim StartValue 'Your startvalue in the loop, change it to your variable
Dim StopValue 'Your stopvalue in the loop, change it to your variable
Dim percent5, percent10, percent20, percent50, percent80, percent95
Dim tStart, t1, t2, t3, timeleft
Dim mins, secs
Dim MyTimer 'Dummy variable - remove it when implemented in your code

StopValue = 600
StartValue = 1

'This is only to make the code easier to read in the loop
'If you need more or less calculationpoints you can just add or delete them
percent5 = (StopValue - StartValue) / 20
percent10 = (StopValue - StartValue) / 10
percent20 = (StopValue - StartValue) / 5
percent50 = (StopValue - StartValue) / 2
percent80 = (StopValue - StartValue) / 5 * 4
percent95 = (StopValue - StartValue) / 100 * 95

tStart = Timer
t1 = tStart

For StartValue = 1 To StopValue

'If statements below calculates/recalculates the time left of the loop
'If you need more or less calculationpoins you can just add or delete them
'The countdown starts when the script has reached 5%
If StartValue = CInt(percent5) Then
Start = 1 ' starts the countdown
t2 = Timer
timeleft = (t2 - tStart) / 5 * 95
End If
If StartValue = CInt(percent10) Then
t2 = Timer
timeleft = (t2 - tStart) / 10 * 90
End If
If StartValue = CInt(percent20) Then
t2 = Timer
timeleft = (t2 - tStart) / 20 * 80
End If
If StartValue = CInt(percent50) Then
t2 = Timer
timeleft = (t2 - tStart)
End If
If StartValue = CInt(percent80) Then
t2 = Timer
timeleft = (t2 - tStart) / 80 * 20
End If
If StartValue = CInt(percent95) Then
t2 = Timer
timeleft = (t2 - tStart) / 95 * 5
End If

If Start = 1 Then
t3 = Timer
timeleft = timeleft + (t1 - t3)
t1 = t3
End If


'devides the timeleft to minutes and seconds
mins = Fix(timeleft / 60)
secs = CInt(timeleft) - (mins * 60)
If secs < 10 Then
secs = "0" & secs
End If

'Prints the % done and estimated time left of the loop
Application.StatusBar = "Progress: " & Format(StartValue / StopValue, "Percent") & " Estimated time left " & mins & ":" & secs


'Dummyloop below to waste time, replace it with your code
MyTimer = Timer
Do
Loop While Timer - MyTimer < 0.03


Next StartValue
Application.StatusBar = False 'removes the statusbar
End Sub


i will post it in Kbase if you guys dont find anything wrong with it.

thanks alot for the help with the countdown!