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.
- 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
Output:
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
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
Output
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
Output
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
Output:
Some Other useful Regex Patterns
- 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,})