How to use Regular Expressions of VBScript?

[Danny J. Lesandrini]

Anyone know how to get started using the Regular Expressions stuff
of VBScript from an Access VBA Code module?

If not the VBScript RegExp, then do you have another suggestion for
testing strings to see if they match a particularly complex set of rules?

 

[Douglas J Steele]

Take a look at what John Nurick provided as a sample at
http://www.mvps.org/access/modules/mdl0063.htm, or my October, 2003 "Access
Answers" column (which you can download at
http://www.accessmvp.com/DJSteele/SmartAccess.html)

 

[DBG]

I'd guess you have found this already but if not, this is of relevance as well
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

I don't use the late binding method in my project, I just hooked up the
RegEx using a reference to the module mentioned at the top of John's article.

Here's a really simple example:

Dim strAString as String
strAString = "111222AB"
Dim re As New RegExp
re.Pattern = "[A-Z][a-z]"
re.IgnoreCase = True
If (Not re.test(strAString)) Then
' String didn't contain a letter, do something
' Maybe Add or subtract
else
' String contained a letter do something else
end if

In this example, the else case would fire because our string has a letter
and our pattern says look for any letter.

-David

 

[Danny J. Lesandrini]

David, the question I have is this: What library reference do I need to set in order
to use the RegExp object? I tried this code, but it failed to compile because the
object reference couldn't be found.

 

[DBG]

I pointed to John's article which Douglas had linked for the name of it, but
its:
VBScript regular expression object (v 5.5)
Which doesn't mention that It's
C:\Windows\system32\vbscript.dll
unless I browsed the article too quickly.
should do it

-David

David, the question I have is this: What library reference do I need to set in order
to use the RegExp object? I tried this code, but it failed to compile because the
object reference couldn't be found.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

I'd guess you have found this already but if not, this is of relevance as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

I don't use the late binding method in my project, I just hooked up the
RegEx using a reference to the module mentioned at the top of John's article.

Here's a really simple example:

Dim strAString as String
strAString = "111222AB"
Dim re As New RegExp
re.Pattern = "[A-Z][a-z]"
re.IgnoreCase = True
If (Not re.test(strAString)) Then
' String didn't contain a letter, do something
' Maybe Add or subtract
else
' String contained a letter do something else
end if

In this example, the else case would fire because our string has a letter
and our pattern says look for any letter.

-David

 

[Douglas J Steele]

I never bother setting a reference, preferring to use Late Binding.

Dim reCurr As Object

Set reCurr = CreateObject("VBScript.RegExp")

If you're determined to use Early Binding, I do talk about how to set the
reference in the Smart Access article I cited (AFAIK, the only way to add
the reference is using the AddFromGUID method.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

David, the question I have is this: What library reference do I need to set in order
to use the RegExp object? I tried this code, but it failed to compile because the
object reference couldn't be found.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

I'd guess you have found this already but if not, this is of relevance as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

I don't use the late binding method in my project, I just hooked up the
RegEx using a reference to the module mentioned at the top of John's article.

Here's a really simple example:

Dim strAString as String
strAString = "111222AB"
Dim re As New RegExp
re.Pattern = "[A-Z][a-z]"
re.IgnoreCase = True
If (Not re.test(strAString)) Then
' String didn't contain a letter, do something
' Maybe Add or subtract
else
' String contained a letter do something else
end if

In this example, the else case would fire because our string has a letter
and our pattern says look for any letter.

-David

 

[DBG]

As an afterthought I'll probably change my code to use Late Binding as well.
Although at this point most of the RegEx stuff is converted over to CLR
Stored Procedures...

-David

I never bother setting a reference, preferring to use Late Binding.

Dim reCurr As Object

Set reCurr = CreateObject("VBScript.RegExp")

If you're determined to use Early Binding, I do talk about how to set the
reference in the Smart Access article I cited (AFAIK, the only way to add
the reference is using the AddFromGUID method.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

David, the question I have is this: What library reference do I need to set in order
to use the RegExp object? I tried this code, but it failed to compile because the
object reference couldn't be found.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

I'd guess you have found this already but if not, this is of relevance as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

I don't use the late binding method in my project, I just hooked up the
RegEx using a reference to the module mentioned at the top of John's article.

Here's a really simple example:

Dim strAString as String
strAString = "111222AB"
Dim re As New RegExp
re.Pattern = "[A-Z][a-z]"
re.IgnoreCase = True
If (Not re.test(strAString)) Then
' String didn't contain a letter, do something
' Maybe Add or subtract
else
' String contained a letter do something else
end if

In this example, the else case would fire because our string has a letter
and our pattern says look for any letter.

-David

 

[DBG]

As an afterthought I'll probably change my code to use Late Binding as well.
Although at this point most of the RegEx stuff is converted over to CLR
Stored Procedures...

-David

I never bother setting a reference, preferring to use Late Binding.

Dim reCurr As Object

Set reCurr = CreateObject("VBScript.RegExp")

If you're determined to use Early Binding, I do talk about how to set the
reference in the Smart Access article I cited (AFAIK, the only way to add
the reference is using the AddFromGUID method.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

David, the question I have is this: What library reference do I need to set in order
to use the RegExp object? I tried this code, but it failed to compile because the
object reference couldn't be found.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

I'd guess you have found this already but if not, this is of relevance as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting051099.asp

I don't use the late binding method in my project, I just hooked up the
RegEx using a reference to the module mentioned at the top of John's article.

Here's a really simple example:

Dim strAString as String
strAString = "111222AB"
Dim re As New RegExp
re.Pattern = "[A-Z][a-z]"
re.IgnoreCase = True
If (Not re.test(strAString)) Then
' String didn't contain a letter, do something
' Maybe Add or subtract
else
' String contained a letter do something else
end if

In this example, the else case would fire because our string has a letter
and our pattern says look for any letter.

-David

 

[DBG]

Yeah, LIKE is a good option if you don't need to return and use substrings.
If you need to match specific parts of a string using if elseif etc. the
RegEx is probably better. Of course you could always just query the DB with
multiple LIKE queries depenant on your DB load, number of users etc.

-David