Precedents arrows state

[Micromegas]

somerange.ShowPrecedents turns on (one more layer of) its nice blue arrows. Is there a way to find out how many layers are currently shown for somerange?

[Grade4.2]

In VBA, you can use the Range.Dependents property to get a collection of cells that are dependent on a particular cell. To count the number of precedents for each cell, you can use a loop to iterate through each cell in the range and use the Range.Precedents property to get the collection of cells that are precedent to that cell. You can then use the Count property to get the number of precedents for each cell.


Here is an example of how you can do this:



Sub CountPrecedents()
Dim rng As Range
Dim cell As Range
Set rng = ActiveSheet.Range("A1:B10")
For Each cell In rng
On Error Resume Next
Debug.Print cell.Address & ": " & cell.Precedents.Count
Next cell
End Sub

[Micromegas]

Thanks. That's not what i'm asking, though. My question is about the display.

[p45cal]

I think you should be able to do this but I haven't the time to develop a solution for you but I can give some pointers.
Because it's convoluted it would be useful to know whether this is a can't-do-without or just a nice-to-have functionality.
So since this is related to arrows displayed rather than cell precedents we can't use cell.Precedents since the latter is independent of visible precedent/dependent arrows.
So far, I haven't been able to find any property which exposes those arrows in vba, but there is a method in vba: Range.NavigateArrow. Normally you'd use it to activate a precedent/dependent range, but it also returns a range if you use it say like:
Set yyy = ActiveCell.NavigateArrow(TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=1)
This will still activate a precedent range but it will also set yyy to that range. What's more, if there are no arrows this returns the same cell (active cell in this case) so is a way of testing if there are none or you've run out of arrows and or link numbers to follow.
This means you could set up a recursive macro/function which would follow all possible arrows from a cell and all their precedent arrows, counting the maximum (or minimum) depth (level) reached.
The function would have to accommodate single arrows pointing to contiguous ranges, single (dotted) arrows pointing to multiple links external to that sheet, so you'd have to iterate through both arrowNumbers and LinkNumbers.
Finally, bear in mind that although in Excel's user interface you can repeatedly click on Trace Precedents in the ribbon to get progressive levels of precedents, you can also select one of those precedent cells and click Remove Dependent Arrows one by one which would partially break a chain of precedents.

Maybe some kind soul will take the time to explore if you can't do it yourself

[Micromegas]

Thank you! I think Range.NavigateArrow solves my question. It hadn't occurred to me to search the methods, but this isn't the only case in which a method is used as a get function for something one might rather expect to be a simple property. E.g. take the Comment.Text method.