Excel2000: How to check for Optional parameter=Nothing

[Arvi Laanemets]

Hi

I have a function declared as:
----
Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant = Nothing, _
Optional Weekends As Variant = Nothing)
....
End Function
----

How to check in code for optional parameters set to Nothing? When I used the
code
....
If Holidays Is Nothing Then
statement
Elseif ...
.....

then it works only, when I omit the parameter or when I leave it empty, i.e.
when the parameter is set to Nothing. But when the the optional parameter
has some value, the the VBA cancels at first test without any error message.
When I test in debug window p.e. for 'Holidays Is Nothing', then instead of
False, <Object required> is returned. And the same with 'Not(Holidays Is
Nothing)' too.


Thanks in advance

 

[Bob Phillips]

Arvi,

It all works fine in my test. How are you passing Holidays? It must be an
object, such as Range, not a string or a number. As such, you might as well
declare it as object, not Variant.

 

[Arvi Laanemets]

Hi

Arvi,

It all works fine in my test. How are you passing Holidays? It must be an
object, such as Range, not a string or a number. As such, you might as well
declare it as object, not Variant.

It can be:
1. Omitted, i.e. =EnchNetworkdaysN(StartDate,EndDate) or
=EnchNetworkdaysN(StartDate,EndDate,,7)
2. Cell reference or expression returning a cell reference like
=EnchNetworkdaysN(StartDate,EndDate, $X$2:$X$14)
3. Reference to named range like
=EnchNetworkdaysN(StartDate,EndDate,Holidays)
4. Value or expression returning a value like
=EnchNetworkdaysN(StartDate,EndDate,DATE(2005,1,1))
5. Array like
=EnchNetworkdaysN(StartDate,EndDate,{36161;36215;36252;36254},{1;6;7})

So in function code I have to find an universal way to check which type of
parameter was used. Depending on test result, a new array containing 1 or
several elements is defined and initialized for use in further calculations.

Btw. I discovered, that when I pass the parameter as array with {}, then in
Watch window the parameter behaves as an array, I can refer to it's elements
as to array elements, etc., but when I check for it's type, then it isn't
vbArray (or 8192), but 8204. Nice! ))

 

[Bob Phillips]

Arvi,

If you don't set a default, you can test for missing. You can also test what
type of variable is passed.

Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant, _
Optional Weekends As Variant)
If IsMissing(Holidays) Then
MsgBox "No holidays"
ElseIf TypeName(Holidays) = "Range" Then
MsgBox "Range"
ElseIf IsArray(Holidays) Then
MsgBox "Array"
ElseIf IsDate(Holidays) Then
MsgBox "Date variable"
Else
MsgBox "Erroer"
End If
End Function

Does this hep you?

 

[Arvi Laanemets]

Thanks, I'll check this out.


Arvi Laanemets

Arvi,

If you don't set a default, you can test for missing. You can also test what
type of variable is passed.

Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant, _
Optional Weekends As Variant)
If IsMissing(Holidays) Then
MsgBox "No holidays"
ElseIf TypeName(Holidays) = "Range" Then
MsgBox "Range"
ElseIf IsArray(Holidays) Then
MsgBox "Array"
ElseIf IsDate(Holidays) Then
MsgBox "Date variable"
Else
MsgBox "Erroer"
End If
End Function

Does this hep you?

--
HTH

Bob Phillips

'Not(Holidays

 

[Tom Ogilvy]

Are you asking why 8204 rather than 8192. 8204 indicates

8192 (array) + 12 (variant)

 

[Arvi Laanemets]

Hi again

It looks like I get it to work in this way:
------
Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant = Nothing, _
Optional Weekends As Variant = Nothing)


Dim arrayH As Variant, arrayW As Variant
...

If TypeName(Holidays) = "Variant()" _
Or (VarType(Holidays) < 8 And Holidays > 0) Then
arrayH = Holidays
ElseIf VarType(Holidays) = 8204 Then
arrayH = Holidays.Value
Else
arrayH = Null
End If
If VarType(arrayH) = 8204 Then
' Sort arrayH
SelectionSort arrayH
End If
If VarType(arrayH) > 0 Then
' Remove double entries
' Remove empty elements
' Replace non-integer values with integers
End If
.....
End Function

 

[Arvi Laanemets]

Thanks

Arvi Laanemets

 

[Bob Phillips]

Then why bother with the =Nothing?

 

[Arvi Laanemets]

Hi

I can drop this for 3rd parameter, but I'm not sure I can do same for 4th
one. And how behaves TypeName() check when the parameter is omitted?


Arvi Laanemets

 

[Bob Phillips]

I assume Weekdays can be a value, range or array, so it should behave the
same. Typename doesn't care about the default value as my example showed.