I need to use the precedents of the cell that is the argument of
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 wit
it

--
Andruh
-----------------------------------------------------------------------
Andruha's Profile: http://www.excelforum.com/member.php...fo&userid=3595
View this thread: http://www.excelforum.com/showthread.php?threadid=55745

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

Function MyFormula (MyCell as object)

instead..

--
lcorey
-----------------------------------------------------------------------
lcoreyl's Profile: http://www.excelforum.com/member.php...nfo&userid=204
View this thread: http://www.excelforum.com/showthread.php?threadid=55745

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 Functio

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=55745

tony h Wrote:
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! )


--
Andruha
------------------------------------------------------------------------
Andruha's Profile: http://www.excelforum.com/member.php...o&userid=35955
View this thread: http://www.excelforum.com/showthread...hreadid=557454

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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=557454

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?


--
Andruha
------------------------------------------------------------------------
Andruha's Profile: http://www.excelforum.com/member.php...o&userid=35955
View this thread: http://www.excelforum.com/showthread...hreadid=557454

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

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

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=55745

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?