CODEWITHSUNDEEP
vb.netexcel
AjayR
AjayR

Reputation: 4179

Excel VBA Get dependent cells recursively

I am writing a program which should get all the dependency cells from a given cell address either directly or indirectly effects other cell values in the same sheet. For ex.

A1 = 10
A2 = A1+20
A3 = A2+30

If I want the dependency for A3 it should result A1 and A2.

I have already used the method Range.Dependents which should give all the dependent cells but it fails. So alternately I am trying to use Range.DirectDependents which returns only the immediate cells dependency and writing a recursive function to do same for each result. For ex. A3 list A2 and again A2 returns A1 recursively.

My question is

  1. Why Range.dependents fails to list all nested dependent cells properly.

  2. Is it a good idea to use recursive method for each cells. (We are not sure how depth they are)

Here is my sample code

Dim Address As String
Try
    For Each DataRange In ValidRange
        cellAddress = DataRange.Address
        Try
            cellRange = DataRange.Dependents
            Slno = Slno + 1
            Address = cellRange.Address
            Output = Output & "<tr><td>" & Slno & "<td>" & cellAddress & "</td><td>" & Address & "</td></tr>"
        Catch ex As Exception
            'Skip if no dependencies found
        End Try
    Next
Catch ex As Exception
        Response.Write(ex.Message)
End Try

Upvotes: 3

Views: 4867

Answers (2)

Santosh
Santosh

Reputation: 12353

Try this code for VBA

Sub test()

    On Error Resume Next

    Dim cell As Range, c As Range
    Set cell = Range("A3").Precedents

    If cell Is Nothing Then Exit Sub

    For Each c In cell
        Debug.Print c.Address
    Next

End Sub

enter image description here

Upvotes: 3

sam092
sam092

Reputation: 1335

Did a quick try and found the following

?Range("A1").Dependents.Address
$A$2:$A$3

?Range("A2").Dependents.Address
$A$3

?Range("A3").Dependents.Address
Run-time error

Apparently, what you are looking for is Range.Precedents

?Range("A3").Precedents.Address
$A$1:$A$2

Upvotes: 9

Related Questions