There isn't any VBA object that will hold a 3D array - to the best of my
knowledge. You will note that most built in functions don't support it
either.

Here is a workaround that allows you to use a 3D range. This is a skeleton
function - so It must have a 3D range in the form

=ABC(Sheet1:Sheet4!A1:B9)

obviously you can make it more robust. In my tests, it updated when the
referenced cells were edited. I did put in an if test so I can call it
from a procedure as well, passing in a string.

Public Function ABC(v As Variant)
Dim sStr As String, sStr1 As String
Dim sStr2 As String, iloc As Long
Dim sh As Worksheet, bYes As Boolean
Dim v1 As Variant, dblSum As Double
Dim rng As Range, rng1 As Range
On Error Resume Next
Set rng1 = Application.Caller
On Error GoTo 0
If Not rng1 Is Nothing Then
sStr = rng1.Formula
sStr = Right(sStr, Len(sStr) - 5)
sStr = Left(sStr, Len(sStr) - 1)
Else
sStr = v
End If
iloc = InStr(sStr, "!")
sStr1 = Left(sStr, iloc - 1)
sStr2 = Right(sStr, Len(sStr) - iloc)
v1 = Split(sStr1, ":")
Set sh = Worksheets(v1(LBound(v1)))
bYes = False
For Each sh In Worksheets
If LCase(sh.Name) = LCase(v1(LBound(v1))) Then _
bYes = True
If bYes Then
Set rng = sh.Range(sStr2)
Debug.Print rng.Address(0, 0, , True)
dblSum = dblSum + Application.Sum(rng)
End If
If LCase(sh.Name) = LCase(v1(UBound(v1))) Then _
bYes = False
Next
ABC = dblSum
End Function


--
Regards,
Tom Ogilvy

"Marc" wrote in message
...
Changed function to receive string but this forces me to enter the formula
as :

= MyFunction("Sheet1!B3:Sheet5!B3")

If I do this, the cell is not recalculated when Sheet1!B3 is changed as
would happen with SUM function.

Is there a type that will accept a 3DRange ? I tried with an Object type
to
no avail.

Regards,

Marc