excel - MS Excel VBA : Find cells that have a specific formula type

Question

我想自动查找包含仅作为简单引用的公式的单元格。例如:
=D20或 ='Sheet1'!D20 .

目的是我想自动对这些单元格进行颜色编码。

VBA 中有没有办法搜索公式类型？

Answer 1

经过一段时间让它沉入其中，我认为这并不是那么简单。直接单元格引用可以包括简单的引用，例如:

=A1

但是在某些情况下，您可以引用另一个工作表中的另一个单元格、另一个打开的工作簿甚至另一个关闭的工作簿。虽然这使得使用 RIGHT 变得困难, LEFT函数甚至 FORMULATEXT对于喜欢，您可以做的一件事是检查模式。

任何直接的单元格引用都不能包含任何其他计算，这意味着它必须排除任何不需要的运算符(/、*、-、+)。就我而言，这意味着如果我们检查一个只有一个 Precedent 的单元格，公式不能以数字开头，也不能以上述任何运算符后的数字结尾。

我们最好的选择是将这些标准放在正则表达式中。我绝不是 RegEx 专家，但我认为以下方法可以解决问题:

"^=.*[A-Z]+\$*\d+$"

行首是 =

后跟任何单词/非单词字符，0-n 次

行尾必须是数字，1-n 次，前面是大写字母字符或与美元符号的组合(用于绝对单元格引用)

只要我们只检查一个 .Precedent，上述方法就可以工作。并检查公式的第二个字符是否不是数字。例如，代码示例将如下所示:

Sub FindFormualaType()

Dim rng1 As Range, rng2 As Range, cl As Range
Dim regex As New RegExp
Dim matches As Object
Dim tststr As String

With regex
    .Global = True
    .Pattern = "^=.*[A-Z]+\$*\d+$"
End With

With Sheet1 'Change to your sheet's codename
    Set rng1 = Intersect(.Cells, .Cells.SpecialCells(-4123))
    For Each cl In rng1
        On Error Resume Next
        If cl.Precedents.Count = 1 Then
            If Left(cl.Formula, 2) Like "=#" = False Then
                Set matches = regex.Execute(cl.Formula)
                If matches.Count = 1 Then
                    If Not rng2 Is Nothing Then
                        Set rng2 = Union(rng2, cl)
                    Else
                        Set rng2 = cl
                    End If
                End If
            End If
        End If
        On Error GoTo 0
    Next cl
End With

rng2.Interior.Color = vbGreen

End Sub

解释上面代码中的一些步骤:

使用 RegExp您必须设置对它的引用:

然后代码使用 .SpecialCells 与 xlCellTypeFormulas ( -4123 )，并创建一个 .Range对象通过 .Intersect .因此，我们不必循环所有单元格来检查 .HasFormula 。 (希望能节省一些时间)。

接下来是新创建的 .Range 中所有单元格的循环。对象并检查 Precendents.Count = 1 .我们需要将其封装成 On Error Resume Next因为如果没有找到先例，它会出错，例如:像 ="Hello" 这样的公式.

当计数等于 1 时，我们检查 Like 运算符，如果我们可以保存地执行正则表达式并测试使用的模式的匹配。

因为我宁愿一次性做一个填充操作，所以建议再创建一个.Range对象 (rng2) 并使用 Union 设置此对象。

最后我们可以使用rng2.Interior.Color = vbGreen为仅直接引用一个单元格的所有单元格着色。

希望有帮助。我绝不是 RegEx 专家，它可能做得更简单。

关于我为什么使用 Like 的一些背景信息运算符(operator)。因为我想确保没有对单元格引用进行计算，所以等式中的第一个字符不能是数字。在 RegEx 中，你得到了一种叫做“lookahead”的东西。虽然，不幸的是，我无法让表达式与 VBA 一起工作(不受支持？)，因此使用了 Like运算符以确保第二个字符不是数字。