New to the Excel 2007 object model is the Comments property of the Name object.

The Name object itself has been around since Excel 2000 and possibly before, but the Comment property is a handy new development.

I like Comment properties personally. Any object model representing items such as this would benefit from a property where you can stash whatever info is pertinent. Take, for instance, the venerable tag property of VB controls. Of course, these things can be misused, but then, is there anything that can’t be misused in the wrong hands?

Anyway, comments like this are great, but they also represent potentially sensitive information that you might not want to reveal to third parties, especially in this case, because the comments themselves are persisted to the workbook. Excel 2007 has a nifty Document Inspector you can use to remove these comments, which is nice. However, when you run the inspector, it simply says that there were comments detected, would you like to delete them? Nice, but not terribly informative.

Retrieving the comments on a Name is not too hard.

cmt = ExcelApp.ActiveWorkbook.Names(1).Comment                                                   

But, when you go to clear the comment:

ExcelApp.ActiveWorkbook.Names(1).Comment = ""

Nothing happens…

Excel, and Word, and PowerPoint for that matter, all seem to be afflicted by this particular “a blank string doesn’t really count” malady in one way or another. Setting the Creation Date, for example, or the Username or UserInitials all seem to have similar problems with being set to a null string.

The solution, though not ideal, isn’t too terribly bad either, I suppose.

ExcelApp.ActiveWorkbook.Names(1).Comment = " "

Yes, that’s right, just set it to a space, instead of a null string.

It’d be interesting to see the code responsible for implementing these properties, just to see how it could be coded such that a null string is ignored like this. It seems like it just couldn’t have been intentional.

And just for the record, passing an actual null char doesn’t work either, so it doesn’t appear to be a “C string/BSTR related” issue.