Parsing and extracting cell references from Excel formulas?

Question

I am looking to parse and extract cell references from Excel formulas. Let's say I have the following formula inside cell P5:

=SUM(P1:P4)+Q3

I am looking for an output of the location of the cells involved for the formula to produce an answer, i.e. P1, P2, P3, P4, Q3 as output (their location/representation in some other form is acceptable too).

My question is whether there is such kind of parser available to accomplish this and if not, what technique I should adopt. Perhaps the formula I have listed as example is quite simple, I would ideally like to cover all potential formulas, maybe even those that have a reference across other sheets and workbooks. I expected and was really hoping for a VSTO C# based solution but I'm having a really difficult time finding one or even relevant functionality in the VSTO library.

Answer 1

For simple formulas:

Sub PrettyPoorParser()
    Dim r As Range, rr As Range
    With Range("D4")
        Set r = .DirectPrecedents
        msg = r.Count
        For Each rr In r
            msg = msg & vbCrLf & rr.Address(0, 0)
        Next rr
    End With

    MsgBox msg
End Sub

Sadly, this simple approach will not work with all formulas. It is easily fooled by the use of INDIRECT() and by off-sheet references.

EDIT#1:

Rather than using a recursive descent parser, it uses a property of the Range Object. This property is pretty good, but not comprehensive. With regard to running this code in the VBA-EXCEL environment:

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Years ago, I started working on a real parser (as a debug tool), but got pulled off that assignment.

Answer 2

A complete solution to finding precedents from a formula is challenging: even Excel's precedents tool does not do a good job of this. For a starting point using c# try https://github.com/spreadsheetlab/XLParser