Search code examples
excelvbaexcel-2007user-defined-functions

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

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.)

Solution

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