Search code examples
excelexcel-2007vba

Now() is giving an error

I am getting an error in the below code. 

Dim CurrentTime As Date
CurrentTime = Format(Now(), "hh:mm AM/PM")
If CurrentTime = ActiveSheet.Range("I1") Then
    Call SendEMail
End If

When the time is right, then the macro is debugging and Now is highlighted. Could anyone solve this problem?

Solution

  • You are not getting an actual error are you? It is just not working as expected.

    Matt Cremeens has identified your issue I believe, you have declared CurrentTime as a date data type. A date data type stores a number representing a time/date, however you are asking for it to store string information (AM/PM) too which is can't so it is getting stripped out.

    The results is cell one may hold a value like '10:30 AM' but the Format(Now(), "hh:mm AM/PM") code going into the Date variable is resulting in '10:30:00', so the two are never matching as strings. Copy as Matt has suggested and this should work (Dim CurrentTime As String).

    Better yet, use the date comparison function DateDiff:-

    If DateDiff("s",ActiveSheet.Range("I1"),Time()) > 0 then
    SendEmail
End If

    This is saying if the time now is greater than the value in I1 (to the second) then run SendEmail.