Override VBA's Now() function

Question

Is there a way to override Now() in VBA for testing purposes? Something like this here - What's a good way to overwrite DateTime.Now during testing?, but for VBA.

Thus, I would need Now() to return a predefined value and not the current PC time.

What I do not want is something like this:

Option Explicit

Public Function NowDefined() As Date

    'NowDefined = Now()
    NowDefined = Format("2016-01-15 15:01:01", "yyyy-MM-dd hh:mm:ss")

End Function

and simply changing in the whole code Now() to NowDefined().

Just write your own `Now()` function that returns whatever date/time you want. When you're done testing, either delete it or comment it out. — Rory, Jan 09 '17 at 08:39
@Rory, it was interesting, but due to some reason I did not think that I would be allowed simply to override the built-in function. — Vityata, Jan 09 '17 at 08:42
Pretty simple to test it rather than assuming it wouldn't work. ;) — Rory, Jan 09 '17 at 08:43
Well, I was thinking in a completely other direction, thinking how to go into the Now() libraries and probably change it from there, etc. Thanks! :) — Vityata, Jan 09 '17 at 08:44

score 3 · Answer 1 · answered Jan 09 '17 at 08:56

3

Rather than creating a String variable and then making Excel try to cast it to a Date, you would be better off generating the actual date to start with:

Function Now() As Date
    Now = DateSerial(2016, 1, 15) + TimeSerial(15, 1, 2)
End Function

answered Jan 09 '17 at 08:56

YowE3K

23,852
7
26
40

Yup, that's better than my code, to be honest I was not sure that it would compile, thats why I made it string. – Vityata Jan 09 '17 at 09:01
Actually, your considerations should have been reversed: Will the string compile? – Gustav Jan 09 '17 at 09:12
This is more of a side point than an actual answer to the question. Still, it's a good point. – Jean-François Corbett Jan 09 '17 at 10:08
@Gustav - yes, the OP's way of calculating a date does work, it's just inefficient - it first casts a `String` to a `Date`, uses `Format` to convert that back to a `String` (the same string it started with), then casts that `String` to a `Date` to pass back as the function's return value. The OP's code could be simplified to `NowDefined = "2016-01-15 15:01:01"`, which is valid and is effectively `NowDefined = CDate("2016-01-15 15:01:01")`. – YowE3K Jan 09 '17 at 18:26
@YowE3K: Or even simpler (no casting at all): `NowDefined = #2016-01-15 15:01:01#`. – Gustav Jan 09 '17 at 21:05

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

Vityata's answer works, but it is a bit fragile and can get cumbersome: you must remember to remove or comment out the entire function declaration when you're done testing; and put it back in whenever you need to do more testing... and then remember to remove it again when you release... etc.

I'd do something like this:

Const IN_TESTING_MODE As Boolean = True ' Set this to false when done testing

Function Now() As Date
    If IN_TESTING_MODE Then
        Now = ... 'whatever date you want
    Else
        Now = VBA.Now() ' back to default behavior
    End If
End Function

Here you only have to change one thing to go in and out of testing mode: the value of the constant IN_TESTING_MODE. You can reuse this constant in other functions as well for which you want a different behavior while testing.

edited May 23 '17 at 10:30

Community

1
1

answered Jan 09 '17 at 08:56

Jean-François Corbett

37,420
30
139
188

2

This would be my preference - you could also do something similar with [conditional compilation](http://stackoverflow.com/documentation/vba/3364/conditional-compilation/11557/changing-code-behavior-at-compile-time#t=201701090858290884913) – SierraOscar Jan 09 '17 at 08:59
Good, I am using always something similar for debug purposes, e.g. `If [set_in_production] Then On Error GoTo codify_Error` – Vityata Jan 09 '17 at 09:00

score 1 · Answer 3 · edited Jan 09 '17 at 08:49

1

Actually this works:

Option Explicit

Public Function Now() As Date

    Now = Format("2016-01-15 15:01:01", "yyyy-MM-dd hh:mm:ss")

End Function

edited Jan 09 '17 at 08:49

Jean-François Corbett

37,420
30
139
188

answered Jan 09 '17 at 08:41

Vityata

42,633
8
55
100

Override VBA's Now() function

3 Answers3