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