Microsoft Access - Creating Optional Arguments In VBA Procedures (Access 97/2000/2002/2003)


Native VBA functions often don't require that you supply values to every supported argument. Chances are you'd like to design your own procedures and functions in the same way. Doing so is easy. When you declare the procedure's arguments, use the Optional keyword as in the following sample procedure:


  • Sub OptionalDemo(lng As Long, Optional lng2 As Long, _ Optional lng3 As Long) Debug.Print 10 * (lng + lng2 + lng3) End Sub

Note that the Optional keyword must precede each optional argument. Also, once you use the keyword, all subsequent arguments must be optional as well.


To test the function, try entering the following statements in the Immediate window:


  • OptionalDemo 34,10,5 OptionalDemo 34,,5 OptionalDemo 34 OptionalDemo

All of the statements except the last one return a result because only the first argument is required.

Go back