Optional Argument anomaly?

[mickey]

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.

 

[Niek Otten]

Your code doesn't seem to be complete; there are unfinished Ifs.
If you want to test for an omitted B, use the IsMissing() function.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.

 

[PapaDos]

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...

 

[mickey]

Thanks for the response.

First I would have to differ with you, the If's are valid single line If''s
(no "End If" required) (Eg. If A then MsgBox "A is True" ) If A is true the
message "A is True" displays.

I'm aware of numerous work arounds, such as "IsMissing".

I have working code, I am trying to understand the apparent inconsistancy of
why initializing B to Null tests correctly via IsNull(B), but initializing B
to the variant sub-type "Empty" fails to evaluate correctly by "IsEmpty(B)".

Since I have posted this message I ran an experiment where I did not
initialze "B" at all - The thought being that "Empty" is an indication of an
uninitialized state, perhaps trying to set the unused optional argument B to
state it was already in might be the problem. Unfortunately the
uninitialized, and unspecified (not used in the function call) still tested
(IsError(B)) as an error.

I think I understand what maybe happening here: Optional "Variants" maybe
treated different then other fixed-type variables in that IF they are
optional and IF they are not used in the calling argument they "REALLY" don't
exist, unless you initialize them to a valid value such as a "string",
integer, etc. or NULL. As "Empty" simply means "Uninitialized" it is the
same as no initialization, therefore any test you try and perform on a
non-initialized, un-used optional variant will return an "Error". This maybe
why IsMissing only applies to Variants, all other types do exist even if they
are un-used options. Again, this only applies to optional, un-used variant
arguments. Declared variants can indeed be set equal to empty and tested by
the IsEmpty function, but not optional, un-used variants.

This is only my current speculation, I would appreciate anyones comments on
that speculation.

Thanks.

 

[mickey]

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]

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...

 

[mickey]

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.

 

[mickey]

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 ;-)

 

[PapaDos]

LOL
Your guess is as good as mine about the reasons for that behaviour.
I have no clue, it sure looks like a bug...

 

[mickey]

Just speculating, but if Excel is written in "C", there maybe an
incompatiblitiy between the way C and VBA represents "Empty", or as you say
it could be a bug.

Cheers.