Excel2000: Declaring function parameter as an array

A

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
 
P

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
 
A

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




Arvi said:
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
Click to expand...
Click to expand...
 
J

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
 
T

Tom Ogilvy

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




Arvi said:
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
Click to expand...
Click to expand...
Click to expand...
 
A

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 said:
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 said:
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
Click to expand...
Click to expand...
 
K

keepITcool

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel2000: Need help on UDF (working with arrays) 2
Query the same table three times 0
Excel2000: How to check for Optional parameter=Nothing 10
Excel2000: Reading values from range, passed to function as parameter using an expression 3
Array to Multiple Arrays 3
Optional argument as Variant not acting like number 4
Copy Array to Array, how to and should I? 13
UDF: how to tell if optional *range* parameter is empty? 3

Top