Vagaries of the Characters() Method
In Excel 2010, version 14, the Characters method of Shapes did not, and in newer versions may still not, build and return a character object containing expected property values. The good news: No deficiencies were identified when working with cell contents.
With Comments
Two issues were observed when argument parameters (params) were passed to the Textframe.Characters() method to read font properties. Without params, Characters() correctly returned font property values. See microsoft Excel’s Textframe.Characters article for more details. Herein describes deficiencies when Characters(start) or Characters(start, length) are used.
Comment.Shape.Textframe.Characters(start)
The length param is documented as optional. If omitted, expected behavior is to return the remaining characters after the start param entered. The method does in fact return an object that refers to the remaining characters, BUT half of the Font property values are reported as “Error 2015.” To see this behavior with your data, create a cell comment, update the following snippet with your cell address, and paste into the immediate window:
Firstly, without start param; confirm code and data is valid:
set font = Range("a2").Comment.Shape.TextFrame.Characters().Font
? font.Size
Secondly, add a start param to the above line, as in Characters(1), and try it again. Try it with other properties like Bold, Name, etc. A null value only means that more than one property value exists in the characters you requested. For example some chars are bold and others are regular.
Comment.Shape.Textframe.Characters(start, length)
This issue appeared to selectively affect the Underline property of Font.
Request a subset of all possible characters by passing both start and length params, and you will be rewarded by Underline booleanesk property value of False. Expected behavior is to return a numeric value as seen in microsoft’s XlUnderlineStyle constants.
Still have that comment you created above? Edit the comment and underline all the text. Then try these lines in the immediate window.
Firstly, without params; confirm code and data is valid:
set font = Range("a2").Comment.Shape.TextFrame.Characters().Font
? font.Underline
Expect to see valid numeric Underline property values.
Secondly, add start and length params:
set font = Range("a2").Comment.Shape.TextFrame.Characters(1,1).Font
? font.Underline
Characters(x,x) reported incorrectly as below:
‘False’ should be -4142 when not underlined
-4142 should be 2 when single underlined
‘False’ should be -4119 when double underlined
So to wrap up on comment font properties, your code will need to be designed to accomodate the above limitations. With regards to the Characters() length param being optional, it was not. If you need to pass the start param, you must also pass the length param. Regarding the Font.Underline property, it appears to be impossible to use VBA to copy the Underline property for anything less than all the characters in your text. Consider how often we see text that is all underlined.
With Other Shapes
TextBox.Textframe.Characters().Font
In contrast to comment shape.textframe.characters(), the textbox version appeared to return the expected property values, whether or not, start or length params were passed. In other words, all expected behavior, right? Wrong.
Textbox.textframe.characters() method returned the property value, BUT of only the first character of the characters you requested. Comment.shape.textframe.characters() on the other hand would return a null value for character objects that contained font properties with mixed values.
To observe this gotcha, create a textbox shape on a worksheet, noting its name in the address field. Add some text, and change any/all font properties of the first character, then try these snippets in your immediate window …
Return all characters:
set o = activesheet.shapes("<textbox name>").TextFrame.Characters()
? o.font.<size name bold etc>
Return all except the first character:
set o = activesheet.shapes("<textbox name>").TextFrame.Characters(2)
? o.font.<size name bold etc>
In concluding with the shape.textframe.characters method, let me know if this saved you any development time.
Share your experiences in a comment