Boolean test for dependency between 2 cells

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

Active Member
Joined
Oct 26, 2010
Messages
330
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:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

... 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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
330

ADVERTISEMENT

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

Active Member
Joined
Oct 26, 2010
Messages
330
Is there a more robust and efficient way to test if one cell is dependent on another?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,819
Members
414,179
Latest member
anthelsene

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top