cell.Characters and Fonts
 

I am using Excel 2003 and VBA 6.5

Recently I have been automatically converting text in cells that are
formatted with the Symbol Font set to unicode when the subroutine

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

is triggered. I am changing the text and fonts by using the
range.characters. This all works fine. The only problem is that it is slow
when there is a large amount of text pasted a the cell.

But the mystery deepens - if I click on the Dialog "Insert, Symbol" then
repeat the same task - my program is lightening fast. I think somehow the
dialog is cacheing the font set in Excel and this is why it is faster.

Is there a way I can do this programatically without telling the users to
click on Insert, Symbol first before doing tasks that would be slow.

cell.Characters and Fonts
 

If you are changing the font of all text in a cell, or multiple cells, no
need to use "Characters", simply
rng.Font.Name = "Symbol"
(typically where the text is a single character)

The first time the Font object is called in an Excel session it can be a
little slow
(if very slow a suspect culprit might be printer). Normally subsequent
calls should be fine.

Regards,
Peter T

"CathyWeyant" wrote in message
...
I am using Excel 2003 and VBA 6.5

Recently I have been automatically converting text in cells that are
formatted with the Symbol Font set to unicode when the subroutine

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

is triggered. I am changing the text and fonts by using the
range.characters. This all works fine. The only problem is that it is
slow
when there is a large amount of text pasted a the cell.

But the mystery deepens - if I click on the Dialog "Insert, Symbol" then
repeat the same task - my program is lightening fast. I think somehow the
dialog is cacheing the font set in Excel and this is why it is faster.

Is there a way I can do this programatically without telling the users to
click on Insert, Symbol first before doing tasks that would be slow.

cell.Characters and Fonts
 

Part of the problem is the I have characters like, alpha and beta embedded in
the text - which are formatted with the Symbol Font set - which is basically
the letter a and b formatted with a Greek Font set. The cell can contain a
mixed Font set. Plus I wanted to handle the special case of the letter "o"
when it is formatted as a superscript - I need to convert it to the Unicode
character for degree. I need to ensure the text is unicode and the font set
is a unicode font set (Arial). But due a limitation of object Characters - I
can't insert/delete if more than 255 characters. In one test case, I had
1260 characters in one cell.

http://support.microsoft.com/kb/213559

So I figured a work around, where I iterated through the characters and
converted the text to unicode and stored the result in a String. I kept
track of the formatting, I was only concerned with subscript, superscript and
italic. I then replaced the entire text and then formatted it. This is
what took such a long time. But was faster if I clicked on Insert, Symbol
first.

"Peter T" wrote:

If you are changing the font of all text in a cell, or multiple cells, no
need to use "Characters", simply
rng.Font.Name = "Symbol"
(typically where the text is a single character)

The first time the Font object is called in an Excel session it can be a
little slow
(if very slow a suspect culprit might be printer). Normally subsequent
calls should be fine.

Regards,
Peter T

"CathyWeyant" wrote in message
...
I am using Excel 2003 and VBA 6.5

Recently I have been automatically converting text in cells that are
formatted with the Symbol Font set to unicode when the subroutine

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

is triggered. I am changing the text and fonts by using the
range.characters. This all works fine. The only problem is that it is
slow
when there is a large amount of text pasted a the cell.

But the mystery deepens - if I click on the Dialog "Insert, Symbol" then
repeat the same task - my program is lightening fast. I think somehow the
dialog is cacheing the font set in Excel and this is why it is faster.

Is there a way I can do this programatically without telling the users to
click on Insert, Symbol first before doing tasks that would be slow.

cell.Characters and Fonts
 

The approach you describe is never going to be particularly fast. Depending
on your code there might be ways of speeding things up a bit.

Regards,
Peter T

"CathyWeyant" wrote in message
...
Part of the problem is the I have characters like, alpha and beta embedded
in
the text - which are formatted with the Symbol Font set - which is
basically
the letter a and b formatted with a Greek Font set. The cell can contain a
mixed Font set. Plus I wanted to handle the special case of the letter
"o"
when it is formatted as a superscript - I need to convert it to the
Unicode
character for degree. I need to ensure the text is unicode and the font
set
is a unicode font set (Arial). But due a limitation of object Characters -
I
can't insert/delete if more than 255 characters. In one test case, I had
1260 characters in one cell.

http://support.microsoft.com/kb/213559

So I figured a work around, where I iterated through the characters and
converted the text to unicode and stored the result in a String. I kept
track of the formatting, I was only concerned with subscript, superscript
and
italic. I then replaced the entire text and then formatted it. This is
what took such a long time. But was faster if I clicked on Insert, Symbol
first.

"Peter T" wrote:

If you are changing the font of all text in a cell, or multiple cells, no
need to use "Characters", simply
rng.Font.Name = "Symbol"
(typically where the text is a single character)

The first time the Font object is called in an Excel session it can be a
little slow
(if very slow a suspect culprit might be printer). Normally subsequent
calls should be fine.

Regards,
Peter T

"CathyWeyant" wrote in message
...
I am using Excel 2003 and VBA 6.5

Recently I have been automatically converting text in cells that are
formatted with the Symbol Font set to unicode when the subroutine

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

is triggered. I am changing the text and fonts by using the
range.characters. This all works fine. The only problem is that it is
slow
when there is a large amount of text pasted a the cell.

But the mystery deepens - if I click on the Dialog "Insert, Symbol"
then
repeat the same task - my program is lightening fast. I think somehow
the
dialog is cacheing the font set in Excel and this is why it is faster.

Is there a way I can do this programatically without telling the users
to
click on Insert, Symbol first before doing tasks that would be slow.