Действительно ли это - RegEx для соответствия какой-либо ссылке на ячейку в Формуле Excel?

Я пытался создать шаблон регулярных выражений, который соответствует любой ссылке в любой Формуле Excel, включая абсолютные, относительные, и внешние ссылки. Я должен возвратить всю ссылку, включая имя рабочей книги и рабочий лист.

Я не смог найти исчерпывающую документацию о A1-нотации Excel, но с большим тестированием я определил следующее:

• Формулам предшествуют со знаком "равно" "="
• Строки в формулах включаются в двойные кавычки и должны быть удалены перед поиском реальных ссылок, иначе =A1&"A1" повредил бы regex
• Названия рабочего листа могут быть до 31 символа долго, исключая \/? * []:
• За названиями рабочего листа во внешних ссылках нужно следовать с ударом =Sheet1!A1
• Имена рабочей книги во внешних ссылках должны быть включены в квадратные скобки =[Book1.xlsx]Sheet1!A1
• Пути рабочей книги, которые добавляет Excel, ли ссылка к диапазону в закрытой рабочей книге, всегда включаются в одинарные кавычки и слева от скобок для имени рабочей книги 'C:\[Book1.xlsx]Sheet1'!A1
• Некоторые символы (неразрывное пространство, например) заставляют Excel включать рабочую книгу и название рабочего листа во внешней ссылке в одинарных кавычках, но я не знаю конкретно который символы ='[Book 1.xlsx]Sheet 1'!A1
• Даже если R1C1-нотация включена, Range.Formula все еще ссылки возвратов в A1-нотации. Range.FormulaR1C1 ссылки возвратов в нотации R1C1.
• 3D стиль ссылки позволяет диапазон имен листа на одной рабочей книге =SUM([Book5]Sheet1:Sheet3!A1)
• Названные диапазоны могут быть указаны в формулах:
• Первый символ имени должен быть буквой, символ подчеркивания (_), или обратная косая черта (\). Оставшиеся символы на имя могут быть буквами, числами, периоды и символы подчеркивания.
• Вы не можете использовать символы верхнего регистра и символы нижнего регистра "C", "c", "R" или "r" как определенное имя, потому что они все используются в качестве сокращения от выбора строки или столбца для в настоящее время выбранной ячейки, когда Вы вводите их в текстовое поле Name или Go To.
• Имена не могут совпасть со ссылкой на ячейку, такой как 100 Z$ или 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 трудно знать, корректно ли это.

Спасибо!