title: Is this the RegEx for matching any cell reference in an Excel formula?
body: <p>I have been trying to create a regular expressions pattern that matches any reference in any Excel formula, including absolute, relative, and external references.  I need to return the entire reference, including the worksheet and workbook name.</p>
  
  <p>I haven't been able to find exhaustive documentation about Excel A1-notation, but with a lot of testing I have determined the following:</p>
  
  <ul>
  <li>Formulas are preceded with an equal sign "="</li>
  <li>Strings within formulas are enclosed in double quotes and need to be removed before looking for real references, otherwise <code>=A1&"A1"</code> would break regex</li>
  <li>Worksheet names can be up to 31 characters long, excluding \ / ? * [ ] :</li>
  <li>Worksheet names in external references must be succeeded with bang <code>=Sheet1!A1</code></li>
  <li>Workbook names in external references must be enclosed in square brackets <code>=[Book1.xlsx]Sheet1!A1</code></li>
  <li>Workbook paths, which Excel adds if a reference is to a range in a closed workbook, are always enclosed in single quotes and to the left of the brackets for the workbook name <code>'C:\[Book1.xlsx]Sheet1'!A1</code></li>
  <li>Some characters (non-breaking space, for example) cause Excel to enclose the workbook and worksheet name in an external reference in single quotes, <em>but I don't know specifically which characters</em> <code>='[Book 1.xlsx]Sheet 1'!A1</code></li>
  <li>Even if R1C1-notation is enabled, <code>Range.Formula</code> still returns references in A1-notation. <code>Range.FormulaR1C1</code> returns references in R1C1 notation.
  <li>3D reference style allows a range of sheet names on one workbook <code>=SUM([Book5]Sheet1:Sheet3!A1)</code></li>
  <li>Named ranges can be specified in formulas:</li>
  <ul>
  <li>The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.</li>
  <li>You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.</li>
  <li>Names cannot be the same as a cell reference, such as Z$100 or R1C1.</li> 
  <li>Spaces are not allowed as part of a name.</li> 
  <li>A name can be up to 255 characters in length.</li> 
  <li>Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names.</li>
  </ul>
  </ul>
  
  <p>Here is what I came up with wrapped in a VBA procedure for testing. I updated the code to handle names as well:</p>
  
  <pre><code>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}\'\!" _
              &amp; "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
              &amp; "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
              &amp; "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
              &amp; "|[a-z]{1,3}\:[a-z]{1,3}" _
              &amp; "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
              &amp; "|[0-9]{1,7}\:[0-9]{1,7}" _
              &amp; "|[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 &gt; objMatch.FirstIndex _
                      And objReferenceMatches(intIndex).FirstIndex &lt; objMatch.FirstIndex + objMatch.Length Then
                          booIsReference = False
                          Exit For
                      End If
                  Next
  
                  If booIsReference Then
                      objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                      &amp; "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
                      &amp; "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
                      &amp; "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
                      &amp; "|[a-z]{1,3}\:[a-z]{1,3}" _
                      &amp; "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
                      &amp; "|[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}\'\!" _
                          &amp; "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)" _
                          &amp; "[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}\'\!" _
                                  &amp; "|(\[.*\])?([^\:\\\/\?\*\[\]]{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 "  " &amp; objReferenceMatches(intIndex).Value _
                      &amp; " (" &amp; objReferenceMatches(intIndex).FirstIndex &amp; ", " _
                      &amp; objReferenceMatches(intIndex).Length &amp; ")"
                  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
  </code></pre>
  
  <p>Can anyone break or improve this? Without exhaustive documentation on Excel's formula syntax it is difficult to know if this is correct.</p>
  
  <p>Thanks!</p>