Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

If your application must work with both Excel 2007 and earlier versions, you need to avoid any features that were added after the earliest Excel version that you will support. Another alternative is to incorporate the new features selectively. In other words, your code can determine which version of Excel is being used and then take advantage of the new features or not.

VBA programmers must be careful not to use any objects, properties, or methods that aren't available in earlier versions. In general, the safest approach is to develop your application for the lowest common denominator. For compatibility with Excel 2000 and later, you should use Excel 2000 for development; then test thoroughly by using the other versions.

The Version property of the Application object returns the version of Excel. The returned value is a string, so you might need to convert it to a value. VBA's Val function is perfect for this. The following function, for example, returns True if the user is running Excel 2007 or later. ( Note: Excel 2007 is version 12.)

Function XL12OrLater() XL12OrLater = Val(Application.Version) >= 12 End Function

A very useful feature in Excel 2007 is the Compatibility Checker, shown in Figure 26-1. Display this dialog box by choosing Office Prepare Run Compatibility Checker. The Compatibility Checker identifies any compatibility issues that might cause a problem if the file is opened using an earlier version of Excel.

Figure 26-1: The Compatibility Checker.

Unfortunately, the Compatibility Checker doesn't even look at the VBA code - which is a prime candidate for compatibility problems.