pepoluan · September 13, 2024 08:11
diff --git a/RegexLookup.vb b/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
	' 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