Update from 02/18/2010: I have recently published some new articles about this subject. You can reach these posts by clicking on one of the following links:
- Excel VBA Type Library Scanner
- VBA Objects in Excel 2010, Excel 2007 and Excel 2003
- VBA Objects in Word 2010, Word 2007 and Word 2003<
- VBA Objects in PowerPoint 2010, 2007 and 2003
- VBA Objects in Access 2010, Access 2007 and Access 2003
- VBA Objects found in the MSO.DLL for 2010, 2007 and 2003
Today, we will talk about the improvements and changes which have been made to the VBA Object Model. First, these extension are not negligible, we have discovered 25 new objects and more than 400 new or changed properties or functions. The new constants are not included. How did we retrieve this information? Well, if you do it manually, then you will surely need some time. It’s much easier to develop a tool, which uses a Windows DLL for retrieving and comparing the objects between the different Excel versions. Later, we will publish this tool as freeware. At the end of this article, you can download the result sheet produced by the tool and compare the VBA Versions.
Let’s have a look to the VBA Editor in Excel 2010. We did not find any changes, all features remained the same as in previous versions of Excel. Following picture shows the Object Catalogue with a new Object selected. You can click on the image to load a larger view.
As you can see, the new Object Slicer contains 19 properties and one method. As usual in VBA, many of the properties are self explaining by their name.
In further articles, we will test the new objects and implement some sample applications. At this point, we will first list the new objects. Please note, that we are using a Technical Preview, so this list is preliminary. And the TP contains no documentation ob the objects, so some of our descriptions are sometimes assumptions.
| Object | Description |
| AddIns2 | Contains the same properties as the AddIn object, we don’t know the reason this object has been added. |
| DataBarBorder | Should be related to the Databars object. |
| DisplayFormat | Contains some properties related to the display format of cells. |
| NegativeBarFormat | Seems to be related to negative bars only. |
| PivotTableChangeList | A list of ValueChange objects. |
| ProtectedViewWindow | Provides properties for the height, width or visibility of documents ProtectedView mode. |
| ProtectedViewWindows | A list of ProtectedViewWindows objects. |
| Slicer | You’ll find the properties and functions for slicers. |
| SlicerCache | Contains references to Slicers, SlicerCacheLevels and PivotTables, I did not really understand it yet what this object is for. |
| SlicerCacheLevel | As the SlicerCache object, something I must try out. |
| SlicerCacheLevels | A list of ASlicerCacheLevel objects. |
| SlicerCaches | A list of SlicerCache objects. |
| SlicerItem | Contains properties and methods related to slicer items. |
| SlicerItems | A list of SlicerItem objects. |
| SlicerPivotTables | Seems to be designed for using slicers in Pivot tables, contains the fucntions AddPivotTable() and RemovePivotTable(). |
| Slicers | A list of Slicer objects. |
| SparkAxes | Contains properties related to the vertical and hotizontal axes of Sparklines. |
| SparkColor | Related to the colors of Sparklines. |
| SparkHorizontalAxis | Contains properties related to the horizontal axis of a Sparkline. |
| Sparkline | Methods and properties for a Sparkline. |
| SparklineGroup | Methods and properties for a Sparkline group. |
| SparklineGroups | A list of SparklineGroup objects. |
| SparkPoints | Contains properties for Sparkline points, like the highest and lowest point. |
| SparkVerticalAxis | Contains properties related to the vertical axis of a Sparkline. |
| ValueChange | I don’t really know how to use this object. |
In addition to the newly added objects, there were also some changes made to existings objects. Here a list of functions and/or properties which seems from special interest:
| AppEvents | |
| ProtectedViewWindowActivate ProtectedViewWindowBeforeEdit ProtectedViewWindowDeactivate ProtectedViewWindowOpen ProtectedViewWindowResize |
New events related to ProtectedView Windows. |
| Application | |
| ActiveProtectedViewWindow | Like ActiveSheet, retrieves the active protected view window. |
| ClusterConnector | A String, I don’t really know what it is for. |
| IsSandboxed | May be True, if the Workbook is in Sandbox mode. |
| SaveISO8601Dates | Related to the new option in the Excel Options. |
| AxisTitle | |
| Width | Width of an axis title, a Double. |
| CalculatedMember | |
| DisplayFolder Dynamic FlattenHierarchies |
Some new properties for the CalculatedMember object. |
| ChartTitle | |
| Formula | Function in a chart title. |
| DisplayUnitLabel | |
| Formula | Function in an unit label. |
| PivotTable | |
| AllocateChanges Allocation AllocationMethod AllocationValue Slicers Summary |
Some new methods related to Pivot tables. |
| Range | |
| ClearHyperlinks | Clear the Hyperlinks in a range. |
| DisplayFormat | Reference to isplayFormat object. |
| SparklineGroups | Reference to Sparkline group in a range. |
| Shapes | |
| AddSmartArt | Add a SmartArt, similar to the Method AddTextbox(). |
| SpellingOptions | |
| ArabicStrictAlefHamza | Related to the new option in the Excel Options. |
| WorksheetFunction | |
| Beta_Dist Beta_Inv Confidence_Norm |
Samples for the new worksheets function in Excel 2010. |
As you can see, VBA is still extended. Finally a screenshot from the result sheet of my tool.
You can download this file here: VBA in Excel 2010, 2007 and 2003. Of course, we don’t assume any liability for the accuracy or correctness of the information in the file.
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German
Related posts: