title: Excel VBA Get dependent cells recursively
body: <p>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.</p>
  
  <pre><code>A1 = 10
  A2 = A1+20
  A3 = A2+30
  </code></pre>
  
  <p>If I want the dependency for A3 it should result A1 and A2.</p>
  
  <p>I have already used the method <code>Range.Dependents</code> which should give all the dependent cells but it fails. So alternately I am trying to use <code>Range.DirectDependents</code> 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.</p>
  
  <p>My question is</p>
  
  <ol>
  <li><p>Why Range.dependents fails to list all nested dependent cells properly.</p></li>
  <li><p>Is it a good idea to use recursive method for each cells. (We are not sure how depth they are)</p></li>
  </ol>
  
  <p>Here is my sample code</p>
  
  <pre><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 &amp; "&lt;tr&gt;&lt;td&gt;" &amp; Slno &amp; "&lt;td&gt;" &amp; cellAddress &amp; "&lt;/td&gt;&lt;td&gt;" &amp; Address &amp; "&lt;/td&gt;&lt;/tr&gt;"
          Catch ex As Exception
              'Skip if no dependencies found
          End Try
      Next
  Catch ex As Exception
          Response.Write(ex.Message)
  End Try
  </code></pre>