Maninweb.de - Professional Excel Solutions

The new VBA features in the Workbook object in Excel 2010

18.02.2010

Based on the results of my tools for comparing the VBA functions between Excel 2010, Excel 2007 and Excel 2003, I think the next logical step should be the systematic testing of new methods, events, properties, and constants.

Unfortunately, the documentation for the new VBA features is still incomplete on my installed version. However the purpose of some new features can often be guessed by their name. But I think this will not always be possible.

The last days, I took a look on the objects „Workbooks“, „Workbook“ und „AppEvents“. While I did not find changes for the workbooks collection, the „Workbook“ object includes some new events, methods and properties which seems to be very interesting.

VBA Workbook Object

First, a list of new events which I have tested and could be triggered by code or by using the Excel 2010 interface. For some other new events, it was not possible for me to get them triggered. All these events can be raised in the Microsoft Excel Object "ThisWorkbook", in the same manner like the other events like Workbook_Open().

Event AfterSave(Success As Boolean)
This new event occurs after saving the workbook using code or using the Excel interface. The variable Success indicates if the operation was successful or not.
Event NewChart(Ch As Chart)
This event occurs when inserting a new chart in a worksheet. This event also occurs when inserting a new PivotTable.
Event SheetPivotTableChangeSync(Sh As Object, Target As PivotTable)
This event occured the first time after I inserted a new PivotTable by using the build in wizard in Excel. The event also occurs each time you change for example the fields list or when updating the PivotTable data. Please note that this event occurs before the update event „SheetPivotTableUpdate()“ when refreshing the PivotTable data and after the update event when changing the fields list.

In the following a list of the new properties for the „Workbook“ object:

Property AccuracyVersion As Long
This property is set by default to zero. You can change this value as it is not write protected. However the setted value is not stored with the workbook and on the next time you open the workbook, the value is resetted to zero.
Property ActiveSlicer As Slicer
This property returns an object representing the current selected slicer. So, you can perform some operations per code.
Property DefaultSlicerStyle
This read/write property returns the current default style for slicers; in my case „SclicerStyleLight1“. In case you change this value, you may carefully select your value. If you set an invalid value per code, then the value is resetted to „None“ and you will not be able to create new slicers for the workbook using the Excel interface. The changed value is also stored in the workbook. Unfortunately, I did not found an option (yet) in the Excel for setting up this value manually.
Property SlicerCaches As SlicerCaches
This property returns an slicer object associated with the workbook. Slicers which have been inserted twice to the workbook are counted only once.

Finally, here is a list of the new methods which have been added to the „Workbook“ object:

Sub DisableDraftModeAllCharts()
Disables the draft mode for all charts.
Sub EnableDraftModeAllCharts()
Enables the draft mode for all charts.

I will report about my experiences with other new objects and object elements in my further articles.