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<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




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


<[email protected]> wrote in message
news:[email protected]...
> 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<at>tarkon.ee
> > Arvi Laanemets
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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<at>tarkon.ee
> > > Arvi Laanemets
> >
>
>

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


"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>

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