Why am I getting a VBA Type Mismatch Error?

Question

I keep getting a Type Mismatch Error with the following code, and I can't figure out why. When the debugger gives me this error, and I press continue, it moves along as it should. So it seems the code works. So why throw this error in the first place?!

Sub Record()

    ' ...
    Application.OnTime Now + TimeValue("00:00:" & (60 - Second(Now))), "Record"
    ' ...

End Sub

EDIT:

Fixed colon, but issue still remained. The issue was That "00:00:60" is not a valid number as you cannot have 60 seconds. It should have been "00:01:00".

Answer 1

You miss colon after 00:00:

Application.OnTime Now + TimeValue("00:00:" & (60 - Second(Now))), "Record"

It doesn't cause 'Type mismatch' error, but it make your function works in unexpected way.

---

The error you talk about appears when Second(Now) = 0.

In such case expression TimeValue("00:00:" & (60 - Second(Now))) returns "00:00:60" and this is not a proper time format.

Here is work-around for this issue:

Application.OnTime Now + TimeSerial(0, 0, 60 - Second(Now)), "Record"