Parsing formulas for cell references

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
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:

Code:
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Have a look at the Dependents property, for a completely different approach.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Hmm, sounds interesting.

Perhaps i could find an empty cell on the source sheet (found by way of the '!'), dump the formula into it then look at 'Precedents'.

That could get nasty though and dump me into the fires of recursion.
 

sefMI

Board Regular
Joined
Apr 24, 2006
Messages
127

ADVERTISEMENT

I think I have the same issue. What if you simply want to set a variable equal to the array name in a vlookup. say =vlookup(a1,xyz,2,false)
make X = xyz so that the array can be referenced in a new formula.
 

sefMI

Board Regular
Joined
Apr 24, 2006
Messages
127

ADVERTISEMENT

"xyz" I used the Define Name tool to name range A5:B10 "xyz". I use "xyz" in my lookup formula, instead of "A5:b10", to reference the range. Now I'd like to have a macro that pulls the name "xyz" out of the formula so that I can insert another lookup formula.
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
Here's a halfazz solution:
Code:
Option Explicit
___________________________________________________________________________________________
Private Sub ReplaceA1withNameRg()
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)
      A1 = ""
      For a = 1 To Len(formula)
        c = Mid(formula, a, 1)
    
        Select Case c
          Case "=", ",", "(", ")", "<", ">", "=", "+", "/", "-", " "
            theTest cl, formula, A1
          Case Else
            A1 = A1 & c
        End Select
      Next a
      If Len(A1) > 0 Then
        theTest cl, formula, A1
      End If
    End If
  Next
End Sub
___________________________________________________________________________________________
Private Sub theTest(cl As Range, ByRef formula As String, ByRef A1 As String)
Dim nm As Name
Dim tg As Range

  If InStr(1, A1, "DRS!") > 0 Then
  
    For Each nm In DRS.Names
      If InStr(1, nm.RefersTo, "!") > 0 Then
        Set tg = DRS.Range(Mid(nm.RefersTo, 2))
      
        If Mid(A1, InStr(1, A1, "!") + 1) = tg.Address(False, False) Then
          Debug.Print cl.Address(False, False) & ": " & " Old: " & formula
          Debug.Print cl.Address(False, False) & ": " & " New: " & Replace(formula, A1, nm.Name)
        End If
      End If
    Next
  End If
  A1 = ""
End Sub

In place of DEBUG.PRINT's I'll instead change the value of the formula to the 'replaced' version (but of course that affects the loop so gotta be careful! - halfazz eh? :rolleyes: )

Please, save me from myself!!!! :LOL:
 

bill

Well-known Member
Joined
Mar 7, 2002
Messages
550
This solutions DOES work for me but of course consider that, in my problem, all external references to other sheets derive from the DRS (codename) tab.

So, my solution is quite specific to my needs.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top