<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-ligatures:standardcontextual;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-GB link="#0563C1" vlink="#954F72" style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>Hi all,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If you use regular expressions, then this may be helpful if you want to use them as User Defined Functions (UDFs) in Excel.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Enjoy!<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Chris<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='mso-element:para-border-div;border:none;border-bottom:solid windowtext 3.0pt;padding:0cm 0cm 1.0pt 0cm'><p class=MsoNormal style='border:none;padding:0cm'>Code starts:<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Option Explicit<o:p></o:p></p><p class=MsoNormal>''' CYates, ICE-BERG Building Physics Jul23<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' Description:<o:p></o:p></p><p class=MsoNormal>'' This module contains several wrappers for VBScript regular expressions.<o:p></o:p></p><p class=MsoNormal>'' These provide User Defined Functions (UDF) to make regular expresssions accessible in Excel.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' Licence:<o:p></o:p></p><p class=MsoNormal>'' Creative Commons Attribution Licence 4.0.<o:p></o:p></p><p class=MsoNormal>'' Thanks must be given to the many contributors to sites such as<o:p></o:p></p><p class=MsoNormal>'' stackoverflow, mrexcel, vbforums, ozgrid to name but a few.<o:p></o:p></p><p class=MsoNormal>'' ChatGPT was not used, but feel free to see if it can be improved using AI.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' Disclaimer:<o:p></o:p></p><p class=MsoNormal>'' While reasonable skill and care has been exercised to provide this software as fit for purpose,<o:p></o:p></p><p class=MsoNormal>'' we do not guarantee that it is free from defects. The software is provided "as is" and you may<o:p></o:p></p><p class=MsoNormal>'' use it at your own risk. We make no warranties as to performance, merchantability, fitness for<o:p></o:p></p><p class=MsoNormal>'' a particular purpose, or any other warranties whether expressed or implied. No oral or written<o:p></o:p></p><p class=MsoNormal>'' communication from or information provided by us shall create a warranty. Under no circumstances<o:p></o:p></p><p class=MsoNormal>'' shall we be liable for direct, indirect, special, incidental, or consequential damages resulting<o:p></o:p></p><p class=MsoNormal>'' from the use, misuse, or inability to use this software, even if we have been advised of the<o:p></o:p></p><p class=MsoNormal>'' possibility of such damages.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' Revisions<o:p></o:p></p><p class=MsoNormal>'' Jul 23 Overhauled regex library for general use. Attempt to make library useable by general Excel users.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' Instructions:<o:p></o:p></p><p class=MsoNormal><span style='background:yellow;mso-highlight:yellow'>'' Ensure the reference MS VBscript regular expressions (version number 5.5 or higher) is available.</span><o:p></o:p></p><p class=MsoNormal>'' You can do this by going to Tools/References and searching in the list. If it is not ticked, scroll<o:p></o:p></p><p class=MsoNormal>'' down and tick it in the list.<o:p></o:p></p><p class=MsoNormal>'' The syntax of VBscript regular expressions is very similar to javascript and others. Resources exist<o:p></o:p></p><p class=MsoNormal>'' on the internet to assist with building and testing regular expressions.<o:p></o:p></p><p class=MsoNormal>'' This is a good one: <a href="https://www.w3schools.com/jsref/jsref_obj_regexp.asp">https://www.w3schools.com/jsref/jsref_obj_regexp.asp</a><o:p></o:p></p><p class=MsoNormal>'' and this: <a href="https://regex101.com/">https://regex101.com/</a> (set to Java 8)<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>''' There is a standard layout for functions:<o:p></o:p></p><p class=MsoNormal>'' Function reXXX (args)<o:p></o:p></p><p class=MsoNormal>'' Input checks, exit with return value error (usually #N/A) if fail<o:p></o:p></p><p class=MsoNormal>'' Do stuff and more stuff if required<o:p></o:p></p><p class=MsoNormal>'' Return value (value can be error if required)<o:p></o:p></p><p class=MsoNormal>'' End function<o:p></o:p></p><p class=MsoNormal>''' Function aguments typically take the form: Input string, pattern string, then optional arguments...<o:p></o:p></p><p class=MsoNormal>'' ignore case true or false, index of match<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Public Function reCount(strInput As String, strPattern As String, Optional icase As Boolean) As Variant<o:p></o:p></p><p class=MsoNormal> ''' counts regular expressions matching pattern.<o:p></o:p></p><p class=MsoNormal> ''' returns 0 if none found<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> If strPattern = "" Or strInput = "" Then<o:p></o:p></p><p class=MsoNormal> reCount = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> Dim regex As RegExp<o:p></o:p></p><p class=MsoNormal> Set regex = New RegExp<o:p></o:p></p><p class=MsoNormal> Dim matches As Object<o:p></o:p></p><p class=MsoNormal> With regex<o:p></o:p></p><p class=MsoNormal> .Global = True<o:p></o:p></p><p class=MsoNormal> .MultiLine = True<o:p></o:p></p><p class=MsoNormal> .IgnoreCase = icase<o:p></o:p></p><p class=MsoNormal> .Pattern = strPattern<o:p></o:p></p><p class=MsoNormal> Set matches = .Execute(strInput)<o:p></o:p></p><p class=MsoNormal> reCount = matches.Count<o:p></o:p></p><p class=MsoNormal> End With<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Set matches = Nothing<o:p></o:p></p><p class=MsoNormal> Set regex = Nothing<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>End Function<o:p></o:p></p><p class=MsoNormal>Public Function reTest(strInput As String, strPattern As String, Optional icase As Boolean) As Variant<o:p></o:p></p><p class=MsoNormal> ''' basic regular expression test<o:p></o:p></p><p class=MsoNormal> ''' returns TRUE on match<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> If strPattern = "" Or strInput = "" Then<o:p></o:p></p><p class=MsoNormal> reTest = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Dim regex As RegExp<o:p></o:p></p><p class=MsoNormal> Set regex = New RegExp<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> With regex<o:p></o:p></p><p class=MsoNormal> .Global = True<o:p></o:p></p><p class=MsoNormal> .MultiLine = True<o:p></o:p></p><p class=MsoNormal> .IgnoreCase = icase<o:p></o:p></p><p class=MsoNormal> .Pattern = strPattern<o:p></o:p></p><p class=MsoNormal> reTest = .Test(strInput)<o:p></o:p></p><p class=MsoNormal> End With<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Set regex = Nothing<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>End Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Public Function reVal(strInput As String, strPattern As String, Optional icase As Boolean, Optional nth As String = "First") As Variant<o:p></o:p></p><p class=MsoNormal>''' Values for nth can be: First, Last, or a number. The number doesn't have to be an integer, but will be rounded down to an integer.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> If strPattern = "" Or strInput = "" Then<o:p></o:p></p><p class=MsoNormal> reVal = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> Dim regex As RegExp<o:p></o:p></p><p class=MsoNormal> Set regex = New RegExp<o:p></o:p></p><p class=MsoNormal> With regex<o:p></o:p></p><p class=MsoNormal> .Global = True<o:p></o:p></p><p class=MsoNormal> .MultiLine = True<o:p></o:p></p><p class=MsoNormal> .IgnoreCase = icase<o:p></o:p></p><p class=MsoNormal> .Pattern = strPattern<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> ' only execute if at least 1 instance...<o:p></o:p></p><p class=MsoNormal> If Not .Test(strInput) Then<o:p></o:p></p><p class=MsoNormal> ' instance not found<o:p></o:p></p><p class=MsoNormal> reVal = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Else ' good to go<o:p></o:p></p><p class=MsoNormal> Dim match, matches As Object<o:p></o:p></p><p class=MsoNormal> Set matches = .Execute(strInput)<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> '' process nth argument<o:p></o:p></p><p class=MsoNormal> Dim nth_case<o:p></o:p></p><p class=MsoNormal> Select Case True<o:p></o:p></p><p class=MsoNormal> Case nth = "" ' no argument supplied<o:p></o:p></p><p class=MsoNormal> nth_case = 1<o:p></o:p></p><p class=MsoNormal> Case IsNumeric(nth) ' number supplied. It doesn't have to be integer but is rounded down<o:p></o:p></p><p class=MsoNormal> nth_case = CInt(nth)<o:p></o:p></p><p class=MsoNormal> Case UCase(nth) = "FIRST"<o:p></o:p></p><p class=MsoNormal> nth_case = 1<o:p></o:p></p><p class=MsoNormal> Case UCase(nth) = "LAST"<o:p></o:p></p><p class=MsoNormal> nth_case = matches.Count<o:p></o:p></p><p class=MsoNormal> Case Else<o:p></o:p></p><p class=MsoNormal> reVal = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End Select<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> '' nth higher than match index or index less than 1?<o:p></o:p></p><p class=MsoNormal> If matches.Count < nth_case Or _<o:p></o:p></p><p class=MsoNormal> nth_case < 1 Then<o:p></o:p></p><p class=MsoNormal> reVal = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Else ' all good!<o:p></o:p></p><p class=MsoNormal> reVal = matches(nth_case - 1).Value<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> Set matches = Nothing<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> End With<o:p></o:p></p><p class=MsoNormal> Set regex = Nothing<o:p></o:p></p><p class=MsoNormal>End Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Public Function reSub(strInput As String, strPattern As String, strRep As String, Optional icase As Boolean) As Variant<o:p></o:p></p><p class=MsoNormal>''' replaces all instances of pattern with a new string<o:p></o:p></p><p class=MsoNormal>'' it would be nice to choose the specific index to replace instead of all instances, but this is probably a job for the index function<o:p></o:p></p><p class=MsoNormal>'' this replaces the legacy function reReplace and is synonymous with Excel's SUBSTITUTE function with comparable functionality.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> If strPattern = "" Or strInput = "" Then<o:p></o:p></p><p class=MsoNormal> reSub = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Dim regex As RegExp<o:p></o:p></p><p class=MsoNormal> Set regex = New RegExp<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> With regex<o:p></o:p></p><p class=MsoNormal> .Global = True<o:p></o:p></p><p class=MsoNormal> .MultiLine = True<o:p></o:p></p><p class=MsoNormal> .IgnoreCase = icase<o:p></o:p></p><p class=MsoNormal> .Pattern = strPattern<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal>' ' only execute if at least 1 instance...<o:p></o:p></p><p class=MsoNormal>' If Not .Test(strInput) Then<o:p></o:p></p><p class=MsoNormal>' ' instance not found<o:p></o:p></p><p class=MsoNormal>' ' Neither VBscript or Excel SUBSTITUTE evaluates this as an error.<o:p></o:p></p><p class=MsoNormal>' ' However, for the sake of consistency with the other UDFs<o:p></o:p></p><p class=MsoNormal>' ' we could evaluate it to an error in this case.<o:p></o:p></p><p class=MsoNormal>' reSub = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal>'<o:p></o:p></p><p class=MsoNormal>' Else ' good to go<o:p></o:p></p><p class=MsoNormal>''' code removed...<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>'' ...to be more consistent with Excel SUBSTITUTE<o:p></o:p></p><p class=MsoNormal> reSub = .Replace(strInput, strRep)<o:p></o:p></p><p class=MsoNormal>' End If<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> End With<o:p></o:p></p><p class=MsoNormal> Set regex = Nothing<o:p></o:p></p><p class=MsoNormal>End Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Public Function reFind(strInput As String, strPattern As String, Optional icase As Boolean, Optional nth As String = "First") As Variant<o:p></o:p></p><p class=MsoNormal>''' Values for nth can be: First, Last, All (not yet implemented), or a number. The number doesn't have to be an integer, but will be rounded down to an integer.<o:p></o:p></p><p class=MsoNormal>'' this replaces the legacy function "reIndex" and has changes in functionality: new nth option, returns first match by default instead of last and index position<o:p></o:p></p><p class=MsoNormal>'' now matches Excel's FIND function.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> If strPattern = "" Or strInput = "" Then<o:p></o:p></p><p class=MsoNormal> reFind = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> Dim regex As RegExp<o:p></o:p></p><p class=MsoNormal> Set regex = New RegExp<o:p></o:p></p><p class=MsoNormal> With regex<o:p></o:p></p><p class=MsoNormal> .Global = True<o:p></o:p></p><p class=MsoNormal> .MultiLine = True<o:p></o:p></p><p class=MsoNormal> .IgnoreCase = icase<o:p></o:p></p><p class=MsoNormal> .Pattern = strPattern<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> ' only execute if at least 1 instance...<o:p></o:p></p><p class=MsoNormal> If Not .Test(strInput) Then<o:p></o:p></p><p class=MsoNormal> ' instance not found<o:p></o:p></p><p class=MsoNormal> reFind = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Else ' good to go<o:p></o:p></p><p class=MsoNormal> Dim match, matches As Object<o:p></o:p></p><p class=MsoNormal> Set matches = .Execute(strInput)<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> '' process nth argument<o:p></o:p></p><p class=MsoNormal> Dim nth_case<o:p></o:p></p><p class=MsoNormal> Select Case True<o:p></o:p></p><p class=MsoNormal> Case nth = "" ' no argument supplied<o:p></o:p></p><p class=MsoNormal> nth_case = 1<o:p></o:p></p><p class=MsoNormal> Case IsNumeric(nth) ' number supplied. It doesn't have to be integer but is rounded down<o:p></o:p></p><p class=MsoNormal> nth_case = CInt(nth)<o:p></o:p></p><p class=MsoNormal> Case UCase(nth) = "FIRST"<o:p></o:p></p><p class=MsoNormal> nth_case = 1<o:p></o:p></p><p class=MsoNormal> Case UCase(nth) = "LAST"<o:p></o:p></p><p class=MsoNormal> nth_case = matches.Count<o:p></o:p></p><p class=MsoNormal> Case Else<o:p></o:p></p><p class=MsoNormal> reFind = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal> End Select<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> '' nth higher than match index or index less than 1?<o:p></o:p></p><p class=MsoNormal> If matches.Count < nth_case Or _<o:p></o:p></p><p class=MsoNormal> nth_case < 1 Then<o:p></o:p></p><p class=MsoNormal> reFind = CVErr(xlErrNA)<o:p></o:p></p><p class=MsoNormal> Exit Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> Else ' all good!<o:p></o:p></p><p class=MsoNormal> reFind = matches(nth_case - 1).FirstIndex + 1 ' plus 1 to match functionality of FIND<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> Set matches = Nothing<o:p></o:p></p><p class=MsoNormal> End If<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal> End With<o:p></o:p></p><p class=MsoNormal> Set regex = Nothing<o:p></o:p></p><p class=MsoNormal>End Function<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>