PRECEDENTS property not working in a UDF

[Jaspington]

Hi all

I have written a UDF formula called FindPrecedents that uses range.precedents.row in the code (range in this case being the target cell of the formula).

When I use this formula within a sub routine, it works fine. But when I enter into a cell, it returns the wrong value.

After testing, the problem seems to lie with the .precedents.row property, which for some reason does not return the correct value (it merely returns the .row value of the target cell, not the target's precedent(s).

But to reiterate, the exact same code, with the exact same target cell, works fine if called in a sub routine, the probelm is only when the formula is entered into a worksheet cell!

NB This is in Excel 2010

Can anyone help at all?!

[p45cal]

Need to see the code.

[Jaspington]

Ok, here you go (hope not too long!):

Function FindPrecedents (Target As Range) As String

Dim nFormulaRow
Dim nFormulaCol
Dim PositionVert
Dim PrecVert
Dim PrecHoriz
Dim PositionHoriz

nFormulaRow = Target.Row
nFormulaCol = Target.Column
PrecVert = Target.DirectPrecedents.Row
PrecHoriz = Target.DirectPrecedents.Column

PositionVert = nFormulaRow - PrecVert
PositionHoriz = nFormulaCol - PrecHoriz

Select Case PositionVert
Case Is > 0: PositionVert = "Up"
Case Is < 0: PositionVert = "Down"
Case Else: PositionVert = ""
End Select

Select Case PositionHoriz
Case Is > 0: PositionHoriz = "Left"
Case Is < 0: PositionHoriz = "Right"
Case Else: PositionHoriz = ""
End Select

FindPrecedents = PositionHoriz & PositionVert

End Function


When use in a cell in the worksheet it always returns blank (it says the precedents.row always equals target.row; same for column).

But if I run it in a sub (eg FindPrecedents("Range")) then it works fine!

Any ideas?

[mikerickson]

UDF's behave differently when called from a VBA sub or from a worksheet formula.

E.g. a UDF called from VBA can change a cell's color, not so when called from a worksheet.

.Precedents, like .Find and .SpecialCells do not work when called from a worksheet formula.

Your UDF looks like it might be a help in auditing a worksheet. Have you looked at the Trace Precedents feature?

[Jaspington]

Hi Mike

Thanks for that, makes sense as I couldn't see how the code could be doing this!

That does however lead to a question - is there an easy way of finding out which properties have this way of working/not working, there was no mention of it in Help.

Re: Trace Precedents, I actually wrote the function to be used within a VBA procedure, so it is fine there. I was just testing it in a worksheets and then wondered what the problem was!

[mikerickson]

That does however lead to a question - is there an easy way of finding out which properties have this way of working/not working, there was no mention of it in Help.

Trying them in small test UDF's it the way I check.

[Jaspington]

Makes sense! Thanks!