vba Excel 是否具有用于解析公式的内置方法？（即：获取包含的 RANGE 参考列表）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11320626/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does Excel have a built in method for parsing formulas? (ie: to obtain a list of included RANGE references)
提问by tbone
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.
对于单元格中的给定 Excel 公式，我希望能够解析该公式，以获得公式中包含的 Excel 范围引用列表。
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]
....我希望能够获得包含在公式中的一系列 excel 范围，因此在这种情况下，它将包含 [A] 和 [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.
“你为什么要这样做”？，我可以听到你在问：我为什么要这样做的
一个例子是在公式中查找范围的“标签”......所以，而不是仅仅执行 CTRL+~ 以查看我的工作表中的公式，我想要以编程方式访问公式中的范围引用的选项，以便在目标范围旁边查找标签。
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
这样做必须调用 Excel 本身中已有的一些功能，因为手写公式解析器是一项复杂的任务：http:
//ewbi.blogs.com/develops/2004/12/excel_formula_p.html
采纳答案by tbone
Thanks to @TimWilliams and @brettdj for pointing me in the right direction to previous discussions on this topic, I can confidently say:
感谢@TimWilliams 和@brettdj 为我指出了之前关于这个主题的讨论的正确方向，我可以自信地说：
NO, EXCEL DOES NOT HAVE A METHOD FOR PARSING.
不，Excel 没有解析方法。
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.
但是，出于我相当小的目的，我想出了一些有效的方法，可以使用跨工作表引用，并且可以从 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/
还发现了这个，看起来很有趣：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)
（只是谷歌 SplitMultiDelims 为该功能）
回答by ExcelJunkie
Tbone, One other option that is not directly what you asked for but could work as alternative solution.
Tbone，另一种不是您要求的直接选择，但可以作为替代解决方案。
Rather than using a formula to try and find the corrosponding label, Try adjusting your formulas to work for you. Here are a couple of options depending on what the formula you were trying to parse is. 1. If your formula is a lookup you could just offset to look to the left. 2. Alternatively you could use the "Indirect" function in both formulas to ensure they are referencing the correct location.
与其使用公式来尝试查找对应的标签，不如尝试调整您的公式以使其适合您。根据您尝试解析的公式，这里有几个选项。1. 如果你的公式是一个查找，你可以偏移到左边看。2. 或者，您可以在两个公式中使用“间接”函数来确保它们引用正确的位置。
回答by Margus
In short, I think you want to do subpart of : Use VBA to generate code to reproduce basic calculations on an Excel worksheet, and using a function to return n'th DirectPrecedents collection element address or name.
简而言之，我认为您想做 : 的子部分Use VBA to generate code to reproduce basic calculations on an Excel worksheet，并使用函数返回第 n 个 DirectPrecedents 集合元素地址或名称。
source: http://www.vb-helper.com/howto_vba_excel_formulas.html
来源：http: //www.vb-helper.com/howto_vba_excel_formulas.html
This use case however has been deprecated. As of Excel 2007 tables allow a much better solution.
但是，此用例已被弃用。从 Excel 2007 表格开始，可以提供更好的解决方案。
