Excel2000: UDF's parameter as cell range OR array
 

Hi

How to create an UDF which can have as parameter either the cell range or an
array, i.e both syntaxes in examples below will work:

=MyFunction(A2,B2,OtherSheet!$A$2:$A$7)
or
=MyFunction(A2,B2,{2;3;4;5;6;7})


Thanks in advance
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: UDF's parameter as cell range OR array
 

Use a variant and test its type to find out what kind of parameter has been
used:

If TypeName(Lookup_Columns) = "Range" Or VarType(Lookup_Columns) = vbArray
Then
etc.

the parameter could be a range, a scalar value or a one or two dimensional
array of constants.

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Arvi Laanemets" wrote in message
...
Hi

How to create an UDF which can have as parameter either the cell range or
an
array, i.e both syntaxes in examples below will work:

=MyFunction(A2,B2,OtherSheet!$A$2:$A$7)
or
=MyFunction(A2,B2,{2;3;4;5;6;7})


Thanks in advance
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Excel2000: UDF's parameter as cell range OR array
 

Hi Arvi

For data in vectors, something along these lines:

Function Test(Var As Variant) As Variant
'Leo Heuser, 19-4-2005
Dim Counter As Long
Dim Dummy As Long
Dim ResultValue As Variant
Dim Sm As Double
Dim Trans As Boolean

If TypeName(Var) = "Range" Then
ResultValue = Var.Value
If UBound(ResultValue, 2) 1 Then Trans = True
Else
ResultValue = Var
On Error Resume Next
Dummy = UBound(ResultValue, 2)
If Err.Number 0 Then Trans = True
On Error GoTo 0
End If

If Trans Then ResultValue = _
Application.Transpose(ResultValue)

For Counter = 1 To UBound(ResultValue)
Sm = Sm + ResultValue(Counter, 1)
Next Counter

Test = Sm

End Function

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Arvi Laanemets" skrev i en meddelelse
...
Hi

How to create an UDF which can have as parameter either the cell range or
an
array, i.e both syntaxes in examples below will work:

=MyFunction(A2,B2,OtherSheet!$A$2:$A$7)
or
=MyFunction(A2,B2,{2;3;4;5;6;7})


Thanks in advance
--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets