Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word

Tags:

I did lot of Googling to get a proper answer on how to use or start using Regular Expressions in VBA.

At last I got it so I'd like to share my knowledge with you guys. Please correct me if I am wrong.

like image 331
Jerry James Avatar asked Aug 03 '14 06:08

Jerry James


People also ask

Can you use regular expressions in VBA?

Regular Expressions (Regex) are a pattern that is used to find matches within strings. This is very useful when you want to validate, replace, or extract data from strings. In Excel, regular expressions can be used when working with VBA.

What is a RegExp object?

RegExp Object A regular expression is a pattern of characters. The pattern is used to do pattern-matching "search-and-replace" functions on text. In JavaScript, a RegExp Object is a pattern with Properties and Methods.


1 Answers

By default Regular Expression option is disabled in word 2007, to enable that plase do following steps,

1). Go to Tools > References as shown below. enter image description here

2). Now put a tick on "Microsoft VBScript Regular Expressions 5.5" option and then press oh as shown below. enter image description here

3). Now onward you can create a RegExp object in your VBA script. You can verify it be searching in object data base as explained below. View > Object Browser ( Or press F2) , as shown below.

enter image description here

and search for RegExp object

enter image description here

4). The RegExp object uses regular expressions to match a pattern. The following properties are provided by RegExp. These properties set the pattern to compare the strings that are passed to the RegExp instance:

a. Pattern: A string that defines the regular expression.

b. IgnoreCase: A Boolean property that indicates whether you must test the regular expression against all possible matches in a string.

c. Global: Sets a Boolean value or returns a Boolean value that indicates whether a pattern must match all the occurrences in a whole search string, or whether a pattern must match just the first occurrence.

RegExp provides the following methods to determine whether a string matches a particular pattern of a regular expression:

d. Test: Returns a Boolean value that indicates whether the regular expression can successfully be matched against the string.

e. Execute: Returns a MatchCollection object that contains a Match object for each successful match.

Please find a simile example for RexExp provided in Microsoft msdn forum.

Function TestRegExp(myPattern As String, myString As String)
   'Create objects.
   Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim colMatches   As MatchCollection
   Dim RetStr As String

   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = myPattern

   ' Set Case Insensitivity.
   objRegExp.IgnoreCase = True

   'Set global applicability.
   objRegExp.Global = True

   'Test whether the String can be compared.
   If (objRegExp.Test(myString) = True) Then

   'Get the matches.
    Set colMatches = objRegExp.Execute(myString)   ' Execute search.

    For Each objMatch In colMatches   ' Iterate Matches collection.
      RetStr = RetStr & "Match found at position "
      RetStr = RetStr & objMatch.FirstIndex & ". Match Value is '"
      RetStr = RetStr & objMatch.Value & "'." & vbCrLf
    Next
   Else
    RetStr = "String Matching Failed"
   End If
   TestRegExp = RetStr
End Function

I hope it might help full for some one, because i wasted almost half a day on it.

Thanks

like image 65
Jerry James Avatar answered Sep 19 '22 11:09

Jerry James