I am writing a function (boolean) that tests if a cell is a direct
precedent of another cell. Any ideas? I am leaning towards using the
intersect of cell and direct precedents range null test as my
indicator.

Jeremy

Sounds good, but there is a problem. It doesn't work.

DirectPrecedents seems to work differently in a User Defined Function.
Presumably, this is a function of the calculation
engine.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jdcox1999" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am writing a function (boolean) that tests if a cell is a direct
> precedent of another cell. Any ideas? I am leaning towards using the
> intersect of cell and direct precedents range null test as my
> indicator.
>
> Jeremy
>

I get it to work from another sub but not from a worksheet cell (see
below). Any suggestions?


Sub testfunction()
Dim i As Boolean
i = IsDirectPrecedentOf(Range("BN45"), Range("BP45"))
MsgBox (i)
End Sub


Public Function IsDirectPrecedentOf(ByRef SubjectCell As Range, ByRef
ObjectCell As Range) As Boolean
IsDirectPrecedentOf = False
If Not Intersect(SubjectCell, ObjectCell.DirectPrecedents) Is Nothing
Then
IsDirectPrecedentOf = True
Else
IsDirectPrecedentOf = False
End If
End Function


Bob Phillips wrote:
> Sounds good, but there is a problem. It doesn't work.
>
> DirectPrecedents seems to work differently in a User Defined Function.
> Presumably, this is a function of the calculation
> engine.
>
> --
> HTH
>
> Bob Phillips
>

It won't work from a worksheet as I explained, and I am afraid I don't
have a solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jdcox1999" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I get it to work from another sub but not from a worksheet cell (see
> below). Any suggestions?
>
>
> Sub testfunction()
> Dim i As Boolean
> i = IsDirectPrecedentOf(Range("BN45"), Range("BP45"))
> MsgBox (i)
> End Sub
>
>
> Public Function IsDirectPrecedentOf(ByRef SubjectCell As Range, ByRef
> ObjectCell As Range) As Boolean
> IsDirectPrecedentOf = False
> If Not Intersect(SubjectCell, ObjectCell.DirectPrecedents) Is Nothing
> Then
> IsDirectPrecedentOf = True
> Else
> IsDirectPrecedentOf = False
> End If
> End Function
>
>
> Bob Phillips wrote:
> > Sounds good, but there is a problem. It doesn't work.
> >
> > DirectPrecedents seems to work differently in a User Defined Function.
> > Presumably, this is a function of the calculation
> > engine.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
>

We went with a text search on the string version of the range. Since
it will be limited in application this is ok. Anyways we use this
formula within conditional formatting. ie. we want to bold a cell if
it is a precedent of another. Using worksheet change routine works,
but we want to use ctrl+z. the string search works but is taxing on
the virtual memory when done in conditional formatting.



Function IsDirectPrecedentOf(subjectcell As Range, objectcell As Range)
As Boolean
IsDirectPrecedentOf = False
IsDirectPrecedentOf = InStr(1, objectcell.Formula,
subjectcell.Address(0, 0))
End Function

Bob Phillips wrote:
> It won't work from a worksheet as I explained, and I am afraid I don't
> have a solution.
>
> --
> HTH
>
> Bob Phillips
>