So a short while ago Mike Wolfe mentioned that Microsoft had announced they were going to phase out and retire VBScript
Microsoft Announces the Death of VBScript
As seems to be their new modus operandi, Microsoft has quietly rolled a hand grenade into the VBA development world with very little fanfare.
and the implications of this could be far reaching impacting all sorts of things, most notably the possible loss of:
- File System Object (FSO)
- Regular Expressions (RegEx)
- and more
All that has officially been stated is
Effective October 2023, VBScript is deprecated. In future releases of Windows, VBScript will be available as a feature on demand before its removal from the operating system.Microsoft
VBScript will be available as a feature on demand before being retired in future Windows releases. Initially, the VBScript feature on demand will be preinstalled to allow for uninterrupted use while you prepare for the retirement of VBScript.Microsoft
Now let’s be clear here, we are all still waiting for clarifications to come from Microsoft on the subject so we can truly know 100% what will be lost when this does actually occurs, which libraries are going to disappear. Everything up until now is simply logical assumptions based on what we know of the libraries, but no official specifics have truly been given to date. (and yes, many people have asked for clarifications, but none have been given thus far)
After years of waiting on Microsoft to publicly provide information about various subjects and it never coming to bare or taking way too long, I decided not to wait on this subject matter. I wanted to try and prepare myself as best I could should such things occur. Plan now rather than panic later!
As such, I’ve decided to share my work here in the hopes it might help others.
Initially, I decided to concentrate on seeing if there wasn’t another approach that could be used in VBA to provided RegEx functionality without the use of the VBScript RegulareExpression library (CreateObject(“VBScript.RegExp”)). For this, I turned towards my web development experience.
RegEx is common in web development and I knew that I could probably do something through the web browser controls, but I didn’t want to have to require some hidden form … That just wouldn’t be ideal. Then I remembered having done some JS via code when I wanted to extract the base URL from a full URL which employed the used of the Script Control.
Now, I didn’t see the Script Control (msscript.ocx) mentioned in Mike’s article, so I’m hoping it isn’t part of the VB Script deprecation!
With this knowledge in hand, I was able to gain RegEx functionalities without the need of the libraries mentioned in Mike’s article, nor use any database objects to make it all happen. This approach should work in any VBA environment (Access, Excel, Outlook, PowerPoint, Word, …).
Validation
If we want to perform RegEx validations, we can now do something like:
'---------------------------------------------------------------------------------------
' Procedure : JSRegEx_Validate
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Performs RegEx validation via the Script Control ocx instead of the
' VBScript RegulareExpression.
' It returns True if it is valid, otherwise it returns False
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Micrsoft Script Control X.X
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPattern : The RegEx pattern to test the object against
' sPatternFlags : The RegEx pattern flags to use
' vInput : The Object to validate against the specified pattern
'
' Usage:
' ~~~~~~
' Validate URL
' ? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.google.ca")
' Returns => True
'
' ? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.googleca")
' Returns => False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2024-03-02 Initial Release
'---------------------------------------------------------------------------------------
Public Function JSRegEx_Validate(ByVal sPattern As String, _
ByVal sPatternFlags As String, _
ByVal vInput As Variant) As String
On Error GoTo Error_Handler
#Const ScriptControl_EarlyBind = False 'Should normally be in the Module header
#If ScriptControl_EarlyBind = True Then
Dim oSC As MSScriptControl.ScriptControl
Set oSC = New ScriptControl
#Else
Static oSC As Object
Set oSC = CreateObject("ScriptControl")
#End If
Dim sOutput
If Not oSC Is Nothing Then
With oSC
.Language = "JScript"
' '? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.google.ca")
' .AddCode "function regEx_Pattern_Test(myInput) {" & _
' " var regEx_Pattern = " & sPattern & sPatternFlags & ";" & _
' " return regEx_Pattern.test(myInput);" & _
' "}"
' sOutput = .Run("regEx_Pattern_Test", vInput)
'? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.google.ca")
.AddCode "function regEx_Pattern_Test(myPattern, myPatternFlags, myInput) {" & _
" var regEx_Pattern = eval(myPattern + myPatternFlags);" & _
" return regEx_Pattern.test(myInput);" & _
"}"
sOutput = .Run("regEx_Pattern_Test", sPattern, sPatternFlags, vInput)
End With
End If
JSRegEx_Validate = sOutput
Error_Handler_Exit:
On Error Resume Next
Set oSC = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: JSRegEx_Validate" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
or
'---------------------------------------------------------------------------------------
' Procedure : JSRegEx_Validate
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Performs RegEx validation via the Script Control ocx instead of the
' VBScript RegulareExpression.
' It returns True if it is valid, otherwise it returns False
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Micrsoft Script Control X.X
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPattern : The RegEx pattern to test the object against
' sPatternFlags : The RegEx pattern flags to use
' vInput : The Object to validate against the specified pattern
'
' Usage:
' ~~~~~~
' Validate URL
' ? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.google.ca")
' Returns => True
'
' ? JSRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.googleca")
' Returns => False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2024-03-02 Initial Release
'---------------------------------------------------------------------------------------
Public Function JSRegEx_Validate(ByVal sPattern As String, _
ByVal sPatternFlags As String, _
ByVal vInput As Variant) As String
On Error GoTo Error_Handler
'#Const ScriptControl_EarlyBind = False 'Should normally be in the Module header
#If ScriptControl_EarlyBind = True Then
Dim oSC As MSScriptControl.ScriptControl
Set oSC = New ScriptControl
#Else
Static oSC As Object
Set oSC = CreateObject("ScriptControl")
#End If
Dim sOutput
If Not oSC Is Nothing Then
With oSC
.Language = "JScript"
'? JSRegEx_Validate("^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$", "igm", "www.google.ca")
.AddCode "function regEx_Pattern_Test(myPattern, myPatternFlags, myInput) {" & _
" var regEx_Pattern = new RegExp(myPattern, myPatternFlags);" & _
" return regEx_Pattern.test(myInput);" & _
"}"
sOutput = .Run("regEx_Pattern_Test", sPattern, sPatternFlags, vInput)
End With
End If
JSRegEx_Validate = sOutput
Error_Handler_Exit:
On Error Resume Next
Set oSC = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: JSRegEx_Validate" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Validate a URL
You could use the above by doing:
? JSRegEx_Validate("^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$", "igm", "www.google.ca")
which will return True
? JSRegEx_Validate("^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$", "igm", "www.googleca")
which will return False
We could also create a wrapper helper function for various common tests: url, e-mail, phone, … to simply coding/usage. For instance, to validate URLs, we could do:
Public Function JSRegEx_Validate_URL(ByVal sURL As Variant) As Boolean
Const sPattern = "/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/"
Const sPatternFlags = "igm"
JSRegEx_Validate_URL = JSRegEx_Validate(sPattern, sPatternFlags, sURL)
End Function
and then validation become much simpler and only requires:
? JSRegEx_Validate_URL("www.google.ca") 'Returns True
? JSRegEx_Validate_URL("www.googleca") 'Returns False
Extracting Matches
Similarly to the above, we can easily develop a universal RegEx Match function that uses the Script Control ocx to find and extract segments of a larger string that match a specified pattern. So not just test if it conforms, but extract the matching segment.
We can do something like:
'---------------------------------------------------------------------------------------
' Procedure : JSRegEx_Matches
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Uses Script Control ocx RegEx to extract the matches of the defined
' pattern.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Micrsoft Script Control X.X
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPattern : The RegEx pattern to test the object against and extract macthes of
' sPatternFlags : The RegEx pattern flags to use
' vInput : The Object to validate against the specified pattern
' sDelimiter : Delimiter to be used to separate the matches in the returned string
' If omitted , is used
'
' Usage:
' ~~~~~~
' Extract E-mail addresses from a string
' ? JSRegEx_Matches("([a-zA-ZF0-9\u00C0-\u017F._-]+@[a-zA-Z0-9\u00C0-\u017F._-]+\.[a-zA-Z0-9\u00C0-\u017F_-]+)", _
' "gmi", _
' "some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .")
' Returns => dani@ccc.com, marth@marth.ru, carl@hotmail.com
'
' ? JSRegEx_Matches("([a-zA-ZF0-9\u00C0-\u017F._-]+@[a-zA-Z0-9\u00C0-\u017F._-]+\.[a-zA-Z0-9\u00C0-\u017F_-]+)", _
' "gmi", _
' "some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .", _
' "~")
' Returns => dani@ccc.com~marth@marth.ru~carl@hotmail.com
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2024-03-02 Initial Release
'---------------------------------------------------------------------------------------
Public Function JSRegEx_Matches(ByVal sPattern As String, _
ByVal sPatternFlags As String, _
ByVal vInput As Variant, _
Optional sDelimiter As String = ", ") As String
On Error GoTo Error_Handler
'#Const ScriptControl_EarlyBind = False 'Should normally be in the Module header
#If ScriptControl_EarlyBind = True Then
Dim oSC As MSScriptControl.ScriptControl
Set oSC = New ScriptControl
#Else
Static oSC As Object
Set oSC = CreateObject("ScriptControl")
#End If
Dim sScript As String
Dim sOutput As String
If Not oSC Is Nothing Then
With oSC
.Language = "JScript"
sScript = "function patternTest(myPattern, myPatternFlags, myInput, myDelimiter) {" & _
" var reg = new RegExp(myPattern, myPatternFlags);" & _
" var output = '';" & _
" var matches = myInput.match(reg);" & _
" if(matches && matches.length != 0){" & _
" for (var i = 0; i < matches.length; i++) {" & _
" output = output + myDelimiter + matches[i];" & _
" }" & _
" output = output.slice(myDelimiter.length);" & _
" }" & _
" return output;" & _
"}"
'Debug.Print sScript 'For Debugging
.AddCode sScript
sOutput = .Run("patternTest", sPattern, sPatternFlags, vInput, sDelimiter)
End With
End If
JSRegEx_Matches = sOutput
Error_Handler_Exit:
On Error Resume Next
Set oSC = Nothing
Exit Function
Error_Handler:
Debug.Print Err.Number, Err.Description
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: JSRegEx_Matches" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Extracting E-mail Addresses
Below is a simple example of how this could be used to extract all the e-mail addresses from a string.
? JSRegEx_Matches("([a-zA-ZF0-9\u00C0-\u017F._-]+@[a-zA-Z0-9\u00C0-\u017F._-]+\.[a-zA-Z0-9\u00C0-\u017F_-]+)", _
"gmi", _
"some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .")
which then would return
dani@ccc.com, marth@marth.ru, carl@hotmail.com
As with the previous section, we could simplify frequent usage by creating a simple wrapper helper functions like:
Public Function JSRegEx_Extract_Emails(ByVal sInput As String) As String
Const sPattern = "([a-zA-ZF0-9\u00C0-\u017F._-]+@[a-zA-Z0-9\u00C0-\u017F._-]+\.[a-zA-Z0-9\u00C0-\u017F_-]+)"
Const sPatternFlags = "igm"
Const sDelim = "||"
JSRegEx_Extract_Emails = JSRegEx_Matches(sPattern, sPatternFlags, sInput, sDelim)
End Function
and which we would call by doing:
?JSRegEx_Extract_Emails("some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .")
which returns => dani@ccc.com||marth@marth.ru||carl@hotmail.com
Or we could instead create a wrapper like:
Public Function JSRegEx_Extract_Emails(ByVal sInput As String) As Variant
Const sPattern = "([a-zA-ZF0-9\u00C0-\u017F._-]+@[a-zA-Z0-9\u00C0-\u017F._-]+\.[a-zA-Z0-9\u00C0-\u017F_-]+)"
Const sPatternFlags = "igm"
Const sDelim = "||"
JSRegEx_Extract_Emails = Split(JSRegEx_Matches(sPattern, sPatternFlags, sInput, sDelim), sDelim)
End Function
which we could use like:
Sub JSRegEx_Extract_Emails2_Test()
Dim aEmails As Variant
Dim i As Long
Const myString = "some text with dani@ccc.com and marth@marth.ru. carl@hotmail.com Some more text ."
aEmails = JSRegEx_Extract_Emails(myString)
For i = 0 To UBound(aEmails)
Debug.Print aEmails(i)
Next i
End Sub
which would then output
dani@ccc.com marth@marth.ru carl@hotmail.com
Notes Regarding Using The Script Control
While developing my code and testing I discovered a couple things you should be aware of should you wish to do any development using the Script Control, most notably:
- JScript does not accept // for inline comments. We must use the /* */ syntax.
- JScript does not support ‘let’, ‘const’, … for declaring variables. So use ‘var’ for pretty much everything!
Basically the JS version is old and isn’t compatible with any of the newer syntax. So keep things as simple as possible even if it breaks what we would consider a best practice today. Otherwise you’ll get all sorts of very weird errors that truly do not indicate the true nature of the problem with your code, errors like:
Error 1004 – Expected ‘;’
Error 1009 – Expected ‘}’
If you are looking into Script Control coding with JScript, you may like to look over:
An Alternative Technique – Using Microsoft HTML Object Library (MSHTML)
I also wanted to quickly demonstrate that we could even use the MSHTML library. For instance, below is an example of a validation function using MSHTML.
'---------------------------------------------------------------------------------------
' Procedure : MSHTMLRegEx_Validate
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Performs RegEx validation via the Microsoft HTML Object Library instead of
' the VBScript RegulareExpression.
' It returns True if it is valid, otherwise it returns False
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft HTML Object Library (mshtml.tlb)
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPattern : The RegEx pattern to test the object against
' sPatternFlags : The RegEx pattern flags to use
' vInput : The Object to validate against the specified pattern
'
' Usage:
' ~~~~~~
' Validate URL
' ? MSHTMLRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.google.ca")
' Returns => True
'
' ? MSHTMLRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "www.googleca")
' Returns => False
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2024-03-02 Initial Release
'---------------------------------------------------------------------------------------
Public Function MSHTMLRegEx_Validate(ByVal sPattern As String, _
ByVal sPatternFlags As String, _
ByVal vInput As Variant) As Boolean
#Const HF_EarlyBind = True 'True => Early Binding -> Microsoft HTML Object Library (mshtml.tlb)
'False => Late Binding
'Typically a Global Module Level Variable
#If HF_EarlyBind = True Then
Dim oHTMLFile As MSHTML.HTMLDocument
Dim oElem As MSHTML.HTMLGenericElement
Dim oLink As MSHTML.HTMLLinkElement
Dim oTable As MSHTML.HTMLTable
Dim oTr As MSHTML.HTMLTableRow
Dim oTd As MSHTML.HTMLTableCell
Set oHTMLFile = New MSHTML.HTMLDocument
#Else
Dim oHTMLFile As Object
Dim oElem As Object
Dim oLink As Object
Dim oTable As Object
Dim oTr As Object
Dim oTd As Object
Set oHTMLFile = CreateObject("HTMLFile")
#End If
oHTMLFile.body.innerHTML = "" 'Clear the default paragraph line
Set oElem = oHTMLFile.createElement("p")
Call oElem.setAttribute("id", "result")
Call oHTMLFile.body.appendChild(oElem)
Set oElem = oHTMLFile.createElement("script")
Call oElem.setAttribute("type", "text/javascript") 'Not strictly necessary as this is the default value anyways
oElem.innerText = "function regEx_Pattern_Test() {" & _
" var regEx_Pattern = " & sPattern & sPatternFlags & ";" & _
" var myInput = '" & vInput & "';" & _
" document.getElementById('result').innerText = regEx_Pattern.test(myInput);" & _
"}"
Call oHTMLFile.body.appendChild(oElem)
Call oHTMLFile.parentWindow.execScript("regEx_Pattern_Test();")
'Debug.Print oHTMLFile.body.innerHTML ' For debugging
MSHTMLRegEx_Validate = CBool(oHTMLFile.getElementById("result").innerText)
Set oHTMLFile = Nothing
End Function
and it could be called by doing:
? MSHTMLRegEx_Validate("/^(https?:\/\/)?([\da-z\.-]+\.[a-z\.]{2,6}|[\d\.]+)([\/:?=&#]{1}[\da-z\.-]+)*[\/\?]?$/", "igm", "https://www.google.ca")
Returns => True
for matches, we could use a function like:
'---------------------------------------------------------------------------------------
' Procedure : MSHTMLRegEx_Matches
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Uses Microsoft HTML Object Library to run RegEx to extract the matches of
' the defined pattern.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft HTML Object Library (mshtml.tlb)
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPattern : The RegEx pattern to test the object against and extract macthes of
' sPatternFlags : The RegEx pattern flags to use
' vInput : The Object to validate against the specified pattern
' sDelimiter : Delimiter to be used to separate the matches in the returned string
' If omitted , is used
'
' Usage:
' ~~~~~~
' Extract E-mail addresses from a string
' ? MSHTMLRegEx_Matches("[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)", _
' "gmi", _
' "some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .")
' Returns => dani@ccc.com, marth@marth.ru, carl@hotmail.com
'
' ? MSHTMLRegEx_Matches("[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)", _
' "gmi", _
' "some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .", _
' "~")
' Returns => dani@ccc.com~marth@marth.ru~carl@hotmail.com
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2024-03-21 Initial Release
'---------------------------------------------------------------------------------------
Public Function MSHTMLRegEx_Matches(ByVal sPattern As String, _
ByVal sPatternFlags As String, _
ByVal vInput As Variant, _
Optional sDelimiter As String = ", ") As String
'#Const HF_EarlyBind = True 'True => Early Binding / False => Late Binding
'Typically a Global Module Level Variable
#If HF_EarlyBind = True Then
Dim oHTMLFile As MSHTML.HTMLDocument
Dim oElem As MSHTML.HTMLGenericElement
Dim oLink As MSHTML.HTMLLinkElement
Dim oTable As MSHTML.HTMLTable
Dim oTr As MSHTML.HTMLTableRow
Dim oTd As MSHTML.HTMLTableCell
Set oHTMLFile = New MSHTML.HTMLDocument
#Else
Dim oHTMLFile As Object
Dim oElem As Object
Dim oLink As Object
Dim oTable As Object
Dim oTr As Object
Dim oTd As Object
Set oHTMLFile = CreateObject("HTMLFile")
#End If
oHTMLFile.body.innerHTML = "" 'Clear the default line
Set oElem = oHTMLFile.createElement("p")
Call oElem.setAttribute("id", "result")
Call oHTMLFile.body.appendChild(oElem)
Set oElem = oHTMLFile.createElement("script")
Call oElem.setAttribute("type", "text/javascript") 'Not strictly necessary as this is the default value anyways
'*** Don't use vbcrlf ... as it will erroneously place
tags in your code!
oElem.innerText = "function patternTest() {" & _
" var myPattern = '" & sPattern & "';" & _
" var myPatternFlags = '" & sPatternFlags & "';" & _
" var myInput = '" & vInput & "';" & _
" var myDelimiter = '" & sDelimiter & "';" & _
" var reg = new RegExp(myPattern, myPatternFlags);" & _
" var output = '';" & _
" var matches = myInput.match(reg);" & _
" if(matches && matches.length != 0){" & _
" for (var i = 0; i < matches.length; i++) {" & _
" output = output + myDelimiter + matches[i];" & _
" }" & _
" output = output.slice(myDelimiter.length);" & _
" }" & _
" document.getElementById('result').innerText = output;" & _
" return true;" & _
"}"
Call oHTMLFile.body.appendChild(oElem)
Call oHTMLFile.parentWindow.execScript("patternTest();")
MSHTMLRegEx_Matches = oHTMLFile.getElementById("result").innerText
Set oHTMLFile = Nothing
End Function
which would could use by doing:
MSHTMLRegEx_Matches("[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)", "gmi", "some text with dani@ccc.com and marth@marth.ru. " & Chr(10) & Chr(13) & "carl@hotmail.com Some more text .")
and which would return:
dani@ccc.com, marth@marth.ru, carl@hotmail.com
Conclusion
As you can see, we may have a relatively simple workaround to the loss of VBScript when it comes to RegEx in VBA.
I should emphasize the benefit of using Self-Healing Object Variable (SHOV) with any repetitive procedure. Thus, the above would likely all benefit from implementing SHOV. If you're not already familiar with SHOV, review the following article to learn more:
Self-Healing Object Variables
So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables! So What Are Self-Healing Object Variables? In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session. If reset, they will reinitialize again automatically. They are like Global Variables on steroids. It is just one more step you can take as a developer to improve the overall performance and give your application a little boost, and this for any VBA application (not just Access)! My first…
I just hope msscript.ocx is not part of vbscript!
Also, there's is always the possibility of running such code through the web browser controls or some other Web library. We could even turn to automating RegEx via PowerShell, but this wouldn't be my first choice though because there is a performance hit associated with PowerShell automation. So worst case scenario, there is still at least several other possible workarounds available to us to explore.
There is more that can be done with this approach, but that will be an article for another time.
Some Further Resources To Explore
Regular expressions - JavaScript | MDN
Regular expressions are patterns used to match character combinations in strings. In JavaScript, regular expressions are also objects. These patterns are used with the exec() and test() methods of RegExp, and with the match(), matchAll(), replace(), replaceAll(), search(), and split() methods of String. This chapter describes JavaScript regular expressions.
Page History
| Date | Summary of Changes |
|---|---|
| 2024-03-18 | Initial Release |
| 2024-03-21 | Added MSHTMLRegEx_Matches sample function to the Alternative Approach section |