Parsing formulas for cell references

[bill]

Hello,

Wondering if our loving community has a way to parse a formula that will extract cell reference (or even named references).

For example, with the following:
=IF(DRS!K106<0,-DRS!K106,0)

I'd like to pull out this element:
DRS!K106

or in this formula:
=ABS(DRS!K58+DRS!K60)

I'd like to pull out
DRS!K58
DRS!K60

The true objective is to then scour (in this case) the DRS tab for a Named Range and then later to replace the relative (or absolute) references for the named range version.

It seems relatively easy (at least from the example above) to parse the string by "(", ")", "<", ">", "=", "+", "/", "-", " " and anything else would be considered a candidate for a cell reference.

I'd then look for a 'DRS!' in the string and if found would know that I have an offpage reference. For that matter I could look for a '!' and the scour all my worksheets for what ever preceeded the bang.

My code looks thus far:

Private Sub ReplaceA1withNameR()
Dim cl As Range
Dim rg As Range

Dim a       As Integer ' a for array position since a string is really an array:)
Dim c       As String
Dim formula As String

Dim A1 As String
  Set rg = Selection

  For Each cl In rg
    If Left(cl.formula, 1) = "=" Then
      formula = Mid(cl.formula, 2)
      For a = 1 To Len(formula)
        c = Mid(formula, a, 1)
    
        Select Case c
          Case ",", "(", ")", "<", ">", "=", "+", "/", "-", " "
            If InStr(1, A1, "DRS!") > 0 Then
              Debug.Print cl.Address(False, False) & ": " & A1
            End If
            A1 = ""
          Case Else
            A1 = A1 & c
        End Select
      Next a
      If Len(A1) > 0 Then
        If InStr(1, A1, "DRS!") > 0 Then
          Debug.Print cl.Address(False, False) & ": " & A1
        End If
        A1 = ""
      End If
    End If
  Next
End Sub

It does appear to parse out cell references OK but wondering if there is a better way.
(I'llbe adding new characters to the SELECT CASE as I 'discover' them
Your thoughs?

 

[GlennUK]

Have a look at the Dependents property, for a completely different approach.

I think that should be the Precedents property. But, unfortunately, that doesn't return external references (to another sheet). I was inclined to go with the ApplyNames method, but that also excludes external references.

D'Oh, of course it should be Precedents. Didn't remember about the external references not being returned ... I don't use these properties much as Aaron Blood's Explode does everything I need in that direction:

http://www.xl-logic.com/