Excel countdown code error when reaching 0

Question

I currently have a code that acts as a countdown timer. The reason I'm not using a timer function or simply using the "CountUp" portion to countdown for me is because i'd like it to self adjust (another code could be running causing the calculation time for the other countdown methods to vary and be lengthy). Whenever the code reaches zero it causes an error: Application defined or object defined error.

 Sub CountupONE()
 Dim CountDownONE As Date

CountDownONE = Now + TimeValue("00:00:01")

Application.OnTime CountDownONE, "RealcountONE"
End Sub


Sub RealcountONE()
 Dim countONE As Date
 Dim counterONE As Date

 counterONE = Now - Sheets("Sheet1").Range("$A$11").Value
 countONE = Sheets("Sheet1").Range("$B$8").Value - counterONE
 'MsgBox countONE
 Sheets("Sheet1").Range("$C$8") = countONE
 'Sheets("Sheet1").[C8] = TimeValue("12:00:00 AM")

If countONE = TimeValue("12:00:00 AM") Then

Beep
[C11].Value = Now
[C11].NumberFormat = "h:mm:ss AM/PM"
Application.Speech.Speak ("Platen one is done")
Call BeginGraphing

Exit Sub

End If

Call CountupONE
End Sub

Sorry, it erros when it reachs: Sheets("Sheet1").Range("$C$8") = countONE — Dimitri M
– Dimitri M, Commented Feb 24, 2015 at 18:03
Application or object-defined error may indicate the worksheet is protected. Is the worksheet protected? — David Zemens
– David Zemens, Commented Feb 24, 2015 at 18:18
I think it's do with the way he's subtracting date datatypes that is causing the error. — Amen Jlili
– Amen Jlili, Commented Feb 24, 2015 at 18:23

David Zemens · Accepted Answer · 2015-02-24 18:50:20Z

1

This should resolve it:

 Sheets("Sheet1").Range("$C$8").Value = TimeValue(CStr(countONE))

I am not sure why... It is possible to put a raw Date into a cell: [A1].Value = CDate(Now), or even this does not raise an error:

countOne = Now()
Range("A1").Value = countOne

When the error raises in your case, the value of countOne is: March 5, 1785 [and some time value portion]. This has a Long equivalent of -41938. I don't know why this would cause an error and I would not expect it to.

In any case this method I suggest first coerces countOne into a String data type, and then pulls out the time portion of that value.

edited Feb 24, 2015 at 18:50

answered Feb 24, 2015 at 18:36

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Zemens Over a year ago

I wish I knew why the original code was causing an error. I certainly would not expect it to :(

Amen Jlili · Accepted Answer · 2015-02-24 18:43:13Z

1

12:00:20 AM is not date data type in your case.

The content of A11 and B8 should in the form of mm/dd/yyyy for you code to pass. You can add time to that.

Try 02/27/2015 and it will go through!

Edit: you can get to work with time like the following line:

If Format(countONE, "hh:mm:ss AM/PM") = "12:00:00 AM"
'do here
End if

edited Feb 24, 2015 at 18:43

answered Feb 24, 2015 at 18:29

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

1 Comment

Dimitri M Over a year ago

Nevermind, I think im understanding... I'll mess around with it for a few minutes.

Collectives™ on Stack Overflow

Excel countdown code error when reaching 0

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related