Try using a Paramarray. This function doesn't really do anything other than
count numeric cells, but it shows how to take an array of range values. You
would use something like = CountNumericCells(Sheet1!a1,Sheet2!A3:Z11)

Public Function CountNumericCells(ParamArray CellRange() As Variant) As
Variant
Dim SubRange As Variant, LocalRange as Range, Cell as Range
Dim lCount as long
For Each SubRange In CellRange
Set S = Sheets(SubRange.Parent.Name)
Set LocalRange = S.Range(SubRange.Address)
Set LocalRange = Intersect(LocalRange, S.UsedRange)
lCount = lCount + LocalRange.Count
For Each Cell In LocalRange
If Not (IsEmpty(Cell.Value)) And IsNumeric(Cell.Value) Then
lCount = lCount +1
Next Cell
Next SubRange
CountNumericCells = lCount
End Sub


Robin Hammond
www.enhanceddatasystems.com

"Wouter HM" wrote in message
om...
Hi to all,

I want to have a userdefined function which accepts a range over more
then one sheet.
eg: MyFunction(Sheet1:Sheet3!A1:B10)

This is code

Public Function MyFunction(ByVal MyArea As ????) As Long
Dim rngCell As Range
Dim lngResult As Long
'
Application.Volatile
'
On Local Error GoTo MyFunction_err

For Each rngCell In MyArea
' Do this and that to calculate lngResult
Next rngCel

MyFunction = lngResult
GoTo CountUniqueValues_exit
CountUniqueValues_err:
MsgBox Err.Description, , Err.Number
MyFunction = -1
CountUniqueValues_exit:
Set UniqueValues = Nothing
End Function

With the standard function SUM you can do this.

I have tried these definitions:
MyFunction(ByVal MyArea As Range) = #VALUE
MyFunction(ByVal MyArea As Variant) = Error 424 / Error 2015 on
MyArea
MyFunction(ByVal MyArea As Object) = #VALUE

Any ideas?