Regex Question

  • Thread starter Thread starter William Barnes
  • Start date Start date
W

William Barnes

Using Excel 2000 under Win2K:

Is there a way in VBA to utilize regular expressions? The Like operator
doesn't seem to fully support them.
 
William -

It's my understanding that the Like operator in VBA for Excel is intended to
be used for string comparisons - could you give an example of a regular
expression?
 
A Regular Expression ("Regex") is a string with defined metacharacters
(which may vary among Regex engines) that is used by a Regex engine to
examine another string for, among other things, a match. You may wish to
refer to http://www.opengroup.org/onlinepubs/007908799/xbd/re.html or other
sources for more info. They are used extensively in Perl programming
scripts.

As an example, if the Like operator supported regexes, then I could write a
line of code that would look something like this:

If cell.value Like "[A-Za-z]+ - [0-9]+" Then
'Do Something
End If

In the above snippet, where cell is a Range Object that represents a cell on
a Worksheet, the condition would be met if the cell contained one or more
upper or lowre case alphabetic characters followed by a space, then a
hyphen, then another space, then one or more numeric characters.
 
Well it does say "The real thing is an ordinary source code module. "
Perhaps you could integrate it into applications, depending on their
distribution permission...
 
Hi William,

I'm a little late on this one, I was just doing a quick browse through
the RegExp questions

You can enable iRegExo with early binding as per Chip's suggestion or
you can use late binding as below

Dim RegExp As Object
Set RegExp = CreateObject("vbscript.regexp")

As it is a distribution I'd stick with the early binding as your RegExp
reference will be included with your file, but when I give code to
someone else I use late binding as its one less thing that the user
needs to set up


Cheers

Dave
 
Back
Top