user defined function receiving a range as parameter

[Marc]

Hi everyone,

I have created a UDF receiving a range very similar to the SUM function but
it works only on ranges that do not contain the worksheet names.

For example, I can do :
= MyFunction(A1:B3) like I could for =SUM(A1:B3)

I receive the range correctly and I can go thru the cells of the range.

But when I move to a more complex range like :
=MyFunction(Sheet1!A1:Sheet8!A1) like I could do for =SUM(Sheet1!A1:Sheet8!A1)

My macro does not even start and the cell is filled with "#VALUE!"

Simple macro really. Here is the code :

Function MyFunction(sel As Range) As String
MyFunction = Empty
For i = 1 To sel.Count
If (sel.Cells(i).Value <> Empty) Then
If (MyFunction <> Empty) Then
MyFunction = MyFunction + ", " + sel.Cells(i).Worksheet.Name
Else
MyFunction = sel.Cells(i).Worksheet.Name
End If
End If
Next i
End Function

 

[Tom Ogilvy]

you can't pass in a 3D range to rng variable. A rng variable can only
refer to one sheet. You will need to change your argument to a string and
then write the code to interpret it.

Function MyFunction(sel As String) As String

 

[Marc]

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

 

[David McRitchie]

In Excel 2000 =SUM(sheet1!B3:sheet5!B3) would not
work you would have to use =SUM(sheet1:sheet5!B3)

You could make your user defined function volatile.

 

[Tom Ogilvy]

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

 

[Marc]

Thanks Tom.

That really did the trick.

Regards,

Marc

 

[Bernie Deitrick]

Marc,

If you want to force a recalc based on Excel's dependency tree, you can add a function to the
formula in your cell with the UDF function call, but use one that won't change your result. For
example, thise should force a recalc of the whole cell when B3 changes on any sheet, from sheet1 to
sheet5:

= MyFunction("Sheet1!B3:Sheet5!B3") + IF(SUM(Sheet1:Sheet5!B3)>0,0,0)

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

any debug.print code was just in the procedure for testing and is not needed
by the function.