Pass of `ByVal` argument to Regex function leads to very slow code , although using Array-CodePudding

I am using this Regex function to (Remove numeric characters from end of string if count of numbers >= 9),

Function Remove_Number_Regex(Text As String) As String
'Remove numbers from end of string if count of numbers(characters) >= 9
    With CreateObject("VBScript.RegExp")
      .Global = True
      .Pattern = "\d{9,}(?=\.\w $)"
      Remove_Number_Regex = .Replace(Text, "")
    End With
End Function

I tried on excel (as formula) and it works as it should without any error.
Then I used it inside vba using Array , but I got this error

Compile error: ByRef argument type mismatch

I fixed that error by passing ByVal argument to the declaration of Regex function

Function Remove_Number_Regex(ByVal Text As String) As String

And that leads to a very slow code to finish 18 seconds on (10K row) ,although using any other text function inside the same array takes 0.4 seconds to finish.
In advance, grateful for any helpful comments and answers.

Sub Use_Function_Remove_Number_Regex()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    Dim arg As Range, arr
 
    With ActiveSheet
        Set arg = .Range("O1", .Cells(.Rows.Count, "R").End(xlUp)) '10k rows
    End With
 
    arr = arg.value
 
    Dim r As Long, j As Long
 
   For j = 1 To 4
     For r = 1 To UBound(arr)
          arr(r, j) = Remove_Number_Regex(arr(r, j))
      Next r
    Next j
 
    arg.value = arr
 
    Application.Calculation = xlCalculationAutomatic
End Sub

CodePudding user response：

Generally speaking; using regular expressions will slow things down. You are correct that common string-operations are faster. So, why not use them:

Function Remove_Number(Text As String) As String

Dim nr As String: nr = CStr(Val("1" & StrReverse(Split(Text, ".")(0))))

If Len(nr) > 9 Then
    Remove_Number = Replace(Text, StrReverse(Mid(nr, 2)) & ".", ".")
Else
    Remove_Number = Text
End If

End Function

To test this function based on your

The trick used:

Split your input based on the dot, and return the 1st element from the array (zero based);
Reverse the string;
Extract the numeric value when concatenated with a '1' (to prevent trailing zeros to disappear);
Check if length is more than 9 (accounting for the '1') and if so replace the value accordingly.

Note: Depending on your version of Excel, you could just stay away from VBA alltogether. See my answer to your previous question.