.Precedents don't work in UDF

[Andruha]

I need to use the precedents of the cell that is the argument of a user-defined function as well as the cell itself. But if I write

Function MyFormula (MyCell as Range)

MyCell.Precedents don't work in this function. What can be done with it?

[lcoreyl]

I'm not even really sure what precedents are, but you could try

Function MyFormula (MyCell as object)

instead...

[tony h]

An example


Function MyFormula(ByRef MyCell As Range)
Dim rng As Range
Dim str As String

For Each rng In MyCell.Precedents
str = str & rng.Address & " , "
Next
MyFormula = str
End Function

[Andruha]

An example


Function MyFormula(ByRef MyCell As Range)
Dim rng As Range
Dim str As String

For Each rng In MyCell.Precedents
str = str & rng.Address & " , "
Next
MyFormula = str
End Function

Perhaps something is wrong with my Excel, but the fact is that this code doesn't work It returns only the address of MyCell (God knows why! )

[tony h]

I ran the code a s a sub which was ok then changed it to a formula which wasn't.

Sub a()
Dim rng As Range
Dim str As String
Dim mycell As Range

Set mycell = Selection

str = "direct : "
For Each rng In mycell.DirectPrecedents
str = str & rng.Address & " , "
Next


str = str & " indirect : "
For Each rng In mycell.Precedents
str = str & rng.Address & " , "
Next
Debug.Print str

End Sub
should work all right as a sub. Need to think about why it failed as a formula. Maybe it needs the cell selected?

Sorry need to go

[Andruha]

I also tried to create Sub()-procedure when I detected the problem The same result.
I explored the I-net - think here is the explanation of the problem: http://support.microsoft.com/?scid=k...=1759&sid=1289
But how can I solve it in this particular situation? Perhaps somebody can give a piece of advice?

[tony h]

I don't thik that is the answer. The range is being passed by reference and nothing is being changed on the worksheet. A value is being passed back but that is valid.

regards,
interesting. Might have a chance to play about with this on Tuesday if it has not been resolved.

[Andruha]

But in a certain sense each precedent is a range as well. And it is not passed to the function as an argument.

[lexcel]

Hi Andruha,

The only thing what I can think of is to capture the change of any of
the changed cells with the Worksheet_Change event subroutine. Use
Tony's code on the "Target" parameter to find the precedents and just
insert them in the sheet where you want them.
Not very elegant, but it should work.

If you can't figure out how, let me know and I'll spend some time
coding it.

Regards,

Lex

> But how can I solve it in this particular situation? Perhaps somebody
> can give a piece of advice?

[Andruha]

Unfortunatelly, but I also think it's the only possible way. Thank you!