RegexLookup function for Excel

RegexLookup.vb

' This is supposed to be a VBA (VB for applications) module, but GitHub does not have a syntax-highlighter for .vba files
'
' SPDX-License-Identifier: MPL-2.0
'
' This Source Code Form is subject to the terms of the Mozilla Public
' License, v. 2.0. If a copy of the MPL was not distributed with this
' file, You can obtain one at https://mozilla.org/MPL/2.0/.

' REQUIREMENTS:
' - You MUST add a reference to "Microsoft VBScript Regular Expressions 5.5" (or any compatible versions)
' - You MUST save your Excel file as .xlsm

Function RegexLookup(value As String, regex_array As Range, Optional ByVal on_not_found) As Variant
    Dim regex As New RegExp
    Dim lookup As Variant
    lookup = regex_array
    For r = 1 To UBound(lookup, 1)
        regex.Pattern = lookup(r, 1)
        If regex.Test(value) Then
            RegexLookup = lookup(r, 2)
            Exit Function
        End If
    Next r
    ' If we fall through to this point, then no regex test was successful
    If IsMissing(on_not_found) Then
        RegexLookup = CVErr(xlErrNull)
    Else
        RegexLookup = on_not_found
    End If
End Function

' ----- The Sub's below need to be run manually using the "Play Button"

' This one used to add the function to the "Lookup" category of formulas
Sub RegisterUDF()
    Dim s As String
    s = "Performs regex tests against value, and return the second column from regex_array if the first column matches. " & _
    "If on_not_found is not set, then returns a #NULL"
    Application.MacroOptions Macro:="RegexLookup", Description:=s, Category:=5
End Sub

' This one used to remove the function from any category
Sub UnregisterUDF()
    Application.MacroOptions Macro:="RegexLookup", Description:=Empty, Category:=Empty
End Sub