Hi all,
If you use regular expressions, then this may be helpful if you want to use
them as User Defined Functions (UDFs) in Excel.
Enjoy!
Chris
Code starts:
Option Explicit
''' CYates, ICE-BERG Building Physics Jul23
''' Description:
'' This module contains several wrappers for VBScript regular expressions.
'' These provide User Defined Functions (UDF) to make regular expresssions
accessible in Excel.
''' Licence:
'' Creative Commons Attribution Licence 4.0.
'' Thanks must be given to the many contributors to sites such as
'' stackoverflow, mrexcel, vbforums, ozgrid to name but a few.
'' ChatGPT was not used, but feel free to see if it can be improved using
AI.
''' Disclaimer:
'' While reasonable skill and care has been exercised to provide this
software as fit for purpose,
'' we do not guarantee that it is free from defects. The software is
provided "as is" and you may
'' use it at your own risk. We make no warranties as to performance,
merchantability, fitness for
'' a particular purpose, or any other warranties whether expressed or
implied. No oral or written
'' communication from or information provided by us shall create a warranty.
Under no circumstances
'' shall we be liable for direct, indirect, special, incidental, or
consequential damages resulting
'' from the use, misuse, or inability to use this software, even if we have
been advised of the
'' possibility of such damages.
''' Revisions
'' Jul 23 Overhauled regex library for general use. Attempt to make library
useable by general Excel users.
''' Instructions:
'' Ensure the reference MS VBscript regular expressions (version number 5.5
or higher) is available.
'' You can do this by going to Tools/References and searching in the list.
If it is not ticked, scroll
'' down and tick it in the list.
'' The syntax of VBscript regular expressions is very similar to javascript
and others. Resources exist
'' on the internet to assist with building and testing regular expressions.
'' This is a good one: https://www.w3schools.com/jsref/jsref_obj_regexp.asp
'' and this: https://regex101.com/ (set to Java 8)
''' There is a standard layout for functions:
'' Function reXXX (args)
'' Input checks, exit with return value error (usually #N/A) if fail
'' Do stuff and more stuff if required
'' Return value (value can be error if required)
'' End function
''' Function aguments typically take the form: Input string, pattern string,
then optional arguments...
'' ignore case true or false, index of match
Public Function reCount(strInput As String, strPattern As String, Optional
icase As Boolean) As Variant
''' counts regular expressions matching pattern.
''' returns 0 if none found
If strPattern = "" Or strInput = "" Then
reCount = CVErr(xlErrNA)
Exit Function
End If
Dim regex As RegExp
Set regex = New RegExp
Dim matches As Object
With regex
.Global = True
.MultiLine = True
.IgnoreCase = icase
.Pattern = strPattern
Set matches = .Execute(strInput)
reCount = matches.Count
End With
Set matches = Nothing
Set regex = Nothing
End Function
Public Function reTest(strInput As String, strPattern As String, Optional
icase As Boolean) As Variant
''' basic regular expression test
''' returns TRUE on match
If strPattern = "" Or strInput = "" Then
reTest = CVErr(xlErrNA)
Exit Function
End If
Dim regex As RegExp
Set regex = New RegExp
With regex
.Global = True
.MultiLine = True
.IgnoreCase = icase
.Pattern = strPattern
reTest = .Test(strInput)
End With
Set regex = Nothing
End Function
Public Function reVal(strInput As String, strPattern As String, Optional
icase As Boolean, Optional nth As String = "First") As Variant
''' 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.
If strPattern = "" Or strInput = "" Then
reVal = CVErr(xlErrNA)
Exit Function
End If
Dim regex As RegExp
Set regex = New RegExp
With regex
.Global = True
.MultiLine = True
.IgnoreCase = icase
.Pattern = strPattern
' only execute if at least 1 instance...
If Not .Test(strInput) Then
' instance not found
reVal = CVErr(xlErrNA)
Else ' good to go
Dim match, matches As Object
Set matches = .Execute(strInput)
'' process nth argument
Dim nth_case
Select Case True
Case nth = "" ' no argument supplied
nth_case = 1
Case IsNumeric(nth) ' number supplied. It doesn't have to be
integer but is rounded down
nth_case = CInt(nth)
Case UCase(nth) = "FIRST"
nth_case = 1
Case UCase(nth) = "LAST"
nth_case = matches.Count
Case Else
reVal = CVErr(xlErrNA)
Exit Function
End Select
'' nth higher than match index or index less than 1?
If matches.Count < nth_case Or _
nth_case < 1 Then
reVal = CVErr(xlErrNA)
Exit Function
Else ' all good!
reVal = matches(nth_case - 1).Value
End If
Set matches = Nothing
End If
End With
Set regex = Nothing
End Function
Public Function reSub(strInput As String, strPattern As String, strRep As
String, Optional icase As Boolean) As Variant
''' replaces all instances of pattern with a new string
'' 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
'' this replaces the legacy function reReplace and is synonymous with
Excel's SUBSTITUTE function with comparable functionality.
If strPattern = "" Or strInput = "" Then
reSub = CVErr(xlErrNA)
Exit Function
End If
Dim regex As RegExp
Set regex = New RegExp
With regex
.Global = True
.MultiLine = True
.IgnoreCase = icase
.Pattern = strPattern
' ' only execute if at least 1 instance...
' If Not .Test(strInput) Then
' ' instance not found
' ' Neither VBscript or Excel SUBSTITUTE evaluates this as an
error.
' ' However, for the sake of consistency with the other UDFs
' ' we could evaluate it to an error in this case.
' reSub = CVErr(xlErrNA)
'
' Else ' good to go
''' code removed...
'' ...to be more consistent with Excel SUBSTITUTE
reSub = .Replace(strInput, strRep)
' End If
End With
Set regex = Nothing
End Function
Public Function reFind(strInput As String, strPattern As String, Optional
icase As Boolean, Optional nth As String = "First") As Variant
''' 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.
'' 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
'' now matches Excel's FIND function.
If strPattern = "" Or strInput = "" Then
reFind = CVErr(xlErrNA)
Exit Function
End If
Dim regex As RegExp
Set regex = New RegExp
With regex
.Global = True
.MultiLine = True
.IgnoreCase = icase
.Pattern = strPattern
' only execute if at least 1 instance...
If Not .Test(strInput) Then
' instance not found
reFind = CVErr(xlErrNA)
Else ' good to go
Dim match, matches As Object
Set matches = .Execute(strInput)
'' process nth argument
Dim nth_case
Select Case True
Case nth = "" ' no argument supplied
nth_case = 1
Case IsNumeric(nth) ' number supplied. It doesn't have to be
integer but is rounded down
nth_case = CInt(nth)
Case UCase(nth) = "FIRST"
nth_case = 1
Case UCase(nth) = "LAST"
nth_case = matches.Count
Case Else
reFind = CVErr(xlErrNA)
Exit Function
End Select
'' nth higher than match index or index less than 1?
If matches.Count < nth_case Or _
nth_case < 1 Then
reFind = CVErr(xlErrNA)
Exit Function
Else ' all good!
reFind = matches(nth_case - 1).FirstIndex + 1 ' plus 1 to
match functionality of FIND
End If
Set matches = Nothing
End If
End With
Set regex = Nothing
End Function