Countdown Timer fails

Question

I have used this code with succes as a Count Up Timer, but it fails as a Count Down Timer. I get the

Error 1004 Application-definde og object-defined error

in the line

Cell.Value = CountDown - (Timer - Start - 86400 * (Start > Timer)) / 86400

I think it multplies with zero.

I know the code will work with Cell.Value = CountDown - TimeSerial(0, 0, Timer - Start), but I can not use it, while TimeSerial is a Variant(integer) which means that the code can only do 32767 counts in seconds before it will stop in an Overflow error. Does anyone have an idear how to work around the error 1004 problem in the code below.

Option Explicit

Sub NewTimer() 'Countdown timer
    Dim Start As Long
    Dim Cell As Range
    Dim CountDown As Date

    Start = Timer

    Set Cell = Sheet1.Range("B1")    'This is the starting value.
    CountDown = TimeSerial(0, 0, 10)    'Set takttime
    Cell.Value = CountDown

    Do While Cell.Value > 0
        Cell.Value = CountDown - (Timer - Start - 86400 * (Start > Timer)) / 86400
        DoEvents
    Loop
End Sub

Answer 1

Since I have no idea why your code throws that error and only sometimes, try this alternative which is without that issue.

Sub OtherTimer()
    Dim UserInput As String
    UserInput = "00:00:10"

    Dim SecondsToRun As Long
    SecondsToRun = CDbl(TimeValue(UserInput)) * 24 * 60 * 60

    Dim TimerStart As Double
    TimerStart = Timer 'remember when timer starts

    Do
        Range("B1").Value = Format$((SecondsToRun - (Timer - TimerStart)) / 24 / 60 / 60, "hh:mm:ss")
        'count backwards from 01:15 format as hh:mm:ss and output in cell A1

        DoEvents
    Loop While TimerStart + SecondsToRun > Timer 'run until SecondsToRun are over
End Sub