See code below. This code worked on many previous versions - suddenly has started failing on the following line

"s_data = regex.Replace(s_data, "$1" & vbCrLf".

Works for 7 iterations and then fails on 8th. However, when 8th iteration is run by itself it works.


Size or memeory problem? Is there a way to set cachesize. I've seen some references to the property, but received error when I tried to set it.


Public Sub TPTToCell(destination As Range, TPTResponse As String)
  'Take TPT format a pastes it into a sheet at "destination"

  Dim regex As Object
  Dim s As String
  Dim s_header As String
  Dim s_data As String
  Dim s_value As String
  Dim NumColumns As String
  Dim a_header() As String
  Dim m As Variant
  Dim matches As Variant
  Dim tests As Variant

  Set regex = CreateObject("vbscript.regexp")

  'strip out bad characters that will cause regex to crash or go into infinite loops
  'some of the output had characters in it that I could not determine. So this pattern
  ' is a pass-thru filter instead of trying to figure out which characters were causing trouble
  ' I only pass those that do not cause trouble.
  regex.Pattern = "[^A-Za-z0-9_ |()./\\\-%]"
  regex.Global = True


  TPTResponse = regex.Replace(TPTResponse, "")

  regex.Pattern = "\|"

  TPTResponse = regex.Replace(TPTResponse, vbTab)

  regex.Global = False
  regex.IgnoreCase = True

  ' get just the header
  regex.Pattern = "^((.*?\t){5})(.*)"
  regex.MultiLine = False

  Set matches = regex.Execute(TPTResponse)
  For Each m In matches
    tests = m.Value
    s_header = m.SubMatches(0)
    s_data = m.SubMatches(2)
  Next

  FillRangeFromCSV destination, s_header

  NumColumns = destination.Offset(, 3).Value
  regex.Pattern = "((.*?\t){" & NumColumns & "})"
  regex.Global = True

  s_data = regex.Replace(s_data, "$1" & vbCrLf
  FillRangeFromCSV destination.Offset(2), s_data
  Set regex = Nothing

End Sub


Steve