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