Understanding potentially impactful changes in the Office 2010 object model

Artikkeli
09/08/2011

Brought to you by our compatibility expert Curtis Sawin

One of the challenges of migrating to Office 2010 is assessing the impact of object model changes in Office applications. IT Pros and developers fear that macros built using a previous version of Excel (for example) may not work in Excel 2010 because the object model has changed too much. Broken macros seem almost inevitable when you review the exhaustive list of differences in the object models between the latest version of Office 2010 applications and their Office 2007, 2003, XP, and 2000 counterparts on MSDN (see the links at the end of this article). However, most changes are non-impactful changes and will not have any effect on your macros. What does “most” mean? Below is a summary:

There are over 22,000 items (properties, methods, and constants) in the combined object models of Excel 2010, Word 2010, PowerPoint 2010, Outlook 2010, and Access 2010
There are 2,134 changes in the object models
There are 49 potentially impactful changes

49 potentially impactful changes. Across 5 applications. Spanning 4 previous versions. That’s it. Here’s a chart that sums up our findings:

This article describes what we mean by non-impactful changes and potentially impactful changes. BONUS: For your future reference, we have a table that lists all potentially impactful changes. The table is also attached as a .pdf file at the end of this post.

Non-impactful and potentially impactful changes

The MSDN documentation provides all changes in the object model for each Office 2010 application. The changes are organized so that each previous Office version lists all methods or properties that have either changed, been removed, or have been deprecated.

For example, for Excel 2010, there are nearly 900 items that in the object model that are different from previous versions. At first glance, this seems like a large, scary number. The MSDN article Excel 2010 Object Model Changes Since Earlier Versions lists every one of those items that have been changed. When printed, the article is about 90 pages long.

Deprecated Items– non-impactful

The majority of these changes are deprecated (also listed as hidden) items. So what is a “deprecated item” and how will it affect your macros? If an item is listed as deprecated, that means you should not count on it being available in future versions of Office and that you should plan to update the code in the future. However, a deprecated item will still work! Thus, if you have macro code that was built for a previous version of Office and it contains deprecated methods, properties, or constants, there is no action necessary to make those items work in Office 2010. None. For the nearly 900 changed items (882 to be exact), 794 are deprecated items. This means nearly 800 of the 900 changes in the Excel object model will not cause any impact if you have those items in your macros.

So you can postpone addressing deprecated items, but you shouldn’t ignore them entirely. Code containing such items should be reviewed and updated to make sure that today’s deprecated code doesn’t become tomorrow’s broken code.

Removed items – impactful

“Removed” items are the big ones. An item that is listed as removed is no longer in the Office 2010 object model. If you have an existing macro that contains items that have been removed in Office 2010, that macro won’t work. For Excel 2010, there are 9 object model items that have been removed since previous versions. These are the items that need your focus. Replace ‘em.

Changed items – mostly non-impactful

“Changed” items are methods and properties that are…well…changed since an earlier version. For instance, a changed method might return a different data type, or a property may have changed from “read only” to “read/write.” Doing a little analysis, we found that most changed items involve a method or property accepting additional, optional parameters. For example, in Excel 2003, the ListRows.Add method provides one optional parameter. In Excel 2010, it provides two optional parameters. The addition of the optional parameter doesn’t have any impact on its use. If you used this method in Excel 2003 and only provided one parameter, the same code will work in Excel 2010 without modification.

In Excel 2010, there are 97 changed items. None of these items is potentially impactful. All of the changes in the items will not affect existing code that contains the “changed” items.

So, out of 882 methods and properties that are different from 4 previous versions of Excel, only 9 of these will be impactful.

Not all changed items are non-impactful. For example, in PowerPoint, the Presentation.SaveAs method uses a different default value for the optional parameter FileFormat. The default value changed from “1” (ppSaveAsPresentation) in PowerPoint 2003 to “11” (ppSaveAsDefault) in PowerPoint 2010. Thus, if you’re using this method and are not explicitly providing a value for the FileFormat parameter, you may experience different results running a macro built in PowerPoint 2003 than in PowerPoint 2010. Thus, depending on how you’re using this method, the change is potentially impactful.

Won’t OCCI detect object model changes for me?

Tools like the Office Code Compatibility Inspector (OCCI) can be used to scan your macros to see if they contain code that has been changed from previous versions. OCCI will provide you with a report that lists the number of items that have been changed, deprecated, or removed. It’ll also tell you if you’re using any external references (especially important if you’re migrating Office 2010 as part of an operating system upgrade), and if you are making any declarations that are not explicitly marked to work in a 64-bit Office environment. Below is a screen shot of a summary of OCCI findings on an Excel workbook with a macro.

Note that OCCI uses the term “redesigned” which corresponds to “removed.” (No, I don’t know why this is different).

Since OCCI picks up all changes, you must sort through the output to figure out what information is “actionable.” Meaning, OCCI will tell you “things are different.” But what things need to be fixed immediately? What things need to be fixed eventually? OCCI doesn’t provide this deep level of information. The table below aims to address that gap. It lists all potentially impactful changes; things that should be evaluated immediately. Depending on how they are used, code that contains these items may not need any modification. So treat this as a ‘quick start’ guide to addressing any macros. But don’t over rely on any tool or even the below table. At the risk of sounding redundant, executing the macros to determine if they work is the only sure way to determine if they work.

Summary

The table below lists every such potentially impactful change, which includes all removed items, and all potentially impactful changed items. Use this table to quickly identify what code in this table you may be using. While you can use tools such as OCCI to detect all changes, this table provides more actionable (and consumable) data by eliminating object model changes that won’t have any impact when migrating your macros to Office 2010.

One of the challenges of relying on tools is that they typically provide data that helps answer technical questions (e.g., “what has changed?”) not business questions (e.g., “How will this impact my solution?”). Tools certainly have their place to help automate and expedite troubleshooting efforts, but over-reliance on tools can lead to leveraging the data that tools provide to answer the wrong questions. As always, the best method to ensure if your solution works when upgrading to Office 2010 is to perform end-user testing that involves actually using the solution (in a test environment, if possible).

(You can download the above table in a .pdf file by clicking the link for the .pdf at the end of this post.)

For more information

The below links provide the differences in the various object models.

Potentially Impactful Changes in the Office 2010 Object Model Reference.pdf

Comments

Anonymous
January 01, 2003
I also note that the deprecation of SmartTags and XML Nodes isn't mentioned - that has had a tremendous impact.
Anonymous
January 01, 2003
This is an interesting article, but not as thorough as it could be. The C# developer could very well be impacted by additional, optional parameters. It would also be nice if the entire table were visible. One thing I'd like to see addressed is alerts and documentation about how changes in the file formats will affect results. What immediately comes to mind is saving as HTML in Excel (2007) that's broken any number of solutions. And the changes going on in the background of the Word application, which is steadily breaking things - even though it appears the object model hasn't been changed. These kinds of things have a much greater impact AND frustration-level because they're not documented anywhere. There's no place to report them and the effort required to track them down and bring them to the attention of the Office team is tremendous. I'd also like to point out that "impactful" is not really a word, which distracted me the entire time I was reading...
Anonymous
January 01, 2003
A really useful piece of work! The list of changes is impressive. > Tools like the Office Code Compatibility Inspector (OCCI) can be used to scan your macros to see if they contain code that has been changed from previous versions. I'd suggest mentioning VSTO in that sentence. Thank you!
Anonymous
July 09, 2013
The comment has been removed

Jaa