Range methods: NavigateArrow, precedents, dependents
Range methods: NavigateArrow, precedents, dependents
(OP)
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.
We would all appreciate it.





RE: Range methods: NavigateArrow, precedents, dependents
RE: Range methods: NavigateArrow, precedents, dependents
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
RE: Range methods: NavigateArrow, precedents, dependents
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")
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").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!
RE: Range methods: NavigateArrow, precedents, dependents
It worked beautiful.
RE: Range methods: NavigateArrow, precedents, dependents
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")
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!