Handling Optional Parameters in VBA

**NeoPa** · Dec 8 '11, 06:55 PM

Handling Optional Parameters in VBA

It seems there is no reliable way to handle these consistently for all variable types. Each has a different approach required, as I hope to illustrate below.

Variant Variables

Such variables can store all types of different values as well as objects, and they have a Missing flag which can be checked with the IsMissing() function.

Code:

Private Sub ProcName(Optional varX As Variant)
    If IsMissing(varX) Then varX = ...
End Sub

Standard Variables

These include Byte, Integer, Long, Single, Double, Currency, Decimal, Date & String. These are handled by assigning a default value as there is no bit specified to store a Missing flag.

Code:

Private Sub ProcName(Optional intX As Integer = -1)
    If intX < 0 Then ...
End Sub

Object Variables

Object variables, that is variables of any defined class (as opposed to the variable of specific type Object) also have no Missing flag available, so these come through when unset as Nothing.

Code:

Private Sub ProcName(Optional dbX As DAO.Database)
    If dbX Is Nothing Then Set db = ...
End Sub

I hope to clarify this further when I come across other types or other required approaches, but this is hard to find in the Help system so I wanted at least to have somewhere to refer to if I needed it again in future.