Range methods: NavigateArrow, precedents, dependents 1

[tigrek]

If you have a handy subroutine using the range methods (NavigateArrow, Precedents, Dependents), could you possibly post the sub here?
We would all appreciate it.

 

[Mala]

Could you be more specific?

 

[tigrek]

Any working sub example that includes NavigateArrow method of Range Object would be a help.
Like:

myRange2.showprecedents
precORdependents = 1
set myRange1= myRange2.NavigateArrow(precORdependents, arrowNo, LinkNo)

debug.print myRange1.Formula
debug.print myrange1.Address(External:=1)

give the cells referred to in the formula of myRange2, which is what I need.

The aim here is to dump all the formulas in a worksheet, their precedents and their dependents.
precORdependent = 1 traces precedents, =0 traces dependents.

What I cannot find specifically is how to ask for number of links per arrow.

Thanks

 

[Mala]

Mala (Computer) Aug 23, 2001
Tigrek, sorry I'm a bit late.

When limits are indefinite, it's a good idea to set up loops with preposterously high upper-bounds and sit back comfortably while the VBA 'On Error Resume Next' statement handles things - may not be very elegant, but gets the work done:

To test the code for your problem I did the following:

1. Created a formula in cell F6 on a sheet (can be any cell, but the code below refers to this - the cell has been named 'TestRng').

2. This formula had ALL sorts of references - same worksheet, other worksheets in same workbook AND other Workbooks.

Taking off from your code, I wrote the following Sub:

Sub MyPrecedents()
RoOff = -1
Set TestRng = Range("TestRng&quot
Application.Goto Reference:=TestRng, Scroll:=True
TestRng.ShowPrecedents
For Arr = 1 To 20
For Lnk = 1 To 20
On Error Resume Next
Application.Goto Reference:=TestRng, Scroll:=True
Set PrecRng = TestRng.NavigateArrow(True, Arr, Lnk)
If Err = 0 Then
KK = PrecRng.Address(External:=True)
MatchFound = 0
MatchFound = WorksheetFunction.Match(KK, Columns("G&quot.Cells, 0)
If MatchFound = 0 Then
RoOff = RoOff + 1
TestRng.Offset(RoOff, 1) = KK
End If
End If
Next
Next
End Sub

Note that I set Arr to 20 and Lnk per Arr to 20 - Nobody in their right mind would write such a long formula - so it's a safe bet it'll cover ALL references.

The references are 'dumped' in the col. beside TestRng starting from TestRng.Row

To modify for Dependents, modify the statement:
Set PrecRng = TestRng.NavigateArrow(True, Arr, Lnk)
and
TestRng.Offset(RoOff, 1) = KK

TO

Set DepRng = TestRng.NavigateArrow(False, Arr, Lnk)
+ all refs to PrecRng to DepRng
and
TestRng.Offset(RoOff, 2) = KK to get the refs in the next col.

Of course, u'll have to modify the code to suit the way your worksheet is set up.

By the way, this was a good one - and the most satisfying part is the real effort you'd put in before posting the query on the forum.

Good Luck!

 

[tigrek]

Thanks

It worked beautiful.

 

Mala-

This looks like it works, but I think it works because you are keeping track of which cells you've visited in the result column and checking each visited cell against that (with the .Match), rather than because of the On Error Resume Next statement. I think that the spirit of the original question (and of *my* question!!) is how to traverse that list to the end and the stop *without* having to keep track of where you've been!!

I'm working on a similar problem, except that I want to traverse the list and at each cell in the list, repeat the traverse, recursively, and I can't figure out how to stop!!

Here's what I have so far (assuming the set of cells whose formula's I want to list is in TestRng):

***********

Public depth, fnum

Dim oCell As Range

Sub showSome(oCell As Range)
Dim aCell As Range

For i = 0 To depth
Print #fnum, Spc(1);
Next i

If oCell.HasFormula Then
Print #fnum, oCell.Address(external:=True); Spc(1); oCell.Formula
Else
Print #fnum, oCell.Address(external:=True)
End If


If oCell.HasFormula Then
depth = depth + 1
oCell.ShowPrecedents (False)

For i = 1 To 20
Set aCell = oCell.NavigateArrow(True, i)
showSome oCell:=aCell
Next

oCell.ShowPrecedents (True)
End If

depth = depth - 1
End Sub

Sub showAllContents()
Dim oStart As Range
theDir = ActiveWorkbook.Path
ChDrive (Left(theDir, 1))
ChDir (theDir)
fnum = FreeFile()
Open theDir & "\sheet.txt" For Output Access Write As #fnum

For Each oSht In Worksheets
oSht.ClearArrows
Next

depth = 0
For Each oStart In Range("TestRng&quot
showSome oCell:=oStart
Next
Close fnum
End Sub


*********

So the idea is that ShowAllContents() calls ShowSome() for each cell in the range, and that ShowSome() calls itself recursively until there are no links left. It looks like what happens, is that if there are, say, 3 links from a given cell, then on NavigateArrow(True,4) you end up at the same place as on NavigateArrow(True,1), which isn't so great.... any ideas? Is this totally unclear??

Thanks!