Re: Parsing Characters - Bug ???

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Jim,

This has to do with an analyzer that colour codes individual characters
within a single text string. It is quite complex. The wb has been rebuilt
once to no effect. I need to be able to parse through the text and capture
the font colour of individual characters. A text string won't work by current
design.

(xl2000 SP-3 Windows 2000 Professional)

I reliably get the phenomenon described assuming I close the wb and after
reopening make no manual cell entries before running the macro. Then, if I
enter any text into any cell in the wb and rerun it I get a much faster
result. Are you saying you don't replicate this? Following is a formula of
Tom's (no relevance) that I copied from one of his posts. Try it on it.

'=MID(A2,SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1),FIND(".",A2)-SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1))

Thanks,
Greg


"Jim Cone" wrote:

Greg,

I couldn't get your code to demo the delay until I had tested it
several times. Then it started delaying every time.

Modifying the code to use a String instead of a Character object,
only took about 1/3 of the best time of the original code.
Also, there was no unexplained delay...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub Testxyz2()
Dim char As String
Dim c As Range
Dim i As Integer
Dim ii As Long
Dim tmr As Long

Application.ScreenUpdating = False
Set c = ActiveCell
'Application.SendKeys "+{TAB}x{TAB}"
'DoEvents
tmr = Timer
For i = 1 To Len(c.Value) - 1
char = Mid$(c.Value, i, 1)
For ii = i + 1 To Len(c.Value)
If Mid$(c.Value, ii, 1) = char Then
c(2).Value = c(2).Value & char
End If
Next ii
Next i
Application.ScreenUpdating = True
MsgBox (Timer - tmr)
End Sub
'------------


"Greg Wilson"
<GregWilson@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message
I have a complex macro that parses characters in the active cell and makes
changes to the work***. However, if the macro is run after opening the wb
before any manual cell entry is made (as opposed to clearing contents or
pasting etc.) then the macro runs very slowly. This is obvious if there is a
lot of text in the cell being parsed. However, as soon as a manual cell entry
is made to any cell in the wb the same macro takes roughly 15% as much time.
Programmatic cell entry doesn't work unless using Sendkeys.

Below is a macro that doesn't do anything meaningful except demo the
problem. Note the commented text which is a kludge that works but, suffice to
say, I would like to avoid in the real macro. It should be run with the
active cell containing some miscellaneous text of about 200 characters.
Expected run time ~12 sec. or ~2.5 sec. depending on if cell entry made. The
active cell should not be in the first column if you run the Sendkeys kludge.

Sub Testxyz()
Dim char As Characters
Dim c As Range
Dim i As Integer, ii As Long
Dim tmr As Long

Application.ScreenUpdating = False
Set c = ActiveCell
'Application.SendKeys "+{TAB}x{TAB}"
'DoEvents
tmr = Timer
For i = 1 To Len(c) - 1
Set char = c.Characters(i, 1)
For ii = i + 1 To Len(c)
If Mid(c, ii, 1) = char.Text Then
c(2) = c(2) & char.Text
End If
Next ii
Next i
Application.ScreenUpdating = True
MsgBox (Timer - tmr)
End Sub

Very appreciative of your thoughts.

Greg


.

Quantcast