Hi Peter,
The SendKeys and DoEvents was an admitted kludge that achieved the requisite
cell entry that enabled faster performance. For some weird reason the macro
performs much faster after a manual (or SendKeys) cell entry has been made,
at least on my system. If you close the wb and reopen and then immediately
run the macro it will perform much slower than after making a simple entry to
any cell in the wb. Pasting and deleting cell contents, nor programmatic cell
change (except SendKeys) doesn't work.
The demo macro only produced the above effect. My actual macro needs to
parse the characters checking for individual character font colour because
of colour coding. Hence passing to a string won't work. See my last post to
Jim.
Regards,
Greg
"Peter T" wrote:
Hi Greg,
I can't put together what you describe below with the demo in your OP (what
was the doevents & sendkeys for - would slow to a crawl). In that you appear
to be parsing characters directly in the characters object, one by one,
which would be very slow. Why not read the string to a variable, do whatever
parsing and return to the cell.value. While doing that keep a record of
sections you want to format (start's & length's) then apply to
character.font (adjacent characters in one go), color, bold etc. Perhaps
first reset whatever overall default formats to cell.font.
Regards,
Peter T
at the same time keep a record
"Greg Wilson" wrote in message
...
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(INDI
RECT("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"
wrote in message
I have a complex macro that parses characters in the active cell and
makes
changes to the worksheet. 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