Microsoft Beefs Up Vbscript With Regular Expressions
Regular-Expressions (RegExp)is something I bumped into when my string manipulation requirements jumped to an advanced level. Before I started using this powerhouse, all my string manipulation functions involved maneuvering through a dozen for-loops; and tackling hundreds of Left-Right-Mid-InStr functions. RegExp is a design matching arrangement that allows you to perform avant-garde string manipulations very hands. It may have a while to get used to it, just one time you become the hang of it, the possibilities are countless.
To tap into this super power, a Reference to "Microsoft VBScript Regular Expressions 5.five" has to be added to the VBA Project. Click hither to see how to add the reference to your project. Unfortunately, the version implemented in Visual Basics, using VBScript Scripting Library, does not include Regular Expressions in its entirety. Some of the new features have not been implemented yet. Click hither to check out which features are available.
RegExp in VBA is fashioned as an Object. Pattern, Global and IgnoreCase are the properties you might need to apply. Replace is the one method that I use very oftentimes. Please remember that the principal purpose of this post is the ease you into getting the hang of the Supersede method.
Steps to using the Supervene upon Method:
- Declare and Initialize the RegExp Object
- Set the IgnoreCase property to False, if you do not want the pattern to exist case sensitive. It is set to True past default
- Gear up the Global property to True, to supplant all instances of matched pattern. It is set to Simulated past default i.e. simply the showtime instance is replaced.
- This is the most important footstep. Set the Pattern property. I suggest going through the links I accept posted below to get the hang of using this.
- Finally, nosotros apply the Replace method and assign it to a string variable.
It is a good thought to wrap this into a nice dandy function, so you do non have to declare new objects or modify the properties of existing ones explicitly. This part can be used inside VBA and tin also be used in the Excel Formula Bar; nosotros will see examples of both. I can't imagine doing string manipulations in Excel without RegExp.I intend to post a lot of other user defined functions in the future that use the following function:
'==================================================================================== 'Wrapper function to impliment the Supersede method of the RegExp object 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Engagement: 08 Dec 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Function RegExpReplace(ByVal WhichString As Cord, _ ByVal Pattern As String, _ ByVal ReplaceWith Every bit String, _ Optional ByVal IsGlobal As Boolean = True, _ Optional ByVal IsCaseSensitive Every bit Boolean = True) As String 'Declaring the object Dim objRegExp As Object 'Initializing an Instance Set objRegExp = CreateObject("vbscript.regexp") 'Setting the Backdrop objRegExp.Global = IsGlobal objRegExp.Pattern = Pattern objRegExp.IgnoreCase = Not IsCaseSensitive 'Execute the Replace Method RegExpReplace = objRegExp.Replace(WhichString, ReplaceWith) End Function
This function can exist wrapped inside other functions to perform string manipulations hands. Below are some very simple examples of how the above role could be used in your VBA functions.
Function to extract only numbers in a cord:
'==================================================================================== 'Function to extract only the numbers in a string using RegExp 'Writer : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 08 December 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Role OnlyNumbers(ByVal WhichString As String) As Variant OnlyNumbers = CDbl(RegExpReplace(WhichString, _ "[^0-9]", vbNullString, True)) Cease Function
The screenshot beneath contains few examples of RegExpReplace() being used in the Excel interface. I would advise experimenting with the Pattern and Replace-With strings to test your ideas; and finally wrap them into user defined office of your ain.
Delight experience gratis to share your ideas here. Finally, I plant the following links very useful and I hope they aid you too:
- VB/VBA regular expression
- Microsoft Beefs Upward VBScript with Regular Expressions
- Regular Expression Syntax (Scripting)
- Regular Expression Object Properties and Methods (VBScript)
Source: https://strugglingtoexcel.com/2013/12/08/introduction-to-regular-expressions-in-vba/
0 Response to "Microsoft Beefs Up Vbscript With Regular Expressions"
Post a Comment