Does Excel have a built-in method for parsing formulas? (i.e. get a list of included RANGE links)

Question

For a given Excel formula in a cell, I would like to analyze the formula to get a list of Excel range references contained in the formula.

For example, if I have a cell with this formula:

= A + 25 + B 

.... I would like to be able to get the array of excel ranges contained in the formula, so in this case it will contain [A] and [B]

“Why do you even want to do this?”, I hear you asking:
Only one example of why I want to do this is to look for “labels” for ranges in formulas ..... so, unlike just CTRL + ~ to view formulas in my worksheet, I need the programmatic link access option range in the formula to search for labels near the target range.

So, in my above example, I could write formulas like:

 =Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',1),0,-1) =Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',2),0,-1) 

... that would give me a label to the left of the 1st and 2nd ranges in the formula.

Doing this would cause some functionality already available in Excel itself, since handwriting with a formula parser is a difficult task:
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

Answer 1

Thanks to @TimWilliams and @brettdj for pointing me in the right direction to previous discussions on this topic, I can say with confidence:

NO, EXCEL DOES NOT HOW TO DETERMINE.

However, for my rather minimal goals, I came up with something that works, works with cross-sheet links, and can be called from UDF.

However, it is extremely fragile, and there are many perfectly legal formulas that, I am sure, it will not process properly.

The code is a mess and can be greatly improved, but I just wanted to drop it here while I switch to something else for a while ....

EDIT

Also found this that looks very interesting:
http://www.dailydoseofexcel.com/archives/2009/12/05/formula-tokenizer/

 Public Function CellPrecedents(cell As Range) As Variant() Dim resultRanges As New Collection If cell.Cells.count <> 1 Then GoTo exit_CellPrecedents If cell.HasFormula = False Then GoTo exit_CellPrecedents Dim formula As String formula = Mid(cell.formula, 2, Len(cell.formula) - 1) If IsRange(formula) Then resultRanges.Add Range(formula), 1 Else Dim elements() As String 'Debug.Print formula & " --> " formula = Replace(formula, "(", "") formula = Replace(formula, ")", "") 'Debug.Print formula & " --> " elements() = SplitMultiDelims(formula, "+-*/\^") Dim n As Long, count As Integer For n = LBound(elements) To UBound(elements) If IsRange(elements(n)) Then 'ACTUALLY JUST DO A REDIM PRESERVE HERE!!!! count = count + 1 'resultRanges.Add Range(Trim(elements(n))) '<--- Do **NOT** store as a range, as that gets automatically Eval()'d resultRanges.Add Trim(elements(n)) End If Next End If Dim resultRangeArray() As Variant ReDim resultRangeArray(resultRanges.count) Dim i As Integer For i = 1 To resultRanges.count resultRangeArray(i) = CStr(resultRanges(i)) '// have to store as a string so Eval() doesn't get invoked (I think??) Next CellPrecedents = resultRangeArray exit_CellPrecedents: Exit Function End Function Public Function IsRange(var As Variant) As Boolean On Error Resume Next Dim rng As Range: Set rng = Range(var) If err.Number = 0 Then IsRange = True End Function 

(just google SplitMultiDelims for this function)

Answer 2

Tbone, another option that is not what you requested, but can work as an alternative solution.

Instead of using a formula to try and find a label that responds to corrosion, try tweaking your formulas to work for you. Here are a few options, depending on which formula you are trying to parse. 1. If your formula is a search, you can simply shift to look to the left. 2. Alternatively, you can use the Indirect feature in both formulas to make sure they are referencing the correct location.

Answer 3

In short, I think you want to make sub-parts of Use VBA to generate code to reproduce basic calculations on an Excel worksheet and use the function to return the nth element or element name of the DirectPrecedents collection.

source: http://www.vb-helper.com/howto_vba_excel_formulas.html

However, this use case is out of date. Compared to Excel 2007 tables, a much better solution.