Setting default function parameters
May 7, 2009 • JP • No Comments • Rate This Article
• Links to this article
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.
DefaultDate = day
End Function
Usage:
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!
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Excel Tutorial Series – VBA Macros, Part Two Of Two
Next Post: Add dynamic ranges names to any worksheet
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].