Gaps in the Excel object model
If you use VBA long enough, you'll eventually understand the concept of an "object model," and gain an appreciation of how VBA manipulates Excel objects. But you'll also discover that the object model is not perfect. In some cases, it's as if the designers took the afternoon off and never resumed where they left off.
A few examples:
- If you want to write VBA code to insert a page header in the center position, you'll discover that the CenterHeader property (as well as the other "header" and "footer" properties) leaves a bit to be desired. These should be objects, which contain a Font object. Rather, you're forced to create a text string that uses obscure codes. The statement below, for example, inserts "XYZ Corp." as a center header, using Times New Roman font, 11 points, bold, and italic:
ActiveSheet.PageSetup.CenterHeader = _
&""Times New Roman,Bold Italic""&11XYZ Corp."
- Using VBA, it is not possible to determine the source range for data used in an Excel chart. The Values property, for example, always returns a variant array (not a Range object). The only solution is to parse the SERIES formula and extract the range address. I've encapsulated this tedious task into a class module, available here.
- Have you ever wanted to write code to determine if a chart's data labels contain formula links? If so, you've either given up, or discovered that the only way to do this is to select the data label and then use an obscure XLM macro:
ExecuteExcel4Macro("GET.FORMULA(SELECTION())")
- And, what if you are one of the 5-6 people in the world who needs to determine if a secondary chart axis contains a linked formula? You're out of luck. XLM won't even come to the rescue. Because of this flaw, it's impossible to write code that can identify all links in a workbook. A linked formula in a secondary axes can not be identified.
- Excel 2002 introduced several enhancements to worksheet protection. including the ability to prevent selection of locked and/or unlocked cells. Unfortunately, the new Protection object does not include these properties! Apparently, Microsoft assumed that developers would be satisfied with the old EnableSelection property. But the problem is, this property is not saved with the workbook.