What is the proper method to know whether the optional arguments are supplied or Not?

[prabha_friend]

Function fun_Attn_Ytd(Optional startdate As Date, Optional Enddate As Date)
If startdate = Null Then 'This is not working as expected.
MsgBox "Startdate Not Supplied"
Else
MsgBox startdate
End If
End Function

I tried Null, Nothing. None of them works.

 

[JANR]

Use IsMissing instead.

If IsMissing (startdate) then....

JR

 

[ChrisO]

Function fun_Attn_Ytd(Optional StartDate As [b]Variant[/b], Optional EndDate As [b]Variant[/b])

    If [b]IsMissing[/b](StartDate) Then
        MsgBox "StartDate Not Supplied"
    Else
        MsgBox StartDate
    End If
    
End Function

 

[prabha_friend]

Thank you both of you.

 

[prabha_friend]

Friends. I read about that. According to that ismissing() works with variant types only. So It is recommended to declare all optional arguments as variant.

 

[ChrisO]

>>So It is recommended to declare all optional arguments as variant.<<

Not by me it’s not but if you want to use the IsMissing() function it helps otherwise it doesn’t work.

 

[vbaInet]

I see what the OP is getting at with this. If the optional argument was of a Number data type then you can't really tell whether or not that argument was parsed to the procedure because it will be initialised to 0 and of course 0 could be a parsed argument too. It would have been better if there was a IsInitialised() function specifically for Optional arguments whereby these optional variables do not get initialised unless a value is parsed. This is where Java comes in

I suppose you could use a global boolean variable for setting whether it was parsed or not just after the procedure call line and check against that variable.

So yes, in my opinion a variant would be the way forward, then using the IsMissing() function and if it's not missing cast the value to your intended data type (after appropriate checks are done).

 

[datAdrenaline]

You may want to consider declaring defaults for your optional values, then test for the defaults to see if different values have been provided...

