vba 在 Excel 公式中查找所有使用的引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13835466/
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
Find all used references in Excel formula
提问by Pon
Below is the example set in Excel,
下面是在 Excel 中设置的示例，
[column1] [column2]
A1 =C3-C5
A2 =((C4-C6)/C6)
A3 =C4*C3
A4 =C6/C7
A5 =C6*C4*C3
I need to extract the used references in formulas
我需要提取公式中使用的引用
For example,
例如，
for "A1", I simply need to get the C3 and C5.
for A2, I need to get the C4 and C6.
回答by Michael M?ldrup
This is an update to:
这是对以下内容的更新：
Will work for local sheet references, but not for references off-sheet. – brettdj May 14 '14 at 11:55
将适用于本地工作表参考，但不适用于表外参考。– brettdj 2014 年 5 月 14 日 11:55
By Using Larrys method, just change the objRegEx.Pattern to:
通过使用 Larrys 方法，只需将 objRegEx.Pattern 更改为：
(['].*?['!])?([[A-Z0-9_]+[!])?($?[A-Z]+$?(\d)+(:$?[A-Z]+$?(\d)+)?|$?[A-Z]+:$?[A-Z]+|($?[A-Z]+$?(\d)+))
This will:
这会：
- Search for optional External links:
(['].*?['!])? - Search for optional Sheet-reference:
([[A-Z0-9_]+[!])? - Do the following steps in prioritized order:
- Search for ranges with row numbers (And optional $):
\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)? - Search for ranges without row numbers (And optional $):
\$?[A-Z]+:\$?[A-Z]+ - Search for 1-cell references (And optional $):
(\$?[A-Z]+\$?(\d)+)
- 搜索可选的外部链接：
(['].*?['!])? - 搜索可选的工作表参考：
([[A-Z0-9_]+[!])? - 按优先顺序执行以下步骤：
- 用行号搜索范围（和可选的 $）：
\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)? - 搜索没有行号的范围（和可选的 $）：
\$?[A-Z]+:\$?[A-Z]+ - 搜索 1-cell 引用（和可选的 $）：
(\$?[A-Z]+\$?(\d)+)
Resulting in this:
结果是这样：
Sub testing()
Dim result As Object
Dim r As Range
Dim testExpression As String
Dim objRegEx As Object
Set r = Cells(1, 2) ' INPUT THE CELL HERE , e.g. RANGE("A1")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*?""" ' remove expressions
testExpression = CStr(r.Formula)
testExpression = objRegEx.Replace(testExpression, "")
objRegEx.Pattern = "(([A-Z])+(\d)+)" 'grab the address
objRegEx.Pattern = "(['].*?['!])?([[A-Z0-9_]+[!])?($?[A-Z]+$?(\d)+(:$?[A-Z]+$?(\d)+)?|$?[A-Z]+:$?[A-Z]+|($?[A-Z]+$?(\d)+))"
If objRegEx.test(testExpression) Then
Set result = objRegEx.Execute(testExpression)
If result.Count > 0 Then
For Each Match In result
Debug.Print Match.Value
Next Match
End If
End If
End Sub
Doing this, will give you the values of all possible references, I could think of. (Updated this post, because I needed the problem solved).
这样做，会给你所有可能引用的值，我能想到的。（更新了这篇文章，因为我需要解决这个问题）。
回答by Peter Albert
This function returns you a comma separated list of source cells (precedents):
此函数返回一个逗号分隔的源单元格列表（先例）：
Function References(rngSource As Range) As Variant
Dim rngRef As Range
Dim strTemp As String
On Error Resume Next
For Each rngRef In rngSource.Precedents.Cells
strTemp = strTemp & ", " & rngRef.Address(False, False)
Next
If Len(strTemp) 0 Then strTemp = Mid(strTemp, 3)
References = strTemp
End Function
However, please note that you cannot use this as a UDF in the worksheet, as rngRef.Addressunfortunately causes a circular reference. However, you can use it in a small procedure to populate another column, e.g.
但是，请注意，您不能将其用作工作表中的 UDF，因为很rngRef.Address遗憾会导致循环引用。但是，您可以在一个小程序中使用它来填充另一列，例如
Sub ShowPrecedents()
Dim rng As Range
'Will paste precedents of A1:A6 into D1:D6
For Each rng In Range("D1:D6")
rng.Value = References(rng.Offset(, -3))
Next
End Sub
回答by Larry
Just to provide you an alternative... NOTE THAT THIS will return duplicate result if the cells are called more than once
只是为了给您提供一个替代方案...请注意，如果多次调用单元格，这将返回重复的结果
Sub testing()
Dim result As Object
Dim r As Range
Dim testExpression As String
Dim objRegEx As Object
Set r = Cells(1, 2) ' INPUT THE CELL HERE , e.g. cells("A1")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*""" ' remove expressions
testExpression = CStr(r.Formula)
testExpression = objRegEx.Replace(testExpression, "")
objRegEx.Pattern = "(([A-Z])+(\d)+)" 'grab the address
If objRegEx.test(testExpression) Then
Set result = objRegEx.Execute(testExpression)
If result.Count > 0 Then
For Each Match In result
Debug.Print Match.Value
Next Match
End If
End If
End Sub
Results are stored in "Match.Value"
结果存储在“Match.Value”中
