What is RegEx in Excel VBA?

RegEx stands for “Regular Expression” in VBA Excel and is a sequence of characters that defines the search pattern for finding a specific pattern of characters in a string of values. So, in a simple word, we can create a regular expression pattern and use it to search for the string of that pattern.

VBA RegEx is an object model. We know it is intimidating to look at the explanation, but the thing is the nature of the object. First, you must remember that VBA RegEx (Regular Expression) is a text object like our other text functionsText FunctionsTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more, “LEFT, RIGHT, MID.”

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA RegEx (wallstreetmojo.com)

How to Enable RegEx in Excel VBA?

Example – Now, we will show you one simple example. Assume you have the words “Sales 2019, Sales 2018, and Sales 2017”. If you define the pattern as [0 – 7], it matches all the numbers between 0 to 7. Therefore, our matches will be 201, 201, and 2017 in each string.

  • Go to Visual Basic Editor (Alt + F11) Go to “Tools” and “References.” Now, you will see references to the VBA Project. Scroll down and select “Microsoft VBScript Regular Expression 5.5.” Now click on “OK.” We can access this RegEx object now in VBA coding.

VBA RegEx Pattern

The pattern of the VBA RegEx function looks intimidating. It takes some time to understand the pattern. For example, we can see two kinds of sequences of characters: literal characters and metacharacters.

  • Literal Characters search for the exact match of the provided string. For example, the literal character sequence “EFG” looks for all the matches of “EFG” in the provided text.Metacharacters are nothing but a combination of characters with exact meaning in the RegEx pattern. It is completely different from Literal Characters. It is a huge topic to cover. Below are some of the important syntaxes.

Properties and Methods of RegEx Object

Like all our object models, RegEx, too, has its properties and methods. Now, we will see them one by one in detail.

Properties of VBA Regex Object

  • Pattern: One may use it to match the provided string.Ignore Case: This is to ignore uppercase and lowercase characters.Global: If you want to find all matches in the pattern, then TRUE is the argument. Else, it will find the first match.Multi-Line: You can use this if you wish to find new line breaksLine BreaksLine break in excel means inserting a new line in any cell value. To insert a line break, press ALT + Enter. As we insert a line break, the cell’s height also increases as it represents the data.read more.

Methods of RegEx Object

  • Test: This is to test whether we can find the pattern in the provided string. It will return TRUE if found or else FALSE.Execute: This will return all the pattern matches against the finding string.Replace: This will replace the search string with the new string.

Example of RegEx in VBA Excel

Now, take a look at the below example VBA codeExample VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code:

Sub RegEx_Example()

Dim RegEx As Object, MyString As String
Set RegEx = CreateObject("VBScript.RegExp")

With RegEx
  .Pattern = "[0-9]+"
End With

MyString = "Date of Birth year is 1985"
MsgBox RegEx.Test(MyString)

MyString = "Date of Birth year is ???"
MsgBox RegEx.Test(MyString)

End Sub

regular

In the above code, we have set the pattern to search the number from 0 to 9.

With RegEx

.Pattern = “[0-9]+”

End With

Then, the variable MyString = “Date of Birth year is 1985” holds the values from 0 to 9, so our message box will return TRUE.

MyString = “Date of Birth year is ???” doesn’t have any numbers from 0 to 9, so it will return FALSE as the message box result.

This article has been a guide to VBA RegEx. Here, we discuss using RegEx (Regular expression) in Excel VBA along with its properties, pattern, and example. You can learn more about Excel VBA from the following articles: –

  • Chr VBA FunctionOption Explicit Variable DeclarationActivate Sheet using VBA codeGetOpenFilename in VBA