Calling custom RegEx function in VBA-CodePudding

I wanted to clean up my excel file, which is using a regular expression query like this. Unfortunately the approach mentioned there via data validation and CTRL-F3 "managed names" does conflict with a Worksheet_Change() sub in VBA. Meaning when applying data validation on the cell in question, Worksheet_Change() gets somehow overlooked.

Anyway, not much of a VBA regular by a long shot. I have a modul with the function RegExpMatch as follows (from above website, I hope this function can even be used for this purpose):

Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant
...

When using this as a formula (i.e. =RegExpMatch(Sheet1!A1, "^[A-Z]{3}-\d{3}$")) it works quite well. However due to above mentioned conflict I would like to call it like:

Private Sub Worksheet_Change(...)
....
    If RegExpMatch(<target>, <pattern>) Then
        ...
    End If
End Sub

I had some issues with special characters before and I find this in general quite confusing. Not sure if the pattern is a problem or how I am trying to call RegExpMatch, but I somehow need this to work and its giving me a run-time error 424. I understand, that there is a somewhat build-in RegEx possibility (see Tools->References), but this file needs to be distributed across different machines and therefore I dont want to make it reliant on special or outdated settings.

EDIT: Please see below MWE. The goal is to save the file when the cell value matches a RegEx. The error occurs when calling the RegExpMatch-Function within the If loop in the last code example. I have a module called RegExpMatch as follows:

Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant
  Dim arRes() As Variant 'array to store the results
  Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns

  On Error GoTo ErrHandl

  RegExpMatch = arRes

  Set regex = CreateObject("VBScript.RegExp")
  regex.pattern = pattern
  regex.Global = True
  regex.MultiLine = True
  If True = match_case Then
    regex.ignorecase = False
  Else
    regex.ignorecase = True
  End If

  cntInputRows = input_range.Rows.Count
  cntInputCols = input_range.Columns.Count
  ReDim arRes(1 To cntInputRows, 1 To cntInputCols)

  For iInputCurRow = 1 To cntInputRows
    For iInputCurCol = 1 To cntInputCols
      arRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)
    Next
  Next

  RegExpMatch = arRes
  Exit Function
  ErrHandl:
    RegExpMatch = CVErr(xlErrValue)
End Function

and am trying to utilize that in my worksheet code as follows:

Public Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("J3")) Is Nothing Then
  ' The following shows run-time error 424
    If RegExpMatch(Range("J3"), "^[A-Z]{3}-\d{3}$") Then
      ActiveWorkbook.SaveAs Filename:=Range("J3").value
    End If
  End If
End Sub

CodePudding user response：

Not an answer (in regards of explaining the error), but as @FunThomas pointed out, I am using the built-in VBScript.RegEx anyway. So I wrote a simplier custom function RegExpMatch and it works as needed.

Public Function RegExpMatch(ByVal value as String, ByVal pattern As String)
  Set regex = CreateObject("VBScript.RegExp")
  regex.pattern = pattern
  RegExpMatch = regex.Test(value)
End Function

and called this in my Workbook_Change sub as follows:

...
  Dim value As String
  value = Target.value
  Dim pattern As String
  pattern = "^[A-Z]{3}-\d{3}$"
  If RegExpMatch(value, pattern) Then
    ...
  End If
...

CodePudding user response：

Try removing the row

RegExpMatch = arRes

right after the

On Error Goto ErrHandl

statement. The array still is undefined in this moment and this most likely causes the error.