VBA Characters Method Bugs: Comments vs TextBoxes

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

Excel Comments Moved Resized Bug

Has Excel ever moved or resized comments for you? Would you like to be able to reset to default position, and resize all existing comment boxes to fit the size of your comments?

Here is an Excel add In that allows you to select a cell, range of cells, or the used area of the worksheet, and will then tell you the number of comments found, and ask permission to relocate and resize the associated comment boxes.

To relocate the comment’s box to the default location is not technically difficult. Where this add in differs from others, is that it does not use a fudge factor in calculating the size of the box. This add in takes into account all of your comment’s font styles, line returns, special characters, etc., sets a configurable width, and then sizes the comment box just tall enough to display the comment.

Caveats:

  • Tested on excel 2010 on windows 7. Might work with other versions
  • Not tested with images attached to comments
  • Macros must be enabled

Install the add in:

  • Dounload Excel Add In – Fix Comments
  • If you need to find your Excel Add Ins folder location:
    • Open Excel, go to Excel Options, then Add Ins, then Manage -> Go
    • Now in the Add Ins dialogue, click Browse and note your folder location.
  • Place Fix.Comments.xlam in your Excel Add Ins folder.
  • Reopen the Add Ins dialogue, check the Fix.Comments, choose OK to load the add in and close the dialogue
  • Your ribbon should now display the “Fix” tab. Select a cell or cells that have comments, and experiment with the buttons in the Comments group.

Change Comment Box Width:

  • After the add in is installed, open Excel, then open the visual basic editor (VBE)
  • In the VBE project browser, expand “VBAProject (Fix.Comments.xlam),” then “Modules,” then double click the “Comments” module.
  • Now in the code window, find the sub named CommentsResizeReposition, then find the variable declaration “bPreferredWidth = 100”
  • Edit the “100” to the number of points you want this add in to use when resizing your comment boxes.
  • Save your changes (Ctrl + S) to Fix.Comments.xlam.
  • Close the VBE and proceed to use the add in.

Many thanks to my brother for his time taken to suggest improvements to this post.

Customizing Excel Ribbon

Quick Start to adding a ribbon group and buttons, and underlying code to become an excel 2010 add-in.

Create an empty folder. EXCELaddins to hold your working files. Within this folder create 2 subfolders named customUI and _rels.

Open a new blank workbook. Open the Visual Basic Editor (VBE), try Alt + F11. Create your VB subs or functions that will be called by your ribbon buttons. In your working folder, save this workbook as an excel add-in file (.xlam) with a descriptive name that will appear in Excel’s Add Ins dialogue.

Edit 2 files within your .xlam file package. Open your .xlam file with your favorite zip program. May require renaming your .xlam to .zip.

Withing the .xlam archive, extract the _rels\rels file to EXCELaddins\_rels\rels. Open the rels file In a plain text editor, and add the following relationship to the <Relationships> section:

<Relationship Id="myUniqueID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" />

Save the changes to your _rels\rels file.

Using a plain text editor create a \customUI\customUI.xml file that contains ribbon groups and buttons to call the VB code. Use the below examples:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="myuniqueid1" label="My Ribbon Tab Name">
        <group id="myuniqueid2" label="My Tab Group Name" >
          <button id="myuniqueid3" label="My Group Top Button Name" size="normal" onAction="myEventSub1" />
          <button id="myuniqueid4" label="Second Button Name" size="normal" onAction="myFunction2" />
          <button id="myuniqueid5" label="Third Button Name" size="normal" onAction="mySub3" />
        </group >
        <!-- group id="myuniqueid6" label="This Group Will Not Appear in the Ribbon" >
          <button id="myuniqueid7" label="Other Group Top Button Name" size="normal" onAction="MyOtherEventHandlerSub" />
        </group -->
      </tab>
    </tabs>
  </ribbon>
</customUI>

Add your updated rels file and your customUI folder (contains your customUI.xml file) back into your zipped archive (your .xlam file).

Copy your working .xlam to the excel addins folder, default windows location was C:\Users\User\AppData\Roaming\Microsoft\AddIns.

Open Excel, navigate to the “Developer” tab on the ribbon, and open “Add Ins”. Now in Excel’s Add Ins dialogue, check your .xlam file to enable it. Restart Excel to try out your Excel add in.

To download a functional example of the above, see our “Fixing Excel Comments” post.