title: Pass of `ByVal` argument to Regex function leads to very slow code , although using Array
body: <p>I am using this <code>Regex function</code> to (Remove numeric characters from end of string if count of numbers &gt;= 9), <br></p>
  <pre><code>Function Remove_Number_Regex(Text As String) As String
  'Remove numbers from end of string if count of numbers(characters) &gt;= 9
      With CreateObject(&quot;VBScript.RegExp&quot;)
        .Global = True
        .Pattern = &quot;\d{9,}(?=\.\w+$)&quot;
        Remove_Number_Regex = .Replace(Text, &quot;&quot;)
      End With
  End Function
  </code></pre>
  <p>I tried on excel (as formula) and it works as it should without any error. <br>
  Then I used it inside vba using Array , but I got this error<br></p>
  <blockquote>
  <p>Compile error: ByRef argument type mismatch</p>
  </blockquote>
  <p>I fixed that error by passing <strong>ByVal argument</strong> to the declaration of Regex function<br></p>
  <pre><code>Function Remove_Number_Regex(ByVal Text As String) As String
  </code></pre>
  <p>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. <br>
  In advance, grateful for any helpful comments and answers. <br></p>
  <pre><code>Sub Use_Function_Remove_Number_Regex()
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
   
      Dim arg As Range, arr
   
      With ActiveSheet
          Set arg = .Range(&quot;O1&quot;, .Cells(.Rows.Count, &quot;R&quot;).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
  </code></pre>