IsMissing is a built-in function that can be used to see if an optional, Variant argument was passed to a sub or function. Since optional arguments are, well, optional, your code needs to determine if they’ve been supplied. If the optional argument is declared as a Variant data type, the IsMissing function will tell you if the argument has been supplied or omitted.
Tony Toews recently blogged about IsMissing.
I’m sure I’ve used IsMissing in the past, but it’s very rare. There’s a trade-off. To use IsMissing, the argument must be declared as a Variant, and no other data type. In that case, I lose the benefits of strong data typing. For instance, I would have to declare a string as a Variant just to use IsMissing.
The other side is that VBA provides defaults for strongly typed optional arguments. If my optional argument is typed as Long and it’s omitted, it becomes a zero. If my optional argument is typed as String and omitted, it becomes a zero length string. That’s not necessarily bad, unless I need to distinguish if a zero was passed or the argument was omitted. For Long optional arguments, there’s no way to tell the difference.
A third possibility is supplying my own defaults, which I almost always do. I can declare optional argument thusly:
End Sub
All I’ve done is replace the default default with my own default, that is a zero length string with Joe. I still have the problem that I won’t know if Joe was passed or the argument was omitted, but since I have a specific case as the default, it’s less likely to matter.
I use the same technique as Tony (specified default)all the time, especially for functions called from VBA.
It also keeps the code more concise – I don;t have to verify ISMISSING to determine how to use the passed variable. Also useful when multiple optional values are passed.
I don’t see the claimed problem. There is a potential problem: variants take more memory and slow down processing since it takes extra cycles to determine what type they currently contain.
However, strong typing is not a problem (provided you could live with runtime instead of compiletime errors). It COULD be handled in code, just like checking whether the optional variable is missing or not.
If IsMissing(OptionalString) Then
OptionalString = “”
ElseIf VarType(OptionalString <> 8) Then
Err.Raise . . .
Else
‘it’s a string – nothing more to do
End If
This time with VB tags.
OptionalString = “”
ElseIf VarType(OptionalString <> 8) Then
Err.Raise . . .
Else
‘it’s a string – nothing more to do
End If
One good reason to use a variant argument is to allow for flexibility is a general purpose function. I probably can’t think of many examples of this, but one real world example I do have is a sub that creates and sends a message through Outlook. The declaration is this:
I use the variant argument to accept either a string or an array of strings, to allow for flexibility when I drop in this module.
That said, I totally agree about the strong typing. I use variants as sparingly as possible, but they do come in handy from time to time.
My only real issue with this example is… you don’t provide an example!
There was enough here to remind me how to use it, but… I had used it plenty before and had just forgotten that it had to be a variant.
Had I not already known how to use it, I wouldn’t have actually learned how to use the IsMissing.