Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming,microsoft.public.word.vba.beginners,microsoft.public.word.vba.general
|
|||
|
|||
![]()
Well, below is the current version of the code, but unless someone is
willing to help and can clarify how to make the MultiLine thing work, I'm probably going to let it stand. This is about 5 times faster than the original version, but basically it's just inlining the subroutine call and reducing the overhead for object creation. This will process the 2000 lines in around 4 seconds. By using a "Dim theText() as String" statement the system seemed to accept the MultiLine assignment, but in reality only the first string from Cells was copied into my array, and all of my experiments failed. I wasn't able to find any directly applicable source code examples, and I couldn't twist any of the examples I did find into working as expected... My current conclusion is that there are very few people using the MultiLine capability, and none of them are monitoring any of these newsgroups. The other approach of using large strings seems implausible since there's a 32 KB size limit on the strings, and the total file is something over 200 KB. However, I may still be able to pursue that approach if I can devise some criteria for lumping things into reasonably small packages... Basically you can take the following as an example of how to use true regular expressions within Excel via a VB macro: Sub FastClean() Dim startTime startTime = Now Dim I As Long Dim lastI As Long Dim S As String Set aRegExp = CreateObject("vbscript.regexp") With aRegExp .Global = True .IgnoreCase = True ' .MultiLine = True End With ActiveCell.SpecialCells(xlLastCell).Select lastI = ActiveCell.Row For I = 1 To lastI S = Cells(I, 1) 'kill first three parameters aRegExp.Pattern = "(height|width|border)=""?\d+%?""? ?" S = aRegExp.Replace(S, "") ' Eight more patterns and replacements Cells(I, 1) = S Next MsgBox "Finished in " + Format(Now - startTime, "ss.s") + " seconds." End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Get rid of with regular expressions | Excel Discussion (Misc queries) | |||
Regular expressions in VB | Excel Discussion (Misc queries) | |||
Substring in excel? How about regular expressions? | Excel Discussion (Misc queries) | |||
Regular expressions in Excel | Excel Discussion (Misc queries) | |||
Regular Expressions in VBA & Excel including an interactive tool | Excel Programming |