Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find all used references in Excel formula

Tags:

excel

vba

Below is the example set in Excel,

[column1] [column2]

A1  =C3-C5

A2  =((C4-C6)/C6)

A3  =C4*C3

A4  =C6/C7

A5  =C6*C4*C3

I need to extract the used references in formulas

For example,

for "A1", I simply need to get the C3 and C5.
for A2, I need to get the C4 and C6.
like image 974
Pon Avatar asked Dec 12 '12 08:12

Pon


People also ask

How do I see all references in Excel?

On the Formulas tab, in the Defined Names group, click Name Manager. Check each entry in the list, and look in the Refers To column for external references. External references contain a reference to another workbook, such as [Budget.


1 Answers

This is an update to:

Will work for local sheet references, but not for references off-sheet. – brettdj May 14 '14 at 11:55

By Using Larrys method, just change the objRegEx.Pattern to:

(['].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))

This will:

  1. Search for optional External links: (['].*?['!])?
  2. Search for optional Sheet-reference: ([[A-Z0-9_]+[!])?
  3. Do the following steps in prioritized order:
  4. Search for ranges with row numbers (And optional $): \$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?
  5. Search for ranges without row numbers (And optional $): \$?[A-Z]+:\$?[A-Z]+
  6. Search for 1-cell references (And optional $): (\$?[A-Z]+\$?(\d)+)

Resulting in this:

Sub testing()
Dim result As Object
Dim r As Range
Dim testExpression As String
Dim objRegEx As Object

Set r = Cells(1, 2)  ' INPUT THE CELL HERE , e.g.    RANGE("A1")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*?"""  ' remove expressions
testExpression = CStr(r.Formula)
testExpression = objRegEx.Replace(testExpression, "")
objRegEx.Pattern = "(([A-Z])+(\d)+)"  'grab the address

objRegEx.Pattern = "(['].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))"
If objRegEx.test(testExpression) Then
    Set result = objRegEx.Execute(testExpression)
    If result.Count > 0 Then
        For Each Match In result
            Debug.Print Match.Value
        Next Match
    End If
End If
End Sub

Doing this, will give you the values of all possible references, I could think of. (Updated this post, because I needed the problem solved).

like image 158
Michael Møldrup Avatar answered Sep 25 '22 15:09

Michael Møldrup