How to use multiple sheets with range in userdefined function.

[Wouter HM]

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?

 

[Robin Hammond]

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

 

[Charles Williams]

Hi Wouter,

There is no satisfactory answer using VBA AFAIK: the fundamental problem is
that the Range object does not allow for 3-dimensional cells.

You can get round it if you are willing to use different syntax for a 3-d
range which separates the first sheet and last sheet into separate
arguments, but its not user-friendly having to invent new excel syntax.


--
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Tom Ogilvy]

Check out Mr. Erlandsen's site for sample code:
http://www.erlandsendata.no/english/vba/adodao/

 

[Tom Ogilvy]

Sorry, replied to the wrong post.

 

[Tushar Mehta]

AFAIK, there is any way to make that work. 3D worksheet references are
not part of the XL object model or the UDF model.

If you install Laurent Longre's Morefunc utility
(http://longre.free.fr/english/), you can then use the ThreeD function
to convert 3D references into a matrix and use that in your function.
[Note that I haven't had any reason to use the ThreeD function in
conjunction with a UDF.]

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

Thank goodness. I was confused<g>

Bob