Address of first layer of precedent cells via VBA in Excel

Question

On the debug toolbar, you can click a button that shows you directed arrows of cells that are directly called by the selected cell (I'm calling these direct precedents the 'first layer'). However, when you use the Precedents property on a cell, it returns all of the cells on the sheet that the cell is dependent upon.

For example, I have cell DT836 with the formula:

=DP836+DR836+DS836

"Trace Precedents" Debug toolbar shows a nice blue arrow connecting those three cells to cell DT836.

Range("DT836").Precedents contains a 70,000+ cell range with (as far as I can tell) no way to distinguish between how distant a dependent is from its precedent.

Is there a way to discern this using VBA?

Answer 1

It turns out there is a different property for ranges that tells you this, DirectPrecidents.

I had thought that it would be a property of the precedents, not a separate property in and of itself.

Answer 2

Alec,

Thank you for closing out your own question. I am posting these comments as an answer rather than a comment given their length

Excel is rather flakey when it comes to 3D functionality (across sheets). Both Precedents and DirectPrecedents work only on the local sheet

To work with off-sheet Precedents and Dependents you will need to use the NavigateArrows Method, this is the VBA equivalent of tracing precedents and dependents via the audit toolbar.

Bill Manville did the heavy lifting on this approach some time back, below are two links applying Bill's approach. The first provides Bill's function, the second link is from me, this article looks "backwards" to test if a certain number eventually links back to a specific sheet

• Bill Manville's FindPrecedents function (written up at vbaexpress)
• My article 'Using a recursive NavigateArrows Method to analyse all local and off sheet cell dependencies' at Ee