EDUCBA

Home VBA VBA Resources VBA String and Text Functions VBA RegEx

VBA RegEx

Madhuri Thakur
Article byMadhuri Thakur

VBA RegEx

VBA RegEx

VBA RegEx “Regular Expression” is very useful in programming world which can be used to validate website input, searching of word patterns in large string/text, for string searching and replacement.

 

 

Most of us are using FIND, SUBSTITUTE, LEFT, RIGHT along with MID functions for string manipulation in Microsoft Excel, aren’t we? However, these functions are having their own limitations which can’t be used widely. Having knowledge about VBA Regular Expressions will save a lot of time for you in your day to day life. Regular Expressions are short formed as “RegEx”(or Regex) in VBA. In this tutorial, we are going to have a look at some of the RegEx with their utilization.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can use VBA RegEx object which is already created in Microsoft VBScript Regular Expressions library. You can navigate this library by clicking Tools – Reference – Microsoft VBScript Regular Expressions.

You can also create a RegEx object using CreateObject function in VBA. It seems more convenient way because you don’t always have to refer the VBScript Regular Expressions library in this case.

See the code below:

Dim regex as Object

Set regex = CreateObject("VBScript.RegExp")

This code will allow you to create and access RegEx objects in your macro. I personally prefer this way over the first due to its rigidity.

RegEx function allows to manipulate the following in a long string.

  • Wild Cards like *, ? etc.
  • Grouped expressions along with wild cards.

RegEx has below parameters/properties:

  1. Pattern – The pattern defined in RegEx which you want to do operations on.
  2. IgnoreCase – Ignore the case of letters (Caps/Non-Caps are same in a string).
  3. Global – Allows to find all possible matches if set to TRUE. If set as FALSE, it finds only the first match.
  4. MultiLine – Allows matching the pattern in a string which is spread along multiple lines.

RegEx facilitates the below three operations:

  • Test – Tests if the mentioned pattern is present in the string or not. If present returns TRUE, else FALSE.
  • Replace – Replaces the original pattern with the other.
  • Execute – Returns all the matching patterns searched.

How to Use Excel VBA RegEx?

We will learn how to use Excel VBA Regular Expression with few examples.

You can download this VBA RegEx Excel Template here – VBA RegEx Excel Template

Excel VBA RegEx – Example #1

Here, you will Check if the mentioned pattern is present in the text using RegEx.Test.

Follow the below steps to use VBA RegEx.

Step 1: Define a new sub-procedure to create a macro.

Code:

Sub RegEx_Ex1()

End Sub

VBA RegEx Example 1-1

Step 2: Define two variables RegEx as an Object which can be used to create RegEx object and Str as a string.

Code:

Sub RegEx_Ex1()

Dim RegEx As Object, Str As String

End Sub

VBA RegEx Example 1-2

Step 3: Create RegEx object using CreateObject function.

Code:

Sub RegEx_Ex1()

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

End Sub

VBA RegEx Example 1-3

Step 4: Add the pattern to be tested with RegEx function.

Code:

Sub RegEx_Ex1()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With

End Sub

VBA RegEx Example 1-4

Step 5: Define the string within which we need to check the given pattern.

Code:

Sub RegEx_Ex1()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With
Str = "My Bike Number is MH-12 PP-6145"

End Sub

VBA RegEx Example 1-5

Step 6: Use RegEx.Test to test whether the given pattern is present within the variable named Str. Also, use Debug.Print to print the result (True or False) on the immediate window.

Code:

Sub RegEx_Ex1()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "[0-9]+"
End With
Str = "My Bike Number is MH-12 PP-6145"
Debug.Print RegEx.Test(Str)

End Sub

VBA RegEx Example 1-6

Step 7: Hit F5 or Run button to run the code and see the output. (Press CTRL + G to open Immediate Window)

Result of Example 1-7

Here, we have created a RegEx object. Then, with VBA RegEx, we have checked if the given pattern (“[0-9]+”) which includes all the numbers/combination of numbers from 0 to 9 is present in the string defined with the help of RegEx.Test(Str). Output “True” under immediate window shows that the pattern “[0-9]+” is present in a given string.

Excel VBA RegEx – Example #2

Here, you will see how you can replace the one string with other using RegEx.Replace.

Follow the below steps to use VBA RegEx.

Step 1: Define a new sub-string to create a macro.

Code:

Sub RegEx_Ex2()

End Sub

VBA RegEx Example 2-1

Step 2: Same as in the first example, define two variables RegEx as an Object which can hold the value of RegEx object and Str which will hold up the value of string you will define.

Code:

Sub RegEx_Ex2()

Dim RegEx As Object, Str As String

End Sub

VBA RegEx Example 2-2

Step 3: Define the RegEx object and set it to RegEx variable using the CreateObject function.

Code:

Sub RegEx_Ex2()

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

End Sub

VBA RegEx Example 2-3

Step 4: Insert the pattern which you want to get replaced using .Pattern function.

Code:

Sub RegEx_Ex2()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123"
End With

End Sub

VBA RegEx Example 2-4

Step 5: Use .GLOBAL = TRUE which if TRUE allows replacing all matching patterns in a given string. If FALSE replaces only the first matching pattern.

Code:

Sub RegEx_Ex2()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123"
    .Global = True      'If FALSE, Replaces only the first matching string'
End With

End Sub

VBA RegEx Example 2-5

Step 6: After End With, define the string which you want to match and replace the pattern with.

Code:

Sub RegEx_Ex2()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123"
    .Global = True      'If FALSE, Replaces only the first matching string' 
End With
Str = "123-654-000-APY-123-XYZ-888"

End Sub

VBA RegEx Example 2-6

Step 6: Use RegEx.Replace to replace the pattern value in the given string by other text.

Code:

Sub RegEx_Ex2()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123"
    .Global = True      'If FALSE, Replaces only the first matching string' 
End With
Str = "123-654-000-APY-123-XYZ-888"
Debug.Print RegEx.Replace(Str, "Replaced")

End Sub

VBA RegEx Example 2-7

Here, we wanted the pattern “123” to be replaced by some other piece of string. RegEx.Replace allows replacing the pattern value with the piece of string (we have defined “Replaced”). Please also have a look at .Global = TRUE. Which is a conditional statement. If Global = TRUE, the Replace function replaces all the matching patterns with the other string. If GLOBAL = FALSE, the Replace function replaces only the first matching pattern and rest others are neglected.

Step 7: Hit the Run button or F5 to run this code and see the output in the Immediate output window.

Result of Example 2-8

Excel VBA RegEx – Example #3

Here, you will see how you can find all the matching patterns in a string using RegEx.Execute.

Follow the below steps to use Excel VBA RegEx.

Step 1: Define a new sub-procedure to add the macro.

Code:

Sub RegEx_Ex3()

End Sub

VBA RegEx Example 3-1

Step 2: Follow the steps the same as of the previous two examples to define a regular expression and a variable which can hold the string value.

Code:

Sub RegEx_Ex3()

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

End Sub

VBA RegEx Example 3-2

Step 3: Insert a pattern which you wanted to be executed and matched in the given string.

Code:

Sub RegEx_Ex3()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123-XYZ"
End With

End Sub

VBA RegEx Example 3-3

Step 4: Use .GLOBAL = TRUE so that all the matches will be captured in the given string.

Code:

Sub RegEx_Ex3()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123-XYZ"
    .Global = True      
End With

End Sub

Example 3-4

Step 5: Define a string after End With under which you need to use Execute and find all the matching patterns.

Code:

Sub RegEx_Ex3()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123-XYZ"
    .Global = True
End With
Str = "123-XYZ-326-ABC-983-670-PQR-123-XYZ"

End Sub

Example 3-5

Step 6: Now, use Execute on the variable named string to find out all the matches associated with a given pattern.

Code:

Sub RegEx_Ex3()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123-XYZ"
    .Global = True
End With
Str = "123-XYZ-326-ABC-983-670-PQR-123-XYZ"
Set matches = RegEx.Execute(Str)

End Sub

Example 3-6

Step 7: Use For loop to print the value of all matched patterns into the immediate output window.

Code:

Sub RegEx_Ex3()

Dim RegEx As Object, Str As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
    .Pattern = "123-XYZ"
    .Global = True
End With
Str = "123-XYZ-326-ABC-983-670-PQR-123-XYZ"
Set matches = RegEx.Execute(Str)
For Each Match In matches
    Debug.Print Match.Value
Next Match

End Sub

Example 3-7

Step 8: Run this code by hitting F5 or run button and see the output under immediate output window.

Result of Example 3-8

This code prints all the number of matches for the pattern which is defined.

Things to Remember

  • VBA Regular Expression can be only used to find out the strings. Obviously, for numeric values, we don’t need such a function. All the numeric values considered in each of the three examples above are actually defined as a string.
  • Excel VBA RegEx is supposed to be used for string manipulation whenever there is a large data. Not using VBA RegEx reduces the efficiency of the program.
  • VBA RegEx fails when there are scenarios where a pattern is repeating n number of times or infinite times for that sake.

Recommended Articles

This has been a guide to VBA RegEx. Here we discussed how to use Excel VBA RegEx along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Replace
  2. VBA TIMER
  3. VBA RGB
  4. VBA XML