Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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 "mickey" wrote in message ... | 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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 he 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. "Niek Otten" wrote: 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 "mickey" wrote in message ... | 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
LOL
Your guess is as good as mine about the reasons for that behaviour. I have no clue, it sure looks like a bug... -- Regards, Luc. "Festina Lente" "mickey" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional Argument anomaly?
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. "PapaDos" wrote: LOL Your guess is as good as mine about the reasons for that behaviour. I have no clue, it sure looks like a bug... -- Regards, Luc. "Festina Lente" "mickey" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Argument not optional | Excel Programming | |||
Argument not optional Error 449! Need Help | Excel Programming | |||
Don't understand why I'm getting an argument not optional | Excel Programming | |||
Compile Error: Argument not optional | Excel Programming | |||
optional argument in a function | Excel Programming |