Regex Or Not
Last Updated 9 Mar 2022 Difficulty level : Moderate
This is the tenth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding.
This article was written in response to a thread at Access World Forums:Range of ASCII values in criteria
Regular expressions (RegEx) are particularly suited to finding complex and repeating patterns in data.
However, regular expressions are NOT part of the standard VBA object library.
To start using RegEx with VBA, you need to select and activate the Microsoft VBScript Regular Expressions 5.5 object reference library from the references list.
To work with RegEx, you need to initiate a RegEx object.
The RegEx object is the mechanism through which you can utilize the RegEx properties and operations. To use the RegEx object, you must declare it through the following syntax:
Dim regexObject As RegExp
Set regexObject = New RegExp
This approach is called early binding and gives the benefit of Intellisense
The alternative is to use late binding in which case the above reference is NOT required
Dim regexObject As Object
Set regexObject = CreateObject(“VBScript.RegExp”)
The attached example uses late binding but this decision should have no effect on the speed of the tests
Tests & Results
In the Range of ASCII values in criteria thread at Access World Forums, the original poster was trying to review text and allow only space, apostrophe, A-Z, a-z, and "Latin-1 Supplement" (other European) letters (À-ÿ = extended ASCII values 192-255).
Two different solutions were suggested by AWF moderators Jack Drawbridge and Galaxiom using standard VBA functions and regular expressions respectively.
Each achieved the same result.
1. Checking the ASCII valueof each character in the text via Select Case (Jack Drawbridge)
2. Using Regex - RegExIllegalSearch procedure (Galaxiom)
The question then arose as to whether there was any significant difference in the execution times.
Galaxiom asked if I would compare the speed of each approach
As with many of the speed comparison tests I have posted, the expectation was that the differences would be minimal.
However, in this case there was a VERY marked difference in the times measured (see below).
For this example, I found a file of World Cities data (12959 records) at https://simplemaps.com/data/world-cities that contained various alphabetic, numeric and punctuation characters for testing and imported several (not all) fields from all records.
Two queries qryNoRegex and qryRegex were written to invoke the approaches mentioned above.
The module RegexRelatedTests includes two functions:
1. Testnames (function written by Jack Drawbridge) which invokes qryNoRegex
2. RegExIllegalSearch (function written by Galaxiom) which invokes qryRegex
In the same module are procedures RunTest which runs the 2 functions; and RepeatTest which executes RunTest 20 times in turn to check for variations in execution times.
Times were measured using clsTimer which uses the api GetTime Alias "timeGetTime".
Output was saved to the table tblResults. Each set of tests were performed 5 times giving 100 sets of results and average times were calculated.
Each set of results was very consistent with minimal variation between values
However, on the first set of tests the average time using Regex was 0.399 seconds whereas the standard VBA approach took an average of 0.067 seconds (approx 1/6 of the time)
The outcomes were so surprising that the tests were run on 3 workstations for comparison:
a) Desktop PC with 16 GB RAM – Access 365 32-bit
b) Laptop with 8 GB RAM – Access 365 64-bit
c) Windows tablet with 4 GB RAM – Access 365 64-bit
In each case, the approach using Regex took between 3 and 6 times longer than the standard VBA approach
Regular expressions are a very powerful tool for developers to make use of.
However, in this particular set of tests, using RegEx was clearly disadvantageous.
Perhaps its use was overkill for the test done?
In other cases, Regex may provide the best or only method of obtaining results.
I would be grateful for any feedback on this article
I would also welcome any suggestions for other tests in order to further assess the comparative strength of regular expressions against other methods.
Click to download the example app used in these tests: RegexNoRegex_v2.zip (approx 3.4 MB (zipped)
Further Reading about Regular Expressions
Colin Riddington Mendip Data Systems Last Updated 9 Mar 2022