Regular Expressions in MS Access VBA?

Question

I definitely like MS Access as an RAD-Tool for small-scope data-driven applications. But one thing I really miss as a .Net-Developer is Regular Expressions. They really come in handy when validating user input. I really do not know why Microsoft did not put these in the standard VBA-Library.

Is there a way to use Regular Expressions in MS Access VBA?

score 27 · Accepted Answer · answered Sep 22 '10 at 15:22

27

You can use the VBScript Regex Object by adding a reference to the Microsoft VBScript Regular Expressions library.

Example usage:

Dim szLine As String  
Dim regex As New RegExp  
Dim colregmatch As MatchCollection  

With regex  
   .MultiLine = False  
   .Global = True  
   .IgnoreCase = False  
End With  

szLine = "Analyzed range (from-to)  10  100"  

regex.Pattern = "^Analyzed range"  
If regex.Test(szLine) Then  
   regex.Pattern = ".*?([0-9]+).*?([0-9]+)"  
   Set colregmatch = regex.Execute(szLine)  

   'From  
    Debug.Print colregmatch.Item(0).submatches.Item(0)  
    'To  
    Debug.Print colregmatch.Item(0).submatches.Item(1)  
End If

Source: http://mark.biek.org/blog/2009/01/regular-expressions-in-vba/

answered Sep 22 '10 at 15:22

BenV

12,052
13
64
92

5

I'd strongly recommend using late binding for this. – David-W-Fenton Sep 23 '10 at 21:33
What is late binding? @David-W-Fenton – Thameem Dec 20 '19 at 14:31
@Thameem https://sourcedaddy.com/ms-access/late-binding.html – Göran May 06 '21 at 08:50

score 4 · Answer 2 · edited Jun 26 '19 at 23:35

4

You can use CreateObject("vbscript.regexp") or just reference the scripting library.

edited Jun 26 '19 at 23:35

MarredCheese

17,541
8
92
91

answered Sep 22 '10 at 15:22

Fionnuala

90,370
7
114
152

Regular Expressions in MS Access VBA?

2 Answers2

Linked

Related