I recently had to write a large amount of Microsoft Access VBA code to parse a bunch of text reports and store those results into tables in Access. I'm sure many of you out there are shuddering and/or sneering about having to write Access VBA.
My feelings are n-fold:
- It was a paying gig and I got to do it from home.
- It was a nice break from writing PHP & Python.
- It was an interesting intellectual exercise and I got to learn something new.
Sounds like a pretty good deal, right?
The text I had to parse lent itself pretty nicely to a set of regular expressions but I wasn't sure how that was done (or if it was even possible) in VBA. Of course I didn't give VBA enough credit because 30 seconds of googling lead me to the RegExp object which can be found by adding a reference to the Microsoft VBScript Regular Expressions library.
It's dead simple to use, especially if you have something like to help with building your regular expressions.
Here's an example that does some simple testing as well as grouped matching including retrieving subgroups.
Dim szLine As String Dim regex As New RegExp Dim colregmatch As MatchCollection With regex .MultiLine = False .Global = True .IgnoreCase = False End With szLine = "Analyzed range (from-to) 10 100" regex.Pattern = " Analyzed range" If regex.Test(szLine) Then regex.Pattern = ".*?([0-9]+).*?([0-9]+)" Set colregmatch = regex.Execute(szLine) 'From Debug.Print colregmatch.Item(0).submatches.Item(0) 'To Debug.Print colregmatch.Item(0).submatches.Item(1) End If