mvidas
08-26-2004, 09:01 AM
Hi everyone,
Two questions. The second one may not need to be answered, depending on the answer to the first. My situation is that I made a subroutine to parse a string based on the pattern, and it separates the text around the regexp matches into each cell in a given row (it's to parse a string based on delimiters chosen at runtime). Here is the code:
Sub ProcessRow(SH As Worksheet, RW As Long, PAT As String, STG As String)
Dim RGX, MAT, MATS, II As Long, JJ As Long, BOOL As Boolean
Set RGX = New RegExp
On Error Resume Next
II = 1
JJ = 1
RGX.Pattern = PAT
RGX.MultiLine = True
RGX.IgnoreCase = True
RGX.Global = True
BOOL = RGX.Test(STG)
If Err.Number = 0 Then
Set MATS = RGX.Execute(STG)
For Each MAT In MATS
SH.Cells(RW, II) = Mid(STG, JJ, MAT.FirstIndex - JJ + 1)
JJ = MAT.FirstIndex + MAT.Length + 1
II = II + 1
Next
End If
On Error GoTo 0
SH.Cells(RW, II) = Mid(STG, JJ)
End Sub
My first question is, the above uses early binding (I think), which requires a reference to regexp. I can incorporate the reference setting into the code, but if the user has office xp it will cause that error to the user. How can I accomplish the same thing using late binding?
My second question is, if I need to use early binding, how can I speed up the above sub? I'm testing it by importing text from a text file, and parsing it as the line is input. If I just import the text right into column A without parsing, it takes only 3 seconds. But when I import it then process it using the above, it takes about 2:10. I know it's not going to be close to 3 seconds by parsing it (as it uses a for loop, and my test workbook is parsed into 11 columns), but I'm hoping there is something I can do to speed it up.
By the way--thanks Dave/brettdj for the regexp file in the KB! Helped a lot!
Thanks
Matt
Two questions. The second one may not need to be answered, depending on the answer to the first. My situation is that I made a subroutine to parse a string based on the pattern, and it separates the text around the regexp matches into each cell in a given row (it's to parse a string based on delimiters chosen at runtime). Here is the code:
Sub ProcessRow(SH As Worksheet, RW As Long, PAT As String, STG As String)
Dim RGX, MAT, MATS, II As Long, JJ As Long, BOOL As Boolean
Set RGX = New RegExp
On Error Resume Next
II = 1
JJ = 1
RGX.Pattern = PAT
RGX.MultiLine = True
RGX.IgnoreCase = True
RGX.Global = True
BOOL = RGX.Test(STG)
If Err.Number = 0 Then
Set MATS = RGX.Execute(STG)
For Each MAT In MATS
SH.Cells(RW, II) = Mid(STG, JJ, MAT.FirstIndex - JJ + 1)
JJ = MAT.FirstIndex + MAT.Length + 1
II = II + 1
Next
End If
On Error GoTo 0
SH.Cells(RW, II) = Mid(STG, JJ)
End Sub
My first question is, the above uses early binding (I think), which requires a reference to regexp. I can incorporate the reference setting into the code, but if the user has office xp it will cause that error to the user. How can I accomplish the same thing using late binding?
My second question is, if I need to use early binding, how can I speed up the above sub? I'm testing it by importing text from a text file, and parsing it as the line is input. If I just import the text right into column A without parsing, it takes only 3 seconds. But when I import it then process it using the above, it takes about 2:10. I know it's not going to be close to 3 seconds by parsing it (as it uses a for loop, and my test workbook is parsed into 11 columns), but I'm hoping there is something I can do to speed it up.
By the way--thanks Dave/brettdj for the regexp file in the KB! Helped a lot!
Thanks
Matt