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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Have a look at the Dependents property, for a completely different approach.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
"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.
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top