Excel有没有内置的公式解析方法？（例如：获取包含的范围引用列表）

Question

针对单元格中给定的Excel公式，我希望能够解析该公式以获取公式中包含的Excel范围引用列表。

例如，如果我有一个包含以下公式的单元格：

= A + 25 + B  

我希望能够获得包含在公式中的Excel范围数组，因此，在这种情况下，它将包含[A]和[B]。你可能会问：“你为什么要这样做？”
只是举个例子，我想这样做是为了查找公式中范围的“标签”......所以，与其只使用CTRL+~查看工作表中的公式，我希望通过编程方式访问公式中的范围引用，以便查找目标范围旁边的标签。
因此，在我的上述示例中，我可以编写类似以下的公式：

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

...这将为我提供公式中第1个和第2个范围左侧的标签。

要实现这一点，必须调用Excel本身已有的一些功能，因为手写公式解析器是一项复杂的任务：
http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html

- tbone

---

VBA可以访问公式的“Precedents”，但仅限于与公式在同一工作表上的那些。有这个：http://www.ozgrid.com/forum/showthread.php?t=17028，但您无法在UDF中使用它，这似乎是您想要的。另请参阅：https://dev59.com/2VXTa4cB1Zd3GeqP5-AA - Tim Williams

йҷӨдәҶTimзҡ„иҜ„и®әд№ӢеӨ–пјҢжӮЁиҝҳеҸҜд»ҘдҪҝз”ЁNavigateArrowsж–№жі•жқҘиҜҶеҲ«и·ЁиЎЁж јеј•з”ЁгҖӮжҲ‘еңЁиҝҷйҮҢдҪҝз”ЁдәҶиҝҷз§Қж–№жі•й“ҫжҺҘпјҢеҲқе§Ӣд»Јз Ғзҡ„еҠҹеҠіеҪ’дәҺBill ManvilleгҖӮ - brettdj

Answer 1

感谢@TimWilliams和@brettdj指引我找到了关于这个主题的先前讨论，我可以自信地说：

否，Excel没有解析的方法。

然而，对于我的相当简单的目的，我想出了一些可行的方法，可以处理跨工作表引用，并且可以从UDF中调用。

然而，它非常脆弱，有许多完全合法的公式，我肯定它无法正确处理。

代码很混乱，但可以大大改进，但我只是想把它放在这里，因为我现在要转移到其他事情上了...

编辑

此外，我还发现了这个，看起来非常有趣：
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

（只需在Google中搜索SplitMultiDelims即可找到该函数）

- tbone

---

https://github.com/spreadsheetlab/XLParser 是一个更专注的努力的例子。 - ivan_pozdeev

非常感谢您。这是一个更新版本， 如果公式具有错误检查和双引号，则兼容性： formula = Replace(formula, """", ""); formula = Replace(formula, "IFERROR", ""); ;; 修复如果公式只有一个范围，即：=B98： 如果IsRange（formula）则resultRanges.Add Trim（formula）;修复结果范围始终具有第一个索引为空： ReDim resultRangeArray（1 To resultRanges.count）; - PalFS

Answer 2

Tbone，有一个不是直接回答你问题但可能作为替代方案的选项。 与其使用公式来尝试找到相应的标签，不如调整你的公式以适应你的需求。以下是一些选项，具体取决于你要解析的公式： 1. 如果你的公式是查找函数，你可以通过偏移量向左查找。 2. 或者你可以在两个公式中都使用“间接”函数，以确保它们引用了正确的位置。

- ExcelJunkie

Answer 3

简而言之，我认为您想要完成以下子任务：使用VBA生成代码以在Excel工作表上重现基本计算，并使用函数返回第n个DirectPrecedents集合元素的地址或名称。

来源：http://www.vb-helper.com/howto_vba_excel_formulas.html

然而，这种用法已经被弃用。从Excel 2007开始，表格提供了更好的解决方案。

- Margus

---

坏消息？：DirectPrecedents属性仅适用于活动工作表，无法跟踪远程引用。（http://msdn.microsoft.com/en-us/library/aa175240(v=office.11).aspx） - tbone

这个（Excel）的另一个限制我相当确定的是它不能从单元格内的公式中调用。 - tbone