cell.Characters and Fonts

[CathyWeyant]

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.

 

[Peter T]

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]

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.