PDA

View Full Version : How to use IsMissing when the optional parameter is integer?



HenryPotter
03-12-2009, 06:58 AM
Hi All,

I have some VBA codes on Optional function. However, even I didn't use the bracket value such as H.Holidays. The compiler automatically assigns 0 to the Index. Do you know how I could solve this issue?

Thanks,
Henry


Public Property Get Holidays(Optional Index As Index) As Variant
If IsMissing(Index) Then
Holidays = dTemp
Else
Holidays = dTemp(Index)
End If
End Property

HenryPotter
03-12-2009, 06:59 AM
Public Property Get Holidays(Optional Index As Integer) As Variant
If IsMissing(Index) Then
Holidays = dTemp
Else
Holidays = dTemp(Index)
End If
End Property

xld
03-12-2009, 07:00 AM
To use IsMissing, the argument has to be a Variant type.

HenryPotter
03-12-2009, 07:07 AM
Any other function I could use besides IsMissing to solve this issue?

xld
03-12-2009, 07:13 AM
Test it for 0?

HenryPotter
03-12-2009, 07:18 AM
Any other way to see if the argument is missing or not?

xld
03-12-2009, 07:22 AM
No, because the optional refers to whether it is passed or not. Non variant data types do no have a provision for a "missing" flag bit. It is this same thing that allows typed optional arguments to specify a default value.

Cosmo
03-12-2009, 08:28 AM
You can set the default value for the optional parameter, but there is no way to tell in the function if the value was missing (so the value would be using the default) or if the default value was actually passed to the function.

In some circumstances, if there is a value that would be outside the possible values sent (such as -1 if only positive values would be sent as the parameter), you could try using that value for your default value.

Public Property Get Holidays(Optional Index As Integer = -1) As Variant

If Index = -1 Then
' Missing Value
Else
...

HenryPotter
03-12-2009, 11:33 AM
Cosmo,

Thanks! This is really helpful. Another question I have is if it is possible to redeclare the property with a different type?

I want the property to be a double instead of a variant depends on certain condition.

Is it possible?

thanks!

xld
03-12-2009, 01:14 PM
No, the type is declared at compile time, not run time. But variants change sub-type as they are loaded.