[Bldg-sim] anybody for regular expressions in Excel?

[chris.malcolm.yates at gmail.com]

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

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.onebuilding.org/pipermail/bldg-sim-onebuilding.org/attachments/20230719/936a5bda/attachment.htm>