Function fun_Attn_Ytd(Optional StartDate As Date = #1900-01-01#, Optional EndDate As Date = #1900-01-01#)
 
    If StartDate = #1900-01-01# Then
        MsgBox "StartDate Not Supplied"
    Else
        MsgBox StartDate
    End If
 
End Function

But ... If you go the Variant route, I would advise you to test with IsDate() simply because with StartDate and EndDate being variants, a caller could pass in a Null, and if a Null is passed in, then IsMissing() will return False since a value (albeit a special one) was passed to the argument, so the argument is not missing. However, if you test using IsDate() a truly missing argument, as well as a Null or any other value that is not a date will return a False ...

Function fun_Attn_Ytd(Optional StartDate As Variant, Optional EndDate As Variant)

    If Not IsDate(StartDate) Then
        MsgBox "StartDate Not Supplied as a Date"
    Else
        MsgBox StartDate
    End If
    
End Function

Also, to test to see if a Variant variable or object property is set to Null, you can use VBA's IsNull() function to do so.

 

[vbaInet]

I thought about the default value but the only drawback with that is the argument may be the same value as the default value.

And I suppose if your call includes the optional argument and it was passed with a null, then you could consider it as being passed. I think the OP might be referring to calling the function from multiple routines with some calls passing the optional argument and some not. If that's the case then you could do a SELECT statement which will identify from which routine it was called.

 

[ChrisO]

I also see what the OP is getting at but there is an illogical assumption here.

Just because the IsMissing() function only works with Optional arguments of Type Variant there is no reason for him to suggest that all Optional arguments are recommended to be of Type Variant.

The question could be asked; what if we don’t want to use the IsMissing() function, should all Optional arguments be passed as Type Variant?

To that the answer would be no.

 

[vbaInet]

Absolutely correct there ChrisO. I don't agree with the OP's suggestion for all Optional arguments to be of type Variant. It doesn't apply to everyone. Maybe for what the OP wants to achieve this was what he/she felt would be suitable for him/her.

 

[datAdrenaline]

Hello vbaInet,

>> I thought about the default value but the only drawback with that is the argument may be the same value as the default value. <<

Very true, that is why one should pick a default value is outside the domain of data that your application may experience. For example, if you are a car dealer and you record the date of each service, a default date of #1900-01-01# when interrogating the service records would not be with in the range of valid data for the application.

>> And I suppose if your call includes the optional argument and it was passed with a null, then you could consider it as being passed. <<

This really has nothing to do with a view point that "consider(s) it as being passed". If you pass a Null to an Optional Variant variable, IsMissing() will be False -- it is a fact of VBA, not a personal definition.

 

[gemma-the-husky]

I will test this - but does this imply that a typed optional argument will NEVER show false for "ismissing"?

 

[ChrisO]

No, optional arguments can be of Type Variant.

 

[vbaInet]

Hello there datAdrenaline,

Very true, that is why one should pick a default value is outside the domain of data that your application may experience. For example, if you are a car dealer and you record the date of each service, a default date of #1900-01-01# when interrogating the service records would not be with in the range of valid data for the application.

You're right there. But one would have to be careful if the function being used was taking input from the user as opposed to using pre-defined set of values.

This really has nothing to do with a view point that "consider(s) it as being passed". If you pass a Null to an Optional Variant variable, IsMissing() will be False -- it is a fact of VBA, not a personal definition.

I was actually referring to the OP re how to handle the calling functions.

All your points and that of ChrisO's are certainly valid and something the OP should really consider.

 

[datAdrenaline]

Hello Dave,

As Chris said, you can have the type of Variant, which negates your post ... but any of the determinant types (ie: Long, Integer, Byte, Currency, String, Boolean ...) will never return an IsMissing() value of false. By default, the deterministic data types will be initialized per VBA specifications (ie: numerics = 0, dates = 1899-12-30 {which is a serial of 0}, strings = "")

With variants, which are initialized to Empty, are set to a value of Missing if the argument is not passed. Note you can not set a variant to Missing manually, that value can only be set by the plumbing of VBA.

 

[Banana]

Just a thought.

Supplying default values, IMHO, does function just as well as using IsMissing() & Variant data type. It won't matter if the users pass in the value that was same as the default because that would (ought to?) mean that user explicitly wants to use default values. The only concern here is whether the default makes sense in the context.

In general, I'd rather use default values if only to to ensure I can continue to use more specific data types than risking the chance that I may get invalid data via a Variant data type as well as relieving the code of extra overhead associated with handling a Variant data type. There are times and places for Variants, but I don't think we should turn to Variant just to use IsMissing. Default values will perform the service adequately.

 

[gemma-the-husky]

Hello Dave,

As Chris said, you can have the type of Variant, which negates your post ... but any of the determinant types (ie: Long, Integer, Byte, Currency, String, Boolean ...) will never return an IsMissing() value of false. By default, the deterministic data types will be initialized per VBA specifications (ie: numerics = 0, dates = 1899-12-30 {which is a serial of 0}, strings = "")

With variants, which are initialized to Empty, are set to a value of Missing if the argument is not passed. Note you can not set a variant to Missing manually, that value can only be set by the plumbing of VBA.

No, thats what I meant - it didnt negate my post - i was just clarifying the situation

If the argument is of type variant then ismissing can return true

but if you accidentally include a type (especially easy if you are in the habit of typing every variable), then ismissing will never return true.

------------
i suppose this principle is similar to a field.

a typed variable cannot be null -but a field can be null.

 

[datAdrenaline]

Hello Dave ...

>> If the argument is of type variant then ismissing can return true <<

Yes ... 100% accurate.

--------------------

>> but if you accidentally include a type (especially easy if you are in the habit of typing every variable), then ismissing will never return true. <<
-- And --
>> a typed variable cannot be null -but a field can be null. <<

Well ... isn't Variant a type? (ie: Dim var As Variant) ... I know it seems I am being a bit strict here, but a typed variable can be null, it just has to be typed as a Variant, in addition a typed argument can return True when passed to IsMissing() it just has to be a Variant type.

The point to be taken is that the Variant type would be included in the set of "typed variables", so a distintion should be made as to whether the typed variable is a determinant (Long, Integer, String, Objects, etc) or indeterminant (Variant) type.

It is a small distinction, but its important to make that distinction to improve the clarity of the information.

 

[gemma-the-husky]

Hello Dave ...

>> If the argument is of type variant then ismissing can return true <<

Yes ... 100% accurate.

--------------------

>> but if you accidentally include a type (especially easy if you are in the habit of typing every variable), then ismissing will never return true. <<
-- And --
>> a typed variable cannot be null -but a field can be null. <<

Well ... isn't Variant a type? (ie: Dim var As Variant) ... I know it seems I am being a bit strict here, but a typed variable can be null, it just has to be typed as a Variant, in addition a typed argument can return True when passed to IsMissing() it just has to be a Variant type.

The point to be taken is that the Variant type would be included in the set of "typed variables", so a distintion should be made as to whether the typed variable is a determinant (Long, Integer, String, Objects, etc) or indeterminant (Variant) type.

It is a small distinction, but its important to make that distinction to improve the clarity of the information.

well that's splitting hairs a bit, I think, to say that a variant is a data type -I meant a non-overlaid data type.

What I took the thread to mean was as follows.

say you have a function

function funccheckdate(startdate as date, optional enddate)

rereading the thread seems to imply that the function header as above is fine
ismissing(enddate) will return true or false, depending whether the argument was passed


BUT - say you incorrectly put
function funccheckdate(startdate as date, optional enddate as date)

then the very fact that you inadvertently included the type qualification, means then this can NEVER return true for ismissing

---------
I am going away to check this now!


---------
in the style of Arnie

I'm Back

I have tested this - and Chris was on the money.

If you dont include the type, the optional argument returns true for ismissing

If you DO include the type, the optional argument returns false, and the optional argument is set to (presumably) 0 or a ZLS as appropriate.


Seems most perverse to me. Its so easy to add the type without thinking.