Boolean test for dependency between 2 cells

Kelvin Stott

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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

pgc01

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

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,887
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
338

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
338
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,887
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,776
Messages
5,833,634
Members
430,221
Latest member
jmmccormick

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