Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does Excel have a built in method for parsing formulas? (ie: to obtain a list of included RANGE references)

Tags:

excel

vba

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

like image 312
tbone Avatar asked Jul 03 '12 23:07

tbone


People also ask

How to dynamically reference a cell in Excel?

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

How to refresh array formulas?

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.


1 Answers

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

EDIT

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)

like image 186
tbone Avatar answered Sep 20 '22 07:09

tbone