vba: regex, remove all but cell reference row references from formula strings

Question

In VBA I am trying to build a generalized function that turn strings like these:

a) =IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)

b) =IF(FZ$16=(BDP($C24,FZ$18,FZ$19,"EQY_FUND_CRNCY",FX)),FZ$17,IF($B24="","",BDP($C24,FZ$18,FZ$19,"EQY_FUND_CRNCY",FX)))

c) =IF(ISNUMBER(FU24),TRUNC((((COUNTIF($J$23:$J$2515,$J24)-(SUMPRODUCT(($J$23:$J$2515=$J24)*(FU24<FU$23:FU$2515))))/COUNTIF($J$23:$J$2515,$J24)))*100,2),FX$17)

d) =IFERROR(PERCENTRANK(EO$23:EO$2515,EO24,3)*(-100)+100,ET$17)

e) =BDP($C24,EH$18,EH$19,"EQY_FUND_CRNCY",FX)

Into these:

a) 23 2515 24 17

b) 16 24 18 19 17 24 24 18 19

c) 24 23 2515 24 23 2515 24 24 23 2515 23 2515 24 17

d) 23 2515 24 17

e) 24 18 19

In other words, remove everything except cell reference rows and separate them with spaces (or some other deliminator) so I can VBA.split(x," ") them later.

Notes:

1. Numbers that aren't part of a cell references are removed.
2. To use this function you must have the regular expression library. If the code below doesn't work for you include the library: http://support.microsoft.com/kb/818802. (Side note: if you know how to include the library in the code without having to follow those instructions, please share.)
3. The list of formulas in this example is just an example. I am looking for a generalized solution.

4. I built this little test sub that might be helpful (IT DOESN'T DO WHAT I WANT):

   Sub test()
   
   Dim s As String
   s = "=IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)"
   Dim s2 As String
   Dim s3 As String
   Dim s1 As String
   Static re As RegExp
   If re Is Nothing Then Set re = New RegExp
   re.IgnoreCase = True
   re.Global = True
   re.Pattern = "[$]"
   s1 = re.Replace(s, "")
   re.Pattern = "[^A-Z0-9 ]"
   s2 = re.Replace(s1, " ")
   re.Pattern = "[^0-9]"
   s3 = re.Replace(s2, " ")
   Debug.Print s3
   End Sub
   

Answer 1

Try:

Sub test()

    Dim s As String, matches, m

    s = "=IFERROR(PERCENTRANK($FU$23:$FU$2515,FU24,3)*100,FY$17)"

    Static re As Object 
        If re Is Nothing Then

            Set re = CreateObject("VBScript.RegExp") 'late binding

            re.IgnoreCase = True
            re.Global = True
            re.Pattern = "[A-Z]+\$?(\d+)"
        End If

    Set matches = re.Execute(s)

    If matches.Count > 0 Then
        For Each m In matches
            Debug.Print m.SubMatches(0)
        Next m
    End If

End Sub