Boolean test for dependency between 2 cells

[Kelvin Stott]

How can I test whether one cell is dependent on another?


I tried the following, but does not seem to work:

Function Depends(Cell_B As Range, Cell_A As Range) As Boolean


If Not Intersect(Cell_B, Cell_A.Dependents) Is Nothing Then Depends = True Else Depends = False


End Function

 

[pgc01]

You code is correct, if you are using Depends() as a vba function.

If, however, you want to use it as a udf it won't work. The .Dependents property of the Range object will not work in udf context.

 

[Kelvin Stott]

OK, thanks, but even when used in VBA it seems to throw up errors when Cell_B is not a formula, or if it depends on cells in another worksheet.

 

[pgc01]

... or if it depends on cells in another worksheet.

You can see it in the help:

The Dependents property only works on the active sheet and can not trace remote references.

Check:

https://docs.microsoft.com/en-us/office/vba/api/excel.range.dependents

 

[pgc01]

... but even when used in VBA it seems to throw up errors when Cell_B is not a formula,

I don't understand.
If Cell_B is not a formula how can Cell_B be a dependent?

 

[pgc01]

Execute with the activeworksheet empty to see that your code works ok:

Sub TestDepends()
Dim r As Range
Dim b As Boolean

Range("A1").Value = 3
Range("B1").Formula = "=A1^2"

MsgBox Depends(Range("B1"), Range("A1"))

End Sub

 

[Kelvin Stott]

I don't understand.
If Cell_B is not a formula how can Cell_B be a dependent?

In that case clearly it can't be, but then the result should be False, rather than an error.

 

[Kelvin Stott]

Is there a more robust and efficient way to test if one cell is dependent on another?

 

[pgc01]

Hi

You can get all the dependents, also the ones in other worksheets, with vba if you display the arrows in the trace dependents and then navigate to each dependent.

If you have problems with the code I think I've posted the code in the past. I'll look for it and post it tonight (GMT) if you don't find a solution until then.