在VBA中搜索单元格引用的公式 [英] Search formula for cell references in VBA
问题描述
在VBA中，我想搜索一个excel公式(字符串)以查找单元格引用.
Within VBA, I want to search an excel formula (String) to find cell references.
具体来说，我想在字符串中找到相对单元引用(任何相对单元引用，而不是特定单元)或混合单元引用的位置.
Specifically I want to find the position(s) in the string where there is either a relative cell reference (ANY relative cell reference, not a specific one), or a mixed cell reference.
- 我不需要查找绝对单元格引用，尽管我可以检查并忽略它们.
- 我不介意参考文献是否在另一张纸上，我很高兴在！之后找到一点.符号.
- 我将需要确保它不会在字符串或工作表名称或数据名称中找到看起来像单元格引用的内容.
我该怎么做?(这是主要问题)
我的想法:
我可以看到如何找到混合单元格引用-我很确定$符号只能出现在混合单元格引用，绝对引用，内部工作表名称或内部字符串中(任何人都可以确认吗?) ，因此使用正确的正则表达式或算法，您可以找到所有这些情况，然后只需检查它是否是绝对单元格引用，然后忽略它即可.
I can see how I would find mixed cell references - I'm pretty sure the $ symbol can only ever come up in either mixed cell references, absolute references, inside sheet names, or inside strings (can anyone confirm that?), so with the right regular expression or algorithm you could find all those cases and then just check if it is an absolute cell reference and ignore that.
但是如何找到所有相关的单元格引用呢?我唯一的想法与上述类似.除单元格引用外，Excel公式中(表名之外或字符串内)是否曾经有一个字母后面有数字的时间?任何公式名称或其他名称?我唯一能想到的就是定义的数据名称，但是我不确定是否可以在公式(尤其是条件格式公式)中使用它们.谁能想到其他时间吗?
But how to find any and all relative cell references? The only idea I have is similar to the above. Beyond cell references, are there ever any times in Excel Formulas (outside of sheet names or inside strings) where there is a number following a letter? Any formula names or anything? The only other one I can think of is in a defined data name, but I'm not sure if you can use those in a formula, especially a Conditional Formatting formula). Can anyone think of any other times?
有人有什么主意吗?
推荐答案
我不确定您的用例是什么，但是您可以尝试使用此函数进行一些尝试:
I wasn't sure what your use case is, but you could try something along the lines in this function:
该项目使用 早期绑定 -您必须设置对以下内容的引用:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=\s])((?:\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?|" & _
"\$?[a-z]{1,3}:\$?[a-z]{1,3}|\$?\d+:\$?\d+))(?:$|[\s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
此函数可以接受两种不同的数据类型:
This function can accept two different data-types:
- 范围
- 字符串
这使您可以将其用作工作表函数来测试公式的文本值，也可以将其直接用于测试输入字符串.
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
以下是检查单元格的返回信息:
Here's the return checking a cell:
这是上面的公式:
这也可以在VBA中使用:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
打破正则表达式: Regex101
Breaking down the Regular Expression: Regex101
(?:^|[,!(=\s])((?:\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?|\$?[a-z]{1,3}:\$?
[a-z]{1,3}|\$?\d+:\$?\d+))(?:$|[\s,)])
-
(?:^|[,(=\s])要求在匹配之前之前发生以下情况之一-
^字符串的开头；或 - 一个字符，可以是
-
,逗号(在公式中有用) -
!感叹号(用于Sheet!引用) -
(左括号(在公式中有用) -
=文字等号(在公式中有用) -
\s空格字符 (?:^|[,(=\s])Requires one of the following to occur before your match^Start of string; or- A single character which is either
,a comma (useful in formulas)!an exclamation (forSheet!references)(opening parenthesis (useful in formulas)=literal equal sign (useful in formulas)\sa whitespace character-
\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?并非整行/整列-
\$获取绝对引用(列)，然后按?使其成为可选
任意字母的 -
[A-Z]字符类，一次或多次+ -
\$获取绝对引用(行)，然后跟随?使其成为可选 -
\d任何数字，+一次或多次 -
(?:...)非捕获组以匹配范围范围(例如A1:B1)- 这使用与上面相同的方法
- 其次是
?，使整个非捕获组都是可选的
\$?[A-Z]{1,3}\$?\d+(?::\$?[A-Z]{1,3}\$?\d+)?not entire row / column\$for an absolute reference (column), followed by?making it optional[A-Z]character class for any letter,+one or more times\$for an absolute reference (row), followed by?making it optional\dany digit,+one or more times(?:...)non capturing group to match a range of ranges (such asA1:B1)- This uses the same methods as above
- Followed by
?, making the entire non-capturing group optional
-
$字符串结尾；或 - 单个字符，是以下字符之一
-
\s空格字符 -
,逗号 -
)右括号
$end of string; or- A single character which is one of
\sa whitespace character,a comma)closing parenthesis
积分:
tripleee 的建议:
-使用字符类[xyz]代替OR语句(?:x|y|z)
-更好的击穿压痕
-语法用法
Makyen的建议:
-支持整行1:4和列A:C
-通过检查Excel的最大列限制[a-z]{1,3}(而不是[a-z]+)Credits:
Suggestions by tripleee:
- Use Character class[xyz]instead ofORstatements(?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows1:4and columnsA:C
- Limit FPs by checking Excel's max column limitation[a-z]{1,3}(instead of[a-z]+)这篇关于在VBA中搜索单元格引用的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
-
-
-
-
