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
|