Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Hi
What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). .... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) .... Thanks in advance -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Hi Arvi
You must first of all declare the variables, not specify their values. e.g.Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) Holidays and Weekend then might arise in several ways: 1. They might be publicly declared variables e.g. Dim MyHolidays as Variant at the top of a code module before any subs then use. Then MyHolidays is calculated in another sub and called using = EnchWorkdaysN(myDate, myEndDate, myHolidays) 2. They might be the output of another function function GetHolidays(Param list) as Variant ....end function then MyHolidays = GetHolidays(Param list) etc 3. They might be generated within the calling sub containing the function sub Calculate() Dim MyHolidays as Variant .... 'code to calculate MyHolidays .... = EnchWorkdaysN(myDate, myEndDate, myHolidays) .... end sub Don't forget that with optional parameters you must test if they are there using IsMissing e.g. Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) ..... If not IsMissing(Holidays) then 'code using Holidays Else 'deal with this possibility end if .... end function The different types of value you give for WeekEnds must be tested for within the function definition, essentially using "If...then...Else" or Case statements. regards Paul Arvi Laanemets wrote: Hi What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). ... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) ... Thanks in advance -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Hi Paul
The syntax Public MyFunction(Optional ParameterName:=MyValue) .... declares a function, with an optional parameter - when user omits the parameter, MyValue is taken for it (look at 'Function statement' in VBA Help). I.e. when into some cell you enter the formula =MyFunction() then it is same as you entered =MyFunction(MyValue). With other words, instead of checking, was the parameter passed or not, I want it to have automatically to have some predefined value. At least in VBA Help nowhere is said directly, that the default value for an parameter can't be an array. It works well with single-value parameters, but I need to say to VBA, that the default value for function's optional parameter is an array. I.e. when the parameter is omitted, then it is a 2-element array, which contains values 1 and 7. Only when this is impossible, then I'll go for default value=Nothing, and have to redefine the passed (unpassed) value in function code - too clumsy solution for my taste. Thanks anyway -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets wrote in message ups.com... Hi Arvi You must first of all declare the variables, not specify their values. e.g.Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) Holidays and Weekend then might arise in several ways: 1. They might be publicly declared variables e.g. Dim MyHolidays as Variant at the top of a code module before any subs then use. Then MyHolidays is calculated in another sub and called using = EnchWorkdaysN(myDate, myEndDate, myHolidays) 2. They might be the output of another function function GetHolidays(Param list) as Variant ...end function then MyHolidays = GetHolidays(Param list) etc 3. They might be generated within the calling sub containing the function sub Calculate() Dim MyHolidays as Variant ... 'code to calculate MyHolidays ... = EnchWorkdaysN(myDate, myEndDate, myHolidays) ... end sub Don't forget that with optional parameters you must test if they are there using IsMissing e.g. Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) .... If not IsMissing(Holidays) then 'code using Holidays Else 'deal with this possibility end if ... end function The different types of value you give for WeekEnds must be tested for within the function definition, essentially using "If...then...Else" or Case statements. regards Paul Arvi Laanemets wrote: Hi What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). ... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) ... Thanks in advance -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Arvi,
The help file for "Function Statement" says... "default value" Optional. Any constant or constant expression. *** Valid for Optional parameters only. If the type is an Object, an explicit default value can only be Nothing. So the following works... Function Test(ByRef Arg As String, Optional varArr As Variant = 7) but this will not... Function Test(ByRef Arg As String, Optional varArr As Variant = array(1, 7)) Regards, Jim Cone San Francisco, USA "Arvi Laanemets" wrote in message ... Hi What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). ... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
It says in help that it must be a constant or constant expression.
"Array" is a function. An expression containing a function would not be considered a constant expression. ------------------------------------------- Array Function Returns a Variant containing an array. Syntax Array(arglist) The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created. ------------------------------------------- Additionally, an array structure generally contains information about the Array variable, but not the data in the array. It contains a pointer that points to another location where the data is stored. I would assume this is another reason that the Array would not be considered to be suitable to be treated as a constant. -- Regards, Tom Ogilvy "Arvi Laanemets" wrote in message ... Hi Paul The syntax Public MyFunction(Optional ParameterName:=MyValue) ... declares a function, with an optional parameter - when user omits the parameter, MyValue is taken for it (look at 'Function statement' in VBA Help). I.e. when into some cell you enter the formula =MyFunction() then it is same as you entered =MyFunction(MyValue). With other words, instead of checking, was the parameter passed or not, I want it to have automatically to have some predefined value. At least in VBA Help nowhere is said directly, that the default value for an parameter can't be an array. It works well with single-value parameters, but I need to say to VBA, that the default value for function's optional parameter is an array. I.e. when the parameter is omitted, then it is a 2-element array, which contains values 1 and 7. Only when this is impossible, then I'll go for default value=Nothing, and have to redefine the passed (unpassed) value in function code - too clumsy solution for my taste. Thanks anyway -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets wrote in message ups.com... Hi Arvi You must first of all declare the variables, not specify their values. e.g.Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) Holidays and Weekend then might arise in several ways: 1. They might be publicly declared variables e.g. Dim MyHolidays as Variant at the top of a code module before any subs then use. Then MyHolidays is calculated in another sub and called using = EnchWorkdaysN(myDate, myEndDate, myHolidays) 2. They might be the output of another function function GetHolidays(Param list) as Variant ...end function then MyHolidays = GetHolidays(Param list) etc 3. They might be generated within the calling sub containing the function sub Calculate() Dim MyHolidays as Variant ... 'code to calculate MyHolidays ... = EnchWorkdaysN(myDate, myEndDate, myHolidays) ... end sub Don't forget that with optional parameters you must test if they are there using IsMissing e.g. Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant, _ Optional Weekends As Variant) .... If not IsMissing(Holidays) then 'code using Holidays Else 'deal with this possibility end if ... end function The different types of value you give for WeekEnds must be tested for within the function definition, essentially using "If...then...Else" or Case statements. regards Paul Arvi Laanemets wrote: Hi What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). ... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) ... Thanks in advance -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Thanks both Tom and Jim
Then I have to use Nothing as default value and replace it with an array in code. (I don't like it at all!) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Jim Cone" wrote in message ... Arvi, The help file for "Function Statement" says... "default value" Optional. Any constant or constant expression. *** Valid for Optional parameters only. If the type is an Object, an explicit default value can only be Nothing. So the following works... Function Test(ByRef Arg As String, Optional varArr As Variant = 7) but this will not... Function Test(ByRef Arg As String, Optional varArr As Variant = array(1, 7)) Regards, Jim Cone San Francisco, USA "Arvi Laanemets" wrote in message ... Hi What must be the syntax, to declare a function with a variant parameter with array default value (parameter Weekends in example below). ... Optional Weekends As Variant = {1,7}.. returns function header to red immediately, the syntax in example below returns an error "Constant expression required", when the function is called. [The parameter must be variant, as it can be an array, a cell reference, or an integer between 0 and 7. I.e. valid syntax for worksheet function will be: =EnchWorkdaysN(Date1, Date2, ,{1;6;7}) , or =EnchWorkdaysN(Date1, Date2, ,$S$1:$Z$1) , or =EnchWorkdaysN(Date1, Date2, ,WorkdaysList) , or =EnchWorkdaysN(Date1, Date2, ,7), or =EnchWorkdaysN(Date1, Date2) , etc.] Public Function EnchWorkdaysN(StartDate As Date, _ EndDate As Date, _ Optional Holidays As Variant = Nothing, _ Optional Weekends As Variant = Array(1, 7)) Thanks in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Declaring function parameter as an array
Arvi,
if an argument is not optional it CAN be an array(of undeclared size). but typing arguments as arrays is only usefull if your functions are intended to be used as VBA functions, not when called as udf from worksheet. note there is an important difference between an array of type variant and a variant (which maybe an array).. certainly when it comes to function arguments So imo: You just have to code for a variant and THEN check to see what the variant contains. in the code below only func3 will work when called as an udf. Note: IsMissing will only work on variant arguments Option Explicit Sub TestVBAcalling() Dim aStr$(3, 4), aVar(3, 4), var Debug.Print Func1(aStr), Func2(aVar) Debug.Print Func3(aStr), Func3(aVar) Debug.Print Func3(aStr, aStr), Func3(aStr, aVar) Debug.Print Func3(var), Func3(1), Func3(Empty) Debug.Print Func3(var, var), Func3(1, 1), Func3(var, Empty) End Sub Function Func1(arg1() As String) 'note: cant change arg to byval Func1 = "ok" End Function Function Func2(arg1() As Variant) 'note: cant change arg to byval Func2 = "ok" End Function Function Func3(ByVal arg1 As Variant, Optional arg2 As Variant) Dim vRet If Not IsArray(arg1) Or (Not IsMissing(arg2) And Not IsArray(arg2)) Then vRet = CVErr(xlErrRef) Else vRet = "ok" End If Func3 = vRet End Function hth -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Arvi Laanemets wrote : Hi Paul The syntax Public MyFunction(Optional ParameterName:=MyValue) ... declares a function, with an optional parameter - when user omits the parameter, MyValue is taken for it (look at 'Function statement' in VBA Help). I.e. when into some cell you enter the formula =MyFunction() then it is same as you entered =MyFunction(MyValue). With other words, instead of checking, was the parameter passed or not, I want it to have automatically to have some predefined value. At least in VBA Help nowhere is said directly, that the default value for an parameter can't be an array. It works well with single-value parameters, but I need to say to VBA, that the default value for function's optional parameter is an array. I.e. when the parameter is omitted, then it is a 2-element array, which contains values 1 and 7. Only when this is impossible, then I'll go for default value=Nothing, and have to redefine the passed (unpassed) value in function code - too clumsy solution for my taste. Thanks anyway |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2000: UDF's parameter as cell range OR array | Excel Programming | |||
Declaring one bidimensinal Array | Excel Programming | |||
declaring a public array | Excel Programming | |||
Q: Declaring a dynamic array | Excel Programming | |||
declaring an array of CheckBox's | Excel Programming |