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.
Expand|Select|Wrap|Line Numbers
- 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.
Expand|Select|Wrap|Line Numbers
- 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.
Expand|Select|Wrap|Line Numbers
- Private Sub ProcName(Optional dbX As DAO.Database)
- If dbX Is Nothing Then Set db = ...
- End Sub