WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA RegEx

VBA RegEx

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. 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. I know it is intimidating by looking at the explanation, but the thing is that the nature of the object. One thing you need to remember here is VBA RegEx (Regular Expression) is a text function object like our other text functions, “LEFT, RIGHT, MID.”

vba-regex

How to Enable RegEx in Excel VBA?

As I told VBA, RegEx is an object model in VBA, just like our external software like “MS Word” and “MS PowerPoint.” Similarly, RegEx is also a Component Object Model (COM), which we need to reference in the VBA editor. To enable RegEx, follow the below steps.

Step 1: Go to Visual Basic Editor (Alt + F11)

vba regex 1

Step 2: Go to Tools and References.

vba regex 1.1

Step 3: Now, you will see references to the VBA Project. Scroll down and select “Microsoft VBScript Regular Expression 5.5.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

vba regex 1.2

Step 4: Now click on, OK. We can access this RegEx object now in VBA coding.

Example – Now, I 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, so our matches will be 201, 201, and 2017 in each string.

VBA RegEx Pattern

The pattern of the VBA RegEx function looks intimidating, and takes some time to understand the pattern. We can see two kinds of a sequence of characters here one is “Literal Characters,” and another one is “Metacharacters.”

  • Literal Characters search for the exact match of the provided string. For example, the literal character sequence “EFG” simply 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. This completely different than Literal Characters. It is a huge topic to cover. Below are some of the important syntaxes.
Syntax Description Example Example Match
. It matches any single character of the input string. p.t Pet. Pot, Put, Pattern
[] It matches any single character between the bracket of an input string. [pt] It matches either p or t
[^] It matches any single character, not between the bracket of an input string. [^pt] It matches neither p or t
[First-Last] It matches any character between the range provided in the bracket. [0-9] It matches any digit from 0 to 9
[a-z] It matches any lower case character from a to z
[A-Z] It matches any upper case character from A to Z
\s It matches any white space character. – Matches Space, New Line, or Tab Character
\S It matches any non-white-space character – Matches characters are not Space, not New Line, or not Tab Character
\d It matches any single digit character. SE 5 VG 6 Matches 5 and 6
\D It matches any single non-digit character SE 5 VG 6 Matches SEVG

Properties and Methods of RegEx Object

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

Properties of VBA Regex Object

  • Pattern: This is used 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, or else the first match will be found.
  • Multi-Line: If you wish to find across new line breaks, you can use this.

Methods of RegEx Object

  • Test: This is to test whether the pattern can be found in the provided string. This will return TRUE if found or else FALSE.
  • Execute: This will return all the matches of the pattern 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 code.

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

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 as follows.

With RegEx

  .Pattern = "[0-9]+"

End With

Then the variable MyString = “Date of Birth year is 1985” this 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 will return FALSE as the message boxes result.

example Gif

Recommended Articles

This has been a guide to VBA RegEx. Here we discuss how to use 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 Function
  • Option Explicit Variable Declaration
  • Activate Sheet using VBA code
  • GetOpenFilename in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA RegEx Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More