Excel2000: Declaring function parameter as an array

[Arvi Laanemets]

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

 

[paul.robinson]

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]

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<at>tarkon.ee
Arvi Laanemets

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

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

 

[Jim Cone]

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