View Full Version : PRECEDENTS property not working in a UDF
Jaspington
06-09-2011, 05:07 AM
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
06-09-2011, 05:40 AM
Need to see the code.
Jaspington
06-09-2011, 05:47 AM
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
06-09-2011, 06:40 AM
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
06-09-2011, 06:49 AM
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
06-09-2011, 06:51 AM
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
06-09-2011, 06:57 AM
Makes sense! Thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.