One of the most powerful features of many other programming languages is their ability to utilize Regular Expressions (RegEx) for finding patterns… to manipulate and/or extract data from strings.

Sadly however, VBA does not have the ability to use them, well at least not natively! Thankfully, it really is not very complicated to be able to integrate RegEx into one any VBA procedure by simply creating a VBScript object (since VBScript does support RegEx).

The basic idea goes something like:

Set oRegEx = CreateObject("VBScript.RegExp")
oRegEx.Pattern = "YourRegEx Goes Here"
bResult = oRegEx.Test(YourStringToTestTheRegExWith)

Instead of just testing, you can .Execute, .Replace… Lots of fun things you can do with RegEx.

One word of caution however, the VBA implementation can be frustrating at times as it is a limited implementation. Several native Regular Expression functionalities do not exist in VBA. For instance, typically you can use /L or /U with the .Replace to make matches lowercase or uppercase, sadly, it does not work in the VBA implementation of RegEx. So, not all solutions you may come up with (which are perfectly sound) or locate online will work.

A great source of RegEx for various purposes can be found at http://regexlib.com/ (click on the button ‘Browse Expressions’ in the main header menu). Start by looking there before wasting your time trying to reinvent the RegEx wheel. You’re not the first to need to validate a string, number, …, chances are a RegEx already exists that you can simply copy.

And there you go, now you can integrate the power of Regular Expressions in all of your Databases, Spreadsheets, Documents and more! Simple as can be!!!

A Few Resources on the Subject

Documentations & General Information for Learning

• Regular Expression Library
• Microsoft Beefs Up VBScript with Regular Expressions | Microsoft Docs
• VBScript RegExp Object – Using Regular Expressions with Client Side Scripting

A few Concrete VBA functions & Examples

• VBA – Directory/Folder Name Validation
• VBA – Split/Break a Camel Case String
• VBA – Remove HTML Tags from a String
• VBA – Is Uppercase?
• VBA – Replace String Between Delimiters
• VBA – Extract Email Address(es) from a String
• VBA – Extract Phone Numbers From a String
• and many more (simply search this site)