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

excel, excel-2007, user-defined-functions, vba

Solution

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

Problem

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

Original source

Related problems