Regex vba script throwing error : runtime error 9 ... Subscript out of range-CodePudding

I have a Word doc with some numbers referred in the foot notes. and I am exporting these references as a csv file.

Sub FindControlNumber()
Dim exp, exp1 As RegExp
Set exp = New RegExp
exp.Pattern = "\b[A-Za-z]{3}[0-9]{7}\b"
exp.Global = True

Dim splits(1000) As String
Dim x As Long

Dim results As MatchCollection
Set results = exp.Execute(ActiveDocument.StoryRanges(wdFootnotesStory))
x = 1

For Each res In results
splits(x) = res
x = x   1
Next res


Dim Filename As String, line As String
Dim i As Integer
Filename = "C:\Users\Mohit\Desktop\VBA Export" & "\Control Numbers.csv"
Open Filename For Output As #2
Print #2, "Control Numbers"
For i = LBound(splits) To UBound(splits)

Print #2, splits(i)

Next i
Close #2
MsgBox "Control Numbers were exported to " & Filename, vbInformation
End Sub

The code above was working fine and just suddenly starting throwing error at 'splits(x) = res' I have tried checking my regex and I can see that it works fine. If I change splits(x) to splits(6) or something similar it works like a charm .

Can someone please help ?

CodePudding user response：

EDIT - added one more layer of abstraction to create a re-usable "ExportMatches" method if you have multiple types of pattern you need to look for.

Using a Collection instead of an array:

Sub Tester()

    ExportMatches ActiveDocument.StoryRanges(wdFootnotesStory), _
                    "\b[A-Za-z]{3}[0-9]{7}\b", _
                    "Control Numbers", _
                    "C:\Temp\Control Numbers.csv"
                    
    ExportMatches ActiveDocument.StoryRanges(wdFootnotesStory), _
                    "\b[A-Za-z]{2}[0-9]{9}\b", _
                    "Other Numbers", _
                    "C:\Temp\Other Numbers.csv"

End Sub

'Search through `SearchText` for text matching `patt` and
' export all matches to a file at `filePath` with a header `HeaderText`
Sub ExportMatches(SearchText As String, patt As String, _
                HeaderText As String, filePath As String)
    
    Dim exp, exp1 As RegExp, col As New Collection
    Dim results As MatchCollection, res As Match
    
    Set exp = New RegExp
    exp.pattern = patt
    exp.Global = True
    
    col.Add HeaderText 'add the header
    Set results = exp.Execute(SearchText)
    For Each res In results
        col.Add res
    Next res
    
    If col.Count > 1 Then
        CollectionToFile col, filePath
    End If
    'log to Immediate pane
    Debug.Print (col.Count - 1) & " matche(s) for '" & patt & "' saved to " & filePath
End Sub

'Export a Collection object's items to a text file
Sub CollectionToFile(col As Collection, filePath As String)
    Dim res, f
    f = FreeFile
    Open filePath For Output As #f
    For Each res In col
        Print #f, res
    Next res
    Close #f
End Sub