Bingo!

Exactly right PapaDos. When called from VBA the IsEmpty works as expected.
Do you know whether this is a MS coding error, or is there some basic
incompatibility problem between Excel and VBA when it comes to the "Empty"
subtype?

Good Call, thanks again ;-)


"mickey" wrote:

I might be able to test that, because the same function is called from VBA
and from the worksheet. I'll let yo know, Thanks.

"PapaDos" wrote:

The problem is not VBA.
Your code will work ok when called from another VBA macro or the immediate
window.

The problem is only when called from a worksheet...
--
Regards,
Luc.

"Festina Lente"


"mickey" wrote:

Thanks for your post. Check my reply to Otten, I may have a handle on the
problem owing to the way VB handles optional, un-used Variant function/sub
arguments.

Cheers :-)

"PapaDos" wrote:

That's a somewhat buggy "feature" of Excel.
If you call your function from another VBA function or sub, it will work OK.
It should be OK in the immediate window too.
But B will be an "error" variant if called from a worksheet...

--
Regards,
Luc.

"Festina Lente"


"mickey" wrote:

Have run across a problem with an optional variant.

Code Example:

Function X (A As Range, Optional B As Variant = ?) As Variant

If IsEmpty(B) Then MsgBox "B is Empty"
If IsNull(B) Then MsgBox "B is Null"
If IsError(B) Then MsgBox "B is Error"

If the optional argument "B" is initialized to "Null" (i.e. Optional B As
Variant = Null) then the message "B is Null" is displayed. However, if "B"
is initialized to "Empty" (i.e. Optional B As Variant = Empty), the "IsEmpty"
is false and the message "B is Error" is displayed.

Does anyone know why the initialization of "B" to "Empty" results in an error?

Thanks.