# Boolean test for dependency between 2 cells

#### Kelvin Stott

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

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

Code:
``````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``````

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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.

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.

Last edited:
... 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?

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

Code:
``````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``````

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.

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

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.

Replies
7
Views
159
Replies
1
Views
67
Replies
0
Views
131
Replies
1
Views
134
Replies
0
Views
109

1,203,629
Messages
6,056,417
Members
444,862
Latest member
more_resource23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back