Excel regex match and return multiple references in formula

Question

I've created a function that will return the Nth reference which includes a sheetname (if it's there), however it's not working for all instances. The regex string I'm using is

'[\w ]+[']!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5})

I'm finding though it won't find the first reference in either of the below examples:

='Biscuits Raw Data'!G783/'Biscuits Raw Data'!E783
=IF('Biscuits Raw Data'!G705="","",'Biscuits Raw Data'!G723/'Biscuits Raw Data'!G7005*100)

Below is my Function code:

Function GrabNthreference(Rng As range, NthRef As Integer) As String

Dim patrn As String
Dim RegX
Dim Matchs
Dim RegEx
Dim FinalMatch
Dim Subm
Dim i As Integer
Dim StrRef As String

patrn = "'[\w ]+[']!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5})"

StrRef = Rng.Formula

Set RegEx = CreateObject("vbscript.regexp")       ' Create regular expression.
RegEx.Global = True
RegEx.Pattern = patrn                             ' Set pattern.
RegEx.IgnoreCase = True                           ' Make case insensitive.
Set RegX = RegEx.Execute(StrRef)

If RegX.Count < NthRef Then
    GrabNthreference = StrRef
    Exit Function
End If
i= -1
For Each Matchs In RegX    ' Iterate Matches collection.
    Set Subm = RegX(i).submatches
    i = i + 1
    If i = NthRef -1 Then
        GrabNthreference = RegX(i)
        Exit Function
    End If
    'Debug.Print RegX(i)
Next

End Function

aha! found some different code which made me realise my mistake in that i = i+1 was incrementing to 1 before reporting RegX(i) when i=0. I shall scrap the above code and use the following. Thanks for @michael-møldrup for his code. [link](http://stackoverflow.com/questions/13835466/find-all-used-references-in-excel-formula) — Rodp, Oct 21 '16 at 10:06

score 0 · Answer 1 · answered Oct 21 '16 at 10:20

Here's my final code

Function GrabNthreference(R As range, NthRef As Integer) As String 'based on http://stackoverflow.com/questions/13835466/find-all-used-references-in-excel-formula
Dim result As Object
Dim testExpression As String
Dim objRegEx As Object
Dim i As Integer

i = 0

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
objRegEx.Pattern = """.*?"""  ' remove expressions
testExpression = CStr(R.Formula)
testExpression = objRegEx.Replace(testExpression, "")
'objRegEx.Pattern = "(([A-Z])+(\d)+)"  'grab the address  think this is an old attempt so remming out
objRegEx.Pattern = "(['].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))"
If objRegEx.Test(testExpression) Then
    Set result = objRegEx.Execute(testExpression)
    If result.Count > 0 Then
        For Each Match In result
            Debug.Print Match.Value

        If i = NthRef - 1 Then
            GrabNthreference = result(i)
            Exit Function

        End If
        i = i + 1
        Next Match
    Else
    GrabNthreference = "No precedencies found"
    End If
End If

End Function

This code did lead me onto thinking about using the simple activecell.precedences method but I think the problem is that it won't report offsheet and won't indicate if the formula is relative or absolute.

Any comments welcome but I think I've answered my own question :)

Excel regex match and return multiple references in formula

1 Answers1