optinal default paramter to function

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I tried to create a function to receive optional paramter and has default
value now as date as following.

Public Function myPrepare(Optional FromDate As Date = now(), Optional ToDate
As Date= now()) As Boolean

The VBA failed, Are there any way to have default now value for optional
parameter?


Your help is great appreciated,
 
Souris said:
I tried to create a function to receive optional paramter and has default
value now as date as following.

Public Function myPrepare(Optional FromDate As Date = now(), Optional
ToDate
As Date= now()) As Boolean

The VBA failed, Are there any way to have default now value for optional
parameter?


Your help is great appreciated,
Click to expand...


Not directly, as the default value has to be a constant expression. But here
are two possible workarounds ...

If the argument is of type Date, then if it was not passed and has no
default value, it will have the value 0 (30 December 1899 00:00:00). If
you're sure that will never be a valid date for your purposes, you can do
something like this ...

Public Function TestFunction1(Optional ByVal date1 As Date, Optional ByVal
date2 As Date) As Boolean


If date1 = 0 Then date1 = Now()
If date2 = 0 Then date2 = Now()

Debug.Print date1, date2

End Function

Alternatively, you can pass the argument as a variant, and use the
IsMissing() function to test whether a value was passed, like so ...

Public Function TestFunction2(Optional ByVal date1 As Variant, Optional
ByVal date2 As Variant) As Boolean

If IsMissing(date1) Then date1 = Now()
If IsMissing(date2) Then date2 = Now()

Debug.Print date1, date2

End Function
 
Back
Top