Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: How to check for Optional parameter=Nothing
Thanks
Arvi Laanemets |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you spell check protected but unlocked cells in excel2000? | Excel Discussion (Misc queries) | |||
format optional parameter | Excel Worksheet Functions | |||
format optional parameter | Excel Discussion (Misc queries) | |||
Excel2000: Declaring function parameter as an array | Excel Programming | |||
Excel2000: UDF's parameter as cell range OR array | Excel Programming |