Tracing Cell Precedents in VBA (Finding formulae t

dkabambe · Nov 21st 2003

I want to be able to determine if a particular cell has precedents on another sheet, or in another workbook. I had hoped to use .Precedents and do something like comparing "testCell.Precedents.Count" with "Intersect(Cells,testCell.Precedents).Count", or looking for "!" in testCell.Precedents.Address.

However the .Precedents range only contains precedents on the same sheet, so this doesn't work. I have tried a couple of other tricks but so far can't find a catch-all workaround, (mainly because I some of the formula reference named cells on other sheets).

Any help would be greatly appreciated, Dzinja.

**Andy Pope** · Nov 21st 2003

Hi Dzinja,

Try this routine,

Code

Sub FindPrecedents()
' written by Bill Manville
' with edits from PaulS
  ' this procedure finds the cells which are the direct precedents of the active cell
  Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
  Dim stMsg As String
  Dim bNewArrow As Boolean
  Application.ScreenUpdating = False
  ActiveCell.ShowPrecedents
  Set rLast = ActiveCell
  iArrowNum = 1
  iLinkNum = 1
  bNewArrow = True
  Do
    Do
      Application.Goto rLast
      On Error Resume Next
      ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
      If Err.Number &gt; 0 Then Exit Do
      On Error GoTo 0
      If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
      bNewArrow = False
      If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
        If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
          ' local
          stMsg = stMsg & vbNewLine & Selection.Address
        Else
          stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
        End If
      Else
        ' external
        stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
      End If
      iLinkNum = iLinkNum + 1  ' try another link
    Loop
    If bNewArrow Then Exit Do
    iLinkNum = 1
    bNewArrow = True
    iArrowNum = iArrowNum + 1  'try another arrow
  Loop
  rLast.Parent.ClearArrows
  Application.Goto rLast
  MsgBox "Precedents are" & stMsg
  Exit Sub
End Sub

Display More

Cheers
Andy

Richie(UK) · Nov 21st 2003

Andy - now that's spooky, that was the exact routine that I was going to post! :o (Guess its something that doesn't come up that often)

d - you may also like to look at the code in Stephen Bullen's FindCirc routine here :
http://www.bmsltd.co.uk/Excel/Default.htm

dkabambe · Nov 21st 2003

As it happens there is a much easier way to do what I was trying to do which side-steps needing to know the precedents. However, it is good to know in case I need it in future.

Many thanks, Dzinja

Tracing Cell Precedents in VBA (Finding formulae t

Participate now!

Share