Sitemap  · Français  · English  · Deutsch

• Home
• Contact
• Imprint

• Blog
  • Recent posts
  • Categories
  • » General
  • » Office 2010
  • » VSTO
  • » Experiments
  • Archive
• Products
• Trainings
• Tutorials
• Customers
• Downloads
• Links

Home » Blog » Article

Article

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.