使用R1C1表示法的Excel单元格的正则表达式

Question

我需要一些代码来测试单元格是否包含对另一个单元格的引用公式。

我找到了答案在Excel公式中查找所有已使用的引用，但是该解决方案也会错误地匹配对表列的引用公式，例如：

=SearchValInCol2(Tabella1[articolo];[@articolo];Tabella1[b])

接下来，我使用Like运算符编写了以下VBA代码，但是使用正则表达式的解决方案肯定更加稳固（我认为以下代码在许多场景中都不起作用）。

Private Function TestIfCellContainsAFormula(cellToTest As Variant) As Boolean

    Dim result As Object
    Dim r As Range
    Dim testExpression As String
    Dim objRegEx As Object

    Set r = cellToTest  ' 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.FormulaR1C1)

    ' search for pattern "=R[-3]C+4"
    If testExpression Like "*R[[]*[]]*C*" Then
        TestIfCellContainsAFormula2 = True
        Exit Function
    End If

    ' search for pattern "=RC[2]"
    If testExpression Like "*R*C[[]*[]]*" Then
    'If InStr(1, testExpression, "C[", vbTextCompare) <> 0 Then
        TestIfCellContainsAFormula2 = True
        Exit Function
    End If

    TestIfCellContainsAFormula2 = False

End Function

- Stefano Spinucci

---

https://regex101.com/r/r196Nz/2 - 0m3r

https://regex101.com/r/r196Nz/3 - 0m3r

这是你在寻找的吗？ - 0m3r

Answer 1

选项1

为了匹配R1C1样式的引用，您可以使用this regex：

R(\[-?\d+\])C(\[-?\d+\])|R(\[-?\d+\])C|RC(\[-?\d+\])

请查看铁路图以获得视觉解释：

核心是“offset”，即-?\d+，它是可选的-后跟一个或多个数字。这个序列放在括号（[]）中，以给出\[-?\d+\]。然后正则表达式允许以下组合：

• R[offset]C[offset]
• R[offset]C或（|）
• RC[offset]或（|）

选项2

上面的正则表达式不会匹配R、C或RC。它将匹配R[0]、C[0]、R[0]C、RC[0]和R[0]C[0]等相当的内容。为了消除这些匹配，您可以使用this regex:

R(\[-?[1-9][0-9]*\])C(\[-?[1-9][0-9]*\])|R(\[-?[1-9][0-9]*\])C|RC(\[-?[1-9][0-9]*\])

这是什么：

但是，似乎在我的Excel（v2013）中输入R[0]、C[0]和R[0]C[0]会将它们变成R、C和RC——因此，如果这不是个问题，你可以避免额外的复杂性。

选项3

如果您想允许R、C和RC，您可以使用一个更简单的正则表达式：

R(\[-?\d+\])?C(\[-?\d+\])?

VBA测试代码

这里使用选项 1。

Option Explicit

Sub Test()

    Dim varTests As Variant
    Dim varTest As Variant
    Dim varMatches As Variant
    Dim varMatch As Variant

    varTests = Array("RC", _
        "R[1]C", _
        "RC[1]", _
        "R[1]C[1]", _
        "R[-1]C", _
        "RC[-1]", _
        "R[-1]C[-1]", _
        "=SUM(A1:B2)", _
        "RC[1]+R[-1]C+R[2]C[-99]", _
        "R[-1]C-R[1]C[-44]-RC[999]+R[0]C[0]", _
        "SearchValInCol2(Tabella1[articolo];[@articolo];Tabella1[b])")

    For Each varTest In varTests
        varMatches = FormulaContainsR1C1Reference(CStr(varTest))
        Debug.Print "Input: " & CStr(varTest)
        Debug.Print VBA.String(Len(CStr(varTest)) + 7, "-")

        If IsEmpty(varMatches) Then
            Debug.Print "No matches"
        Else
            Debug.Print UBound(varMatches) & " matches"
            For Each varMatch In varMatches
                Debug.Print varMatch
            Next varMatch
        End If

        Debug.Print vbCrLf
    Next varTest

End Sub

Function FormulaContainsR1C1Reference(ByVal strFormula As String) As Variant

    Dim objRegex As Object
    Dim strPattern As String
    Dim objMatches As Object
    Dim varMatches As Variant
    Dim lngCounter As Long

    Set objRegex = CreateObject("VBScript.RegExp")
    With objRegex
        ' setup regex
        .Global = True
        .IgnoreCase = False
        .Pattern = "R(\[-?\d+\])C(\[-?\d+\])|R(\[-?\d+\])C|RC(\[-?\d+\])"

        ' get matches
        Set objMatches = .Execute(strFormula)

        ' iterate matches
        If objMatches.Count > 0 Then
            ReDim varMatches(1 To objMatches.Count)
            For lngCounter = 1 To objMatches.Count
                varMatches(lngCounter) = objMatches.Item(lngCounter - 1)
            Next lngCounter
        Else
            varMatches = Empty
        End If

    End With

    FormulaContainsR1C1Reference = varMatches

End Function

A1样式引用

我在这里发布了一个正则表达式链接，用于A1样式引用：

^(?:[A-Z]|[A-Z][A-Z]|[A-X][A-F][A-D])(?:[1-9]|[1-9][0-9]|[1-9][0-9][0-9]|[1-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9]|[1-9][0-9][0-9][0-9][0-9][0-9]|10[0-3][0-9][0-9][0-9][0-9]|104[0-7][0-9][0-9][0-9]|1048[0-4][0-9][0-9]|10485[0-6][0-9]|104857[0-6])$

- Robin Mackenzie