When called from an Excel VBA UDF, Range.Precedents returns the range and not its precedents. Is there a workaround?

Question

I have this VBA function:

Public Function testPrec(target As Range) As String
    testPrec = target.Precedents.Address(External:=False)
End Function

In cell C11, I have this formula:

=C6+C8

If I call testPrec from the immediate window, it works just fine:

?testPrec([c11])
$C$6,$C$8

EDIT: It also works fine if called from a non-UDF macro Sub. The anomaly is the UDF case.

If I call it from the worksheet as a UDF:

=testPrec(C11)

I just get back "$C$11".

Does anyone know what's going on, or even better how to get the actual precedents from within a UDF call? (I'm using Excel 2007.)

Answer 1

It seems the constraint lies in that any call to .Precedents in a call stack that includes a UDF gets handled differntly. So, find a way to do the call outside the call stack triggered from the UDF: One thought is to use events. Here is a overly simplistic example to demonstrate

In a module define

Public strPrecedent As String
Public rngPrecedent As Range

Public Function testPrec(target As Range) As String
    Set rngPrecedent = target
    testPrec = strPrecedent
End Function

In a sheet define

Private Sub Worksheet_Calculate()
    If Not Module1.rngPrecedent Is Nothing Then
        Module1.strPrecedent = Module1.rngPrecedent.Precedents.Address(External:=False)
    End If
End Sub

testPrec now returns the correct range address, albeit one recal late. The idea is to have the UDF build a list of addresses to get Precedents for, and an event to do the actual GetPrecedent work, returning the address strings to the list for pickup by the udf. You might be able to build a workable solution out of this, depending on your needs.

Answer 2

The only workaround I can think of is to get target.formula and parse it - not very nice.