Using Regular Expressions in VBA

[Evan Marks]

Is there a way to use regular expressions in Excel VBA code? I notice that
there is a "Microsoft VBscript Regular Expressions (v1.0 & 5.5) on the Tools

References window of the Visual Basic Editor.

Thanks.

-em

 

[Bill James]

Yes. You set a reference to the 5.5 library. Here is an
example of its use (examples cleans currency expressions):


Dim regex As RegExp

Set regex = New RegExp
regex.Global = True
regex.Pattern = "[^$.,0-9]" ' Strip non currency chars
S = regex.Replace(S, "")
regex.Pattern = "[^0-9]" ' Strip non-digits
S = regex.Replace(S, "")

 

[Evan Marks]

Is there somewhere to get a description of ALL of this "module's"
functionality? The world of regular expressions is pretty vast. Is there
some online reference, or something squirreled away on the Office
installation CD's?

-em

Yes. You set a reference to the 5.5 library. Here is an
example of its use (examples cleans currency expressions):


Dim regex As RegExp

Set regex = New RegExp
regex.Global = True
regex.Pattern = "[^$.,0-9]" ' Strip non currency chars
S = regex.Replace(S, "")
regex.Pattern = "[^0-9]" ' Strip non-digits
S = regex.Replace(S, "")

-----Original Message-----
Is there a way to use regular expressions in Excel VBA code? I notice that
there is a "Microsoft VBscript Regular Expressions (v1.0 & 5.5) on the Tools

Thanks.

-em


.

 

[Bill James]

Look in VBSCRIP5.CHM and search for "Regular Expressions".

-----Original Message-----
Is there somewhere to get a description of ALL of this "module's"
functionality? The world of regular expressions is pretty vast. Is there
some online reference, or something squirreled away on the Office
installation CD's?

-em

Yes. You set a reference to the 5.5 library. Here is an
example of its use (examples cleans currency expressions):


Dim regex As RegExp

Set regex = New RegExp
regex.Global = True
regex.Pattern = "[^$.,0-9]" ' Strip non currency chars
S = regex.Replace(S, "")
regex.Pattern = "[^0-9]" ' Strip non-digits
S = regex.Replace(S, "")

-----Original Message-----
Is there a way to use regular expressions in Excel VBA code? I notice that
there is a "Microsoft VBscript Regular Expressions

(v1.0
& 5.5) on the Tools

References window of the Visual Basic Editor.

Thanks.

-em


.

.

 

[Tushar Mehta]

In addition to the suggestions already posted, here's what I use:
Regular Expression (RegExp) Object
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/script56/html/vsobjregexp.asp

and

Introduction to Regular Expressions
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/script56/html/vtoriVBScript.asp

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Look in VBSCRIP5.CHM and search for "Regular Expressions".

-----Original Message-----
Is there somewhere to get a description of ALL of this "module's"
functionality? The world of regular expressions is pretty vast. Is there
some online reference, or something squirreled away on the Office
installation CD's?

-em

Yes. You set a reference to the 5.5 library. Here is an
example of its use (examples cleans currency expressions):


Dim regex As RegExp

Set regex = New RegExp
regex.Global = True
regex.Pattern = "[^$.,0-9]" ' Strip non currency chars
S = regex.Replace(S, "")
regex.Pattern = "[^0-9]" ' Strip non-digits
S = regex.Replace(S, "")

-----Original Message-----
Is there a way to use regular expressions in Excel VBA
code? I notice that
there is a "Microsoft VBscript Regular Expressions (v1.0
& 5.5) on the Tools
References window of the Visual Basic Editor.

Thanks.

-em


.

.