Excel2000: How to check for Optional parameter=Nothing
 

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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

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.

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

Hi


"Bob Phillips" wrote in message
...
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;3 6252;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! :-)))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

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

"Arvi Laanemets" wrote in message
...
Hi


"Bob Phillips" wrote in message
...
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;3 6252;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! :-)))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

Thanks, I'll check this out.


Arvi Laanemets


"Bob Phillips" wrote in message
...
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

"Arvi Laanemets" wrote in message
...
Hi


"Bob Phillips" wrote in message
...
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;3 6252;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! :-)))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

Are you asking why 8204 rather than 8192. 8204 indicates

8192 (array) + 12 (variant)

--
Regards,
Tom Ogilvy


"Arvi Laanemets" wrote in message
...
Hi


"Bob Phillips" wrote in message
...
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;3 6252;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! :-)))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

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
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Bob Phillips" wrote in message
...
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

Excel2000: How to check for Optional parameter=Nothing
 

Thanks

Arvi Laanemets

Excel2000: How to check for Optional parameter=Nothing
 

Then why bother with the =Nothing?

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Bob Phillips" wrote in message
...
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

Excel2000: How to check for Optional parameter=Nothing
 

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" wrote in message
...
Then why bother with the =Nothing?

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Bob Phillips" wrote in message
...
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

Excel2000: How to check for Optional parameter=Nothing
 

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.

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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" wrote in message
...
Then why bother with the =Nothing?

--
HTH

Bob Phillips

"Arvi Laanemets" wrote in message
...
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
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Bob Phillips" wrote in message
...
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