VBA countdown timer

[wasntme]

Hello,

I want to make a countdown timer with counting to a date in the future in format "dd hh:mm:ss" or similar. F.e. how many days, hours, minutes and seconds left till "21/10/2019 15:00:00". I have newly found the timer function, but can not make it count backwards.

All help and ideas appreciated. Thanks.

 

[FormR]

Hi, maybe you could consider using formulas:

Excel 2013/2016
	A	B	C	D	E	F
1	Start	End	Days	Hours	Minutes	Seconds
2	29/08/2019 09:38:22	21/10/2019 15:00:00	53	5	21	38
Sheet1

Cell Formulas
Range	Formula
A2	=NOW()
C2	=INT(B2-A2)
D2	=HOUR(MOD(B2-A2,1))
E2	=MINUTE(MOD(B2-A2,1))
F2	=SECOND(MOD(B2-A2,1))

 

[wasntme]

Hi FormR,
Thank you for your suggestion, it was the first thing i tried. The thing is i am trying to make this as active clock timer, but the formula for current time does not refresh despite formula calculation option is set to automatic.
Have an idea how to sort this out, maybe even force calculations with VBA?
Regards.

 

[FormR]

Hi, you can force Excel to recalculate by pressing F9.

I suppose you could use On Timer in VBA to schedule a macro to do the re-calculation periodically.

 

[Logit]

.

Option Explicit


Public bTimerOn As Boolean


Sub ToggleTimer() ' This Sub will switch the automatic timer on if it was off before, and vice-versa
bTimerOn = Not bTimerOn


Refresh ' It calls the Refresh Sub
End Sub


Sub Refresh() ' This Sub calculates the sheet and tells Excel to call it again after 1 second
Application.Calculate


If bTimerOn Then ' If bTimerOn is True, start refreshing automatically
Application.OnTime Now + TimeValue("00:00:01"), "Refresh" ' The intervall is set to 1 second but can be changed variably
End If
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/Do210jMhUDUD4xutTmXZHLpjtVo1ATXebd5aHmuo32a

 

[wasntme]

Thank you Logit,

I have used some freestyle and ended up with the below code, that currently works fine.

Option Explicit

Private Sub Workbook_Open()
           
Dim CurrentTime
Dim i As Long
Dim j As Long
        
Worksheets(1).Range("B4").Value = 0
j = Worksheets(1).Range("B3").Value
                        
    For i = 1 To j
                
        CurrentTime = Timer
                
        Do While Timer < CurrentTime + 1
                
        DoEvents
            
        Loop
                                
        Worksheets(1).Range("B4").Value = i
                
    Next i
                    
End Sub

with B3 being the sum of time left in seconds.
Regards

 

[Logit]

.
Sorry I misunderstood. Your thread title said "Countdown Timer".