Setting default function parameters

May 7, 2009 by JP ·

If you wanted to specify default parameters for a function, here's how to do it in VBA.

Use the Optional keyword when specifying your function's parameters, and enter a default value if no parameter is passed to your function.

As a demonstration, here's a function that simply returns the date back to the calling function. We want to have a default date such that the function can be called with or without a date. Also, we want anyone trying to use the code to know the defaults, because someone using this function might not have easy access to the source code. With this method, the Intellisense will reveal the defaults for each parameter.

Function DefaultDate(Optional day As Date = "1/1/2009") As Date
  DefaultDate = day
End Function

Usage:

Sub testme()
Dim dte As Date

dte = DefaultDate
Msgbox dte

dte = DefaultDate(#1/10/2009#)
MsgBox dte
End Sub

The function "DefaultDate" takes one optional parameter: a date. If no date is specified, the date "1/1/2009" is used inside the function. The date is simply returned to the calling function.

If you paste this code into a standard module in Excel (or Outlook for that matter) and run the testme procedure, you'll get two messageboxes; one with 1/1/2009 and another with 1/10/2009. The function is first called with no parameters. The date 1/1/2009 is used in lieu of a date parameter.

If you try to type "DefaultDate(" you should see the Intellisense tell you what the default value is for that parameter.

The usual rules apply for optional parameters; if you declare any required parameters (i.e. by not using Optional), they have to be listed before the optional parameters. Or you can make all parameters optional and include default values for all of them!