View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default 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