For a given Excel formula in a cell, I'd like to be able to parse the formula in order to get a list of Excel Range references contained within the formula.
For example, if I have a cell with this formula:
= A + 25 + B
....I would like to be able to get an array of excel ranges contained within the formula, so in this case, it would contain [A] and [B]
"Why do you even want to do this"?, I can hear you asking:
Just one example of why I want to do this is to look up "labels" for ranges in formulas.....so, as opposed to just doing a CTRL+~ to view the formulas in my sheet, I'd like the option of programatically accessing the range references within the formula in order to do a lookup of the label beside the target range.
So, in my above example, I could then write formulas something like:
=Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',1),0,-1)
=Offset(CellFormulaRanges('TheAddressMyFormulaIsIn',2),0,-1)
...which would give me the the label to the left of the 1st and 2nd ranges within the formula.
Doing this would have to call upon some functionality already within Excel itself, as hand writing a formula parser is a complicated task:
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1) .
To expand an array formula, i.e. apply it to more cells, select all cells containing the current formula plus empty cells where you want to have it, press F2 to switch to the edit mode, adjust the references in the formula and press Ctrl + Shift + Enter to update it.
Thanks to @TimWilliams and @brettdj for pointing me in the right direction to previous discussions on this topic, I can confidently say:
NO, EXCEL DOES NOT HAVE A METHOD FOR PARSING.
However, for my fairly minimal purposes, I've come up with something that works, works with cross worksheet references, and can be called from a UDF.
However, it is extremely brittle, and there are multitudes of perfectly legitimate formulas that I'm certain it wouldn't handle properly.
The code is a mess and could be greatly improved but I just wanted to throw it up on here as I'm moving onto to something else for the time being....
Also found this, which 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 that function)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With