vba - Find all used references in Excel formula

Question

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.

Answer 1

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).