Optional arguments and IsMissing

[pinkfloydfan]

Hi there

In a UDF with an Optional argument where the type is a long, if the
argument is passed an empty cell or variable then the IsMissing
returns false. So, for example:

Function test(arg1, arg2, Optional arg3 as long)
If IsMissing(arg3) then
'code
End If
'rest of code
End Function

If arg3 is passed say range A3 and there is no data in A3 then the
IsMissing returns false: in fact the function thinks that arg3 is
zero.

As zero is a option I want to test for it does not help me decide if
arg3 is missing or not.

Any ideas how to figure out if the optional argument really is
missing?

Thanks a lot
Lloyd

 

[Jim Rech]

If you pass _something_ then IsMissing is false. A range is something.
Excel conveniently converts the value of that range to a long as you
requested. The value of an empty cell is zero.

To distinguish an empty cell from one with a zero forget the IsMissing (it
will always be false since you're always passing something) and try this:

Sub a()
MySub ActiveCell.Value
End Sub

Sub MySub(X As Variant)
MsgBox IsEmpty(X) ''True is cell is empty
End Sub

 

[pinkfloydfan]

ok thanks

So I set the optional argument as a variant and test it for IsMissing
before using it. And I guess that same applies when calling the
function from within VBA: I have to pass an empty Variant...