How to use IsMissing when the optional parameter is integer?

[HenryPotter]

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]

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

[Bob Phillips]

To use IsMissing, the argument has to be a Variant type.

[HenryPotter]

Any other function I could use besides IsMissing to solve this issue?

[Bob Phillips]

Test it for 0?

[HenryPotter]

Any other way to see if the argument is missing or not?

[Bob Phillips]

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]

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]

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!

[Bob Phillips]

No, the type is declared at compile time, not run time. But variants change sub-type as they are loaded.