×

VBA Regex Pattern Validation

VBA Regex Pattern Validation

The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard and are followed worldwide. Following those standards, we can extract and list all the values from the excel datasheet just within one click. Below are the few common regex validation patterns used in day to day activities.

  1. Email Validation using Regex

 In email validation, two things are constant, i.e. ‘@’ and ‘.’. With the help of these two characters we have defined the regex pattern. In the first place, we can have as many characters, it will be followed by ‘@’, then again we will have characters (unlike Gmail) followed by ‘.’ and at last ending it characters (com/in/co.in).

Regex Syntax:

[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]

Program:

Function EmailRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the Boolean True if the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidEmail As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim emailRegexPattern As String
'the regex pattern for 10-digit phone validation
emailRegexPattern = "([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.])"
With regex
    .Pattern = emailRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the negative numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidEmail = regex.Test(regexStr)
EmailRegexValidation = isValidEmail
End Function
VBA Regex Pattern Validation

Output:

VBA Regex Pattern Validation
VBA Regex Pattern Validation

2. Regex Validation Pattern for Alphanumeric Strings

It must have a combination of alphabets and numbers. It will mostly be used in passwords and hence, alphanumeric strings are classified as Strong passwords.

Regex Syntax:

^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))

Program:

Function AlphaNumericRegexValidation (regexStr As String) As Boolean
'***************************************************************
' This function returns the boolean True the pattern matches
'***************************************************************
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim phoneNumberRegexPattern As String
'Defining the regex pattern to extract or validate alpha numeric
alphaNumericRegexPattern = "^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))"
With regex
    .Pattern = alphaNumericRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the negative numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
End Function

3. RegEx Pattern for Positive numbers

It will check for the number which is greater than 0.

Regex Syntax:

^([+]?\d+([.]\d+))

Program:

Function PositiveNumberRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the boolean True the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidNumber As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim positiveNumberRegexPattern As String
'the regex pattern for positive number validation
positiveNumberRegexPattern = "^([+]?\d+([.]\d+))"
With regex
    .Pattern = positiveNumberRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the positive numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidNumber = regex.Test(regexStr)
PositiveNumberRegexValidation = isValidNumber
End Function
VBA Regex Pattern Validation

4. RegEx Validation for Negative numbers (num < 0)

It will check for the number which is smaller than 0.

Regex Syntax:

^(\-\d+([.]?\d+))

Program:

Function NegativeNumberRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the boolean True the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidNumber As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim negativeNumberRegexPattern As String
'the regex pattern for negative number validation
negativeNumberRegexPattern = "(^(\-\d+([.]?\d+)))"
With regex
    .Pattern = negativeNumberRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidNumber = regex.Test(regexStr)
NegativeNumberRegexValidation = isValidNumber
End Function
VBA Regex Pattern Validation

Output

VBA Regex Pattern Validation

5. RegEx Pattern for 10-digit telephone number [not starting with Zero]

It will check for a 10-digit telephonic number. As the first digit can never be 0, so it will check the number from 1-9, and for the next 9 digits, it can take values from 0-9.

Regex Syntax:

[1-9]{1}[0-9]{9}$

Program:

Function PhoneNumberRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the Boolean True if the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidEmail As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim phoneNumberRegexPattern As String
'the regex pattern for 10-digit phone validation
phoneNumberRegexPattern = "[1-9]{1}[0-9]{9}$"
With regex
    .Pattern = phoneNumberRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the negative numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidEmail = regex.Test(regexStr)
PhoneNumberRegexValidation = isValidEmail
End Function
VBA Regex Pattern Validation

Output

VBA Regex Pattern Validation
VBA Regex Pattern Validation

6. RegEx Pattern for Phone with a country code format

It same unlike the above syntax except in this we have added the ‘+’ character and allocated two more digits, which can be from 0-9.

Regex Syntax:

[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$

Program:

Function PhoneNumberRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the boolean True the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidEmail As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim phoneNumberRegexPattern As String
'the regex pattern for 12 digit phonenumber (along with country code) validation
phoneNumberRegexPattern = "[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$"
With regex
    .Pattern = phoneNumberRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidEmail = regex.Test(regexStr)
PhoneNumberRegexValidation = isValidEmail
End Function

7. RegEx Pattern for Year between 1900 and 2018

This is helpful to check if the specified year lies in between the range of 1900 – 2018.

Regex Syntax:

(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$

Program:

Function YearRegexValidation(regexStr As String) As Boolean
'***************************************************************
' This function returns the boolean True the pattern matches
'***************************************************************
Dim regex As Object
Dim isValidYear As Boolean
Set regex = CreateObject("VBScript.RegExp")
Dim matchesVal As Object
Dim yearRegexPattern As String
'the regex pattern for year(between 1900 and 2018) validation
yearRegexPattern = "(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$"
With regex
    .Pattern = yearRegexPattern
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' All the numbers will be listed in this Object
Set matchesVal = regex.Execute(regexStr)
'Loop reading all the matches found
For Each Item In matchesVal
    Debug.Print Item.Value
Next
'will test the string for the given pattern
isValidNumber = regex.Test(regexStr)
YearRegexValidation = isValidNumber
End Function
VBA Regex Pattern Validation

Output:

VBA Regex Pattern Validation

Some Other useful Regex Patterns

  1. Password validation using Regex
  • Strong Password: It consists at least one small letter, one capital letter, one symbol, and one number, and it should be 8 chars long.

Regex Syntax:

^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{8,})
  • Medium password: It consists one capital, one small letter, and one numeric field, and total 6 chars long.

Regex Syntax:

^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))
  • Simple password: It takes at least one alphabet, and one numeric field [alphanumeric], and the total length should be 5 characters long.

Regex Syntax:

^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))
  • RegEx Pattern for Date format : DD/mm/yyyy

Regex Syntax:

^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\/(0[1-9]|[1-9]|1[0-2])\/([12]\d{3}))
  • Regex Pattern for NL Postal Code

Regex Syntax:

\d{4}[ ]*([aA-zZ]{2})$
  • Regex Pattern for US Postal Code format

Regex Syntax:

^\d{5}(?:[-\s]\d{4})?$
  • RegEx Pattern for Website URL
(https?:\/\/(?:www\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\.[^\s]{2,}|www\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\.[^\s]{2,}|https?:\/\/(?:www\.|(?!www))[a-zA-Z0-9]\.[^\s]{2,}|www\.[a-zA-Z0-9]\.[^\s]{2,})

Related Topics

Excel VBA Weekday Function

Excel VBA Weekday Function: The TimeValue function in VBA returns an integer (1 to 7), signifying the day of the week for the specified date. Syntax Weekday (Date, [FirstDayOfWeek]) Parameter Date (required) – This parameter...

2 minutes read.

VBA Regex Pattern Validation

VBA Regex Pattern Validation The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard...

6 minutes read.

Excel VBA Join Function

Excel VBA Join Function: The Join function in VBA is used to join an array of substrings and return them all as a single string. Syntax Join (SourceArray, [Delimiter]) Parameter SourceArray (required) – This parameter...

1 minute read.

Excel VBA Tan Function

VBA Tan Function: The Tan function in VBA returns the tangent for the specified angle in radians. Syntax Tan (Number) Parameter Number (required) – This parameter represents the angle supplied in radiant that you want...

1 minute read.

Excel VBA FormatDateTime Function

VBA FormatDateTime Function: The FormatDateTime function in VBA returns the result as a string after applying a date and/or time format to the supplied expression. Syntax FormatDateTime (Expression, [NamedFormat]) Parameter Expression (specified) – This parameter...

2 minutes read.

Excel Objects in VBA

What are Excel Objects? The Excel objects belong to the entities that make up an Excel Workbook, Worksheets, Columns, Rows, Cell Ranges, etc. Each object in Excel has loads of Properties that are...

6 minutes read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

Excel VBA Timer Function

Excel VBA Timer Function: The Timer function in VBA returns a Single data type, evaluating the number of seconds that have elapsed since midnight of the current day. Syntax Timer () Parameter NA Return This...

1 minute read.

VBA Object Required

What is Object Required Error? VBA Object Required is a run time error which occurs when the user does not define a valid object qualifier, or the assigned object doesn’t exist in the...

6 minutes read.

Scope in Visual Basics

Definition of Scope The scope of any programming language implies the area of code where the variables will be identified, accessed, and used. Every variable has a scope associated with it. The scope of...

4 minutes read.

Excel VBA Sgn Function

VBA Sgn Function: The Sgn function in VBA returns an integer (+1, 0, or -1), stating the arithmetic sign for the specified number. Syntax Sgn (Number) Parameter Number (required) –This parameter represents the number that...

1 minute read.

Excel VBA Functions

What is a function? A function is also called a procedure, but it is not a sub procedure, it’s a function procedure. You have already been using some function in Excel,...

7 minutes read.

VBA Option Explicit

VBA Option Explicit The Option Explicit in VBA is used to declare the variables at the top of your macro code. It is the most secure and easy option to maintain your variables....

5 minutes read.

Excel VBA MonthName Function

Excel VBA MonthName Function: The MonthName function in VBA returns a string with the month name for the specified month number. Syntax MonthName (Month, [Abbreviate]) Parameter Month (required) – This parameter represents an integer between...

1 minute read.

Excel VBA Asc Function

VBA Asc Function: The Asc function in VBA returns an integer showing the character code for the first character of a given string. Syntax Asc (String) Parameter String (required) – This parameter represents the text...

1 minute read.

Excel VBA Day Function

The function Day in VBA returns the day number (from 1 to 31) for the given date value. Syntax Day (Date) Parameter Date (required) – This parameter represents the date. Return This function returns the day...

1 minute read.

Excel VBA IsEmpty Function

VBA IsEmpty Function: The IsEmpty function in VBA returns a Boolean value showing whether the specified Expression is Empty (variant has not been declared) or not. Syntax IsEmpty (Expression) Parameter Expression (required)- This parameter...

1 minute read.

Excel VBA InStrRev Function

VBA InStrRev Function: The InStrRev function in Excel VBA returns an integer representing the position of a substring within the specified string if the substring is fount else it returns...

2 minutes read.

Excel VBA DateValue Function

The DateValue function in VBA returns a VBA Date from the given String representation of a date wherein the time information is ignored. It is unable to interpret dates that include the...

1 minute read.

UserForm and its Properties Excel VBA

UserForm and its Properties Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or...

11 minutes read.