regex - 这是用于匹配 Excel 公式中任何单元格引用的正则表达式吗？

Question

我一直在尝试创建一个匹配任何 Excel 公式中的任何引用的正则表达式模式，包括绝对引用、相对引用和外部引用。我需要返回整个引用，包括工作表和工作簿名称。

我无法找到有关 Excel A1 符号的详尽文档，但通过大量测试，我确定了以下内容:

公式前面带有等号“=”

公式中的字符串用双引号括起来，需要在查找真正的引用之前删除，否则 =A1&"A1"会破坏正则表达式

工作表名称最多可包含 31 个字符，不包括\/? * [ ] :

外部引用中的工作表名称必须以 bang =Sheet1!A1 结尾

外部引用中的工作簿名称必须用方括号括起来 =[Book1.xlsx]Sheet1!A1

工作簿路径(如果引用是对已关闭工作簿中的范围的引用，则 Excel 添加)始终用单引号括起来，并位于工作簿名称的括号左侧 'C:\[Book1.xlsx]Sheet1'!A1

某些字符(例如，不间断空格)会导致 Excel 将工作簿和工作表名称用单引号括在外部引用中，但我不知 Prop 体是哪些字符 ='[Book 1.xlsx]Sheet 1'!A1

即使启用 R1C1 表示法，Range.Formula仍然以 A1 表示法返回引用。 Range.FormulaR1C1以 R1C1 表示法返回引用。

3D 引用样式允许在一个工作簿上使用一系列工作表名称 =SUM([Book5]Sheet1:Sheet3!A1)

可以在公式中指定命名范围:

名称的第一个字符必须是字母、下划线字符 (_) 或反斜杠 (\)。名称中的剩余字符可以是字母、数字、句点和下划线字符。

您不能使用大写和小写字符“C”、“c”、“R”或“r”作为定义的名称，因为它们都用作为当前选定的单元格选择行或列的简写，当您在名称或转到文本框中输入它们。

名称不能与单元格引用相同，例如 Z$100 或 R1C1。

空格不允许作为名称的一部分。

名称最长可达 255 个字符。

名称可以包含大写和小写字母。 Excel 不区分名称中的大小写字符。

这是我想出的用 VBA 程序进行测试的方法。我也更新了处理名称的代码:

Sub ReturnFormulaReferences()

    Dim objRegExp As New VBScript_RegExp_55.RegExp
    Dim objCell As Range
    Dim objStringMatches As Object
    Dim objReferenceMatches As Object
    Dim objMatch As Object
    Dim intReferenceCount As Integer
    Dim intIndex As Integer
    Dim booIsReference As Boolean
    Dim objName As Name
    Dim booNameFound As Boolean

    With objRegExp
        .MultiLine = True
        .Global = True
        .IgnoreCase = True
    End With

    For Each objCell In Selection.Cells
        If Left(objCell.Formula, 1) = "=" Then

            objRegExp.Pattern = "\"".*\"""
            Set objStringMatches = objRegExp.Execute(objCell.Formula)

            objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
            & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
            & "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
            & "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
            & "|[a-z]{1,3}\:[a-z]{1,3}" _
            & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
            & "|[0-9]{1,7}\:[0-9]{1,7}" _
            & "|[a-z_\\][a-z0-9_\.]{0,254})"
            Set objReferenceMatches = objRegExp.Execute(objCell.Formula)

            intReferenceCount = 0
            For Each objMatch In objReferenceMatches
                intReferenceCount = intReferenceCount + 1
            Next

            Debug.Print objCell.Formula
            For intIndex = intReferenceCount - 1 To 0 Step -1
                booIsReference = True
                For Each objMatch In objStringMatches
                    If objReferenceMatches(intIndex).FirstIndex > objMatch.FirstIndex _
                    And objReferenceMatches(intIndex).FirstIndex < objMatch.FirstIndex + objMatch.Length Then
                        booIsReference = False
                        Exit For
                    End If
                Next

                If booIsReference Then
                    objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                    & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
                    & "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
                    & "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
                    & "|[a-z]{1,3}\:[a-z]{1,3}" _
                    & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
                    & "|[0-9]{1,7}\:[0-9]{1,7})"
                    If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'reference is not A1
                        objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                        & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)" _
                        & "[a-z_\\][a-z0-9_\.]{0,254}$"
                        If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'name is not external
                            booNameFound = False
                            For Each objName In objCell.Worksheet.Parent.Names
                                If objReferenceMatches(intIndex).Value = objName.Name Then
                                    booNameFound = True
                                    Exit For
                                End If
                            Next
                            If Not booNameFound Then
                                objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                                & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)"
                                For Each objName In objCell.Worksheet.Names
                                    If objReferenceMatches(intIndex).Value = objRegExp.Replace(objName.Name, "") Then
                                        booNameFound = True
                                        Exit For
                                    End If
                                Next
                            End If
                            booIsReference = booNameFound
                        End If
                    End If
                End If

                If booIsReference Then
                    Debug.Print "  " & objReferenceMatches(intIndex).Value _
                    & " (" & objReferenceMatches(intIndex).FirstIndex & ", " _
                    & objReferenceMatches(intIndex).Length & ")"
                End If
            Next intIndex
            Debug.Print

        End If
    Next

    Set objRegExp = Nothing
    Set objStringMatches = Nothing
    Set objReferenceMatches = Nothing
    Set objMatch = Nothing
    Set objCell = Nothing
    Set objName = Nothing

End Sub

任何人都可以打破或改善这一点吗？如果没有关于 Excel 公式语法的详尽文档，很难知道这是否正确。

谢谢!

Answer 1

jtolle 引导我朝着正确的方向前进。据我所知，这就是我想要做的。我一直在测试，它似乎工作。

stringOriginFormula = rangeOrigin.Formula
rangeOrigin.Cut rangeDestination
rangeOrigin.Formula = stringOriginFormula

谢谢jtolle!