(C) 2009 - 2014 by Mourad Louha · All rights reserved

The most important changes to VBA in Excel 2010

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:

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.

Excel 2010 VBA

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. Update: this object represents a value that has been changed in a PivotTable report that is based on an OLAP data source.

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.

Excel 2010 VBA

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:

 

 
Comments

No comments yet.

Leave a Reply