Chapter 26: Compatibility Issues

IN THIS CHAPTER

• Increasing the probability that your Excel 2010 applications will also work with previous versions of Excel

• Declaring API functions that work with 32-bit Excel 2010, 64-bit Excel 2010, and earlier versions of Excel

• Being aware of issues if you're developing Excel applications for international use

What Is Compatibility?

Compatibility is an oft-used term among computer people. In general, it refers to how well software performs under various conditions. These conditions might be defined in terms of hardware, software, or a combination of the two. For example, software written for Windows will not run directly on other operating systems, such as Mac OS X or Linux.

In this chapter, I discuss a more specific compatibility issue involving how your Excel 2010 applications will work with earlier versions of Excel for Windows and Excel for Macintosh. The fact that two versions of Excel might use the same file format isn't always enough to ensure complete compatibility between the contents of their files. For example, Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2002 for Macintosh all use the same file format, but compatibility problems are rampant. Just because a particular version of Excel can open a worksheet file or an add-in doesn't guarantee that that version of Excel can carry out the VBA macro instructions contained in it. Another example: Excel 2010 and Excel 2007 both use the same file format. If your application uses features that were introduced in Excel 2010, you can't expect that Excel 2007 users will magically have access to these new features.

Excel is a moving target, and there is really no way that you can guarantee complete compatibility. Unfortunately, cross-version compatibility doesn't happen automatically. In most cases, you need to do quite a bit of additional work to achieve compatibility.

Types of Compatibility Problems

You need to be aware of several categories of potential compatibility problems. These issues are listed here and discussed further in this chapter:

• File format issues: You can save workbooks in several different Excel file formats. Earlier versions of Excel might not be able to open workbooks that were saved in a later version's file format. For more information about sharing Excel 2010 (and Excel 2007) files, see the sidebar, “The Microsoft Office Compatibility Pack.”

• New feature issues: It should be obvious that you can't use a feature introduced in a particular version of Excel in previous versions of Excel.

• Microsoft issues: For whatever reason, Microsoft itself is responsible for some types of compatibility issues. For example, as I note in Chapter 23, index numbers for shortcut menus haven't remained consistent across Excel versions.

• Windows versus Macintosh issues: If your application must work on both platforms, plan to spend lots of time ironing out various compatibility problems. Excel for Macintosh no longer supports VBA, so it's likely that you'll be forced to create a separate version for the Macintosh.

• Bit issues: Excel 2010 is the first version of Excel that's available in both 32-bit and 64-bit editions. If your VBA code uses API functions, you'll need to be aware of some potential problems if the code must run in both 32-bit and 64-bit Excel, as well as other versions of Excel.

• International issues: If your application will be used by those who use a different language version of Excel, you must address a number of additional issues.

After reading this chapter, it should be clear that there is only one way to ensure compatibility: You must test your application on every target platform and with every target version of Excel. Often, this amount of testing is simply not feasible. However, you, as a developer, can take certain measures to help ensure that your application works with different versions of Excel.

If you're reading this chapter in search of a complete list of specific compatibility issues among the various versions of Excel, you will be disappointed. As far as I know, no such list exists, and it would be virtually impossible to compile one. These types of issues are far too numerous and complex.

A good source for information about potential compatibility problems is Microsoft's support site. The URL is

http:// support.microsoft.com

Information at this site can often help you identify bugs that appear in a particular version of Excel.

Avoid Using New Features

If your application must work with both Excel 2010 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 version number. For compatibility with Excel 2000 and later, you should use Excel 2000 for development; then test thoroughly by using the later versions.

A very useful feature that was introduced in Excel 2007 is the Compatibility Checker, shown in Figure 26-1. Display this dialog box by choosing File⇒Info⇒Check For Issues⇒ Check Compatibility. The Compatibility Checker identifies any compatibility issues that might cause a problem if the file is opened using an earlier version of Excel.

Unfortunately, the Compatibility Checker doesn't even look at the VBA code — which is a prime candidate for compatibility problems. However, you can download the Microsoft Office Code Compatibility Inspector (search for it at http://Microsoftcom). This tool installs as an add-in and adds new commands to the Developer tab. It may help you locate potential compatibility problems in your VBA code. As I write this book, the Microsoft Office Code Compatibility Inspector was available as beta software — and it wasn't very helpful. Perhaps the final version will be more useful.

FIGURE 26-1: The Compatibility Checker.

But Will It Work on a Mac?

A common problem that I hear about is Macintosh compatibility. Excel for Macintosh represents a very small proportion of the total Excel market, and many developers choose simply to ignore it. The good news is that the old Excel XLS file format is compatible across both platforms. The bad news is that the features supported aren't identical, and VBA macro compatibility is far from perfect. In fact, the current version of Excel for Macintosh doesn't even support VBA.

The remainder of this section assumes that you're working with an older version of Excel for Macintosh — a version that still supports VBA.

You can write VBA code to determine which platform your application is running. The following function accesses the OperatingSystem property of the Application object and returns True if the operating system is any version of Windows (that is, if the returned string contains the text “Win”):

Function WindowsOS() As Boolean

If Application.OperatingSystem like “*Win*” Then

WindowsOS = True

Else

WindowsOS = False

End If

End Function

Many subtle (and not so subtle) differences exist between the Windows versions and the Mac versions of Excel. Many of those differences are cosmetic (for example, different default fonts), but others are much more serious. For example, Excel for Macintosh doesn't include ActiveX controls. Also, it uses the 1904 date system as the default, so workbooks that use dates could be off by four years. Excel for Windows, by default, uses the 1900 date system. On the Macintosh, a date serial number of 1 refers to January 1, 1904; in Excel for Windows, that same serial number represents January 1, 1900.

Another limitation concerns Windows API functions: They won't work with Excel for Macintosh. If your application depends on such functions, you need to develop a workaround.

If your code deals with paths and filenames, you need to construct your path with the appropriate path separator (a colon for the Macintosh, a backslash for Windows). A better approach is to avoid hard-coding the path separator character and use VBA to determine it. The following statement assigns the path separator character to a variable named PathSep:

PathSep = Application.PathSeparator

After this statement is executed, your code can use the PathSep variable in place of a hard-coded colon or backslash.

Rather than try to make a single file compatible with both platforms, most developers choose to develop on one platform (typically Excel for Windows) and then modify the application so that it works on the Mac platform. In other words, you'll probably need to maintain two separate versions of your application.

There is only one way to make sure that your application is compatible with the Macintosh version of Excel: You must test it thoroughly on a Macintosh — and be prepared to develop some workarounds for procedures that don't work correctly.

Dealing with 64-bit Excel

You can install Excel 2010 as a 32-bit application or as a 64-bit application. The latter works only if you're running a 64-bit version of Windows. The 64-bit version can handle much larger workbooks because it takes advantage of the larger address space in 64-bit Windows.

Most users don't need the 64-bit version of Excel because they don't work with massive amounts of data in a workbook. And remember, the 64-bit version offers no performance boost. Some operations may actually be slower in the 64-bit version.

In general, workbooks created using the 32-bit version will work fine in the 64-bit version. The only potential problem is if the workbook contains VBA code that uses Windows API functions. The 32-bit API function declarations won't compile in the 64-bit version.

For example, the following declaration works with 32-bit Excel versions, but causes a compile error with 64-bit Excel 2010:

Declare Function GetWindowsDirectoryA Lib “kernel32” _

(ByVal lpBuffer As String, ByVal nSize As Long) As Long

The following declaration works with Excel 2010 (both 32-bit and 64-bit), but causes a compile error in previous versions of Excel:

Declare PtrSafe Function GetWindowsDirectoryA Lib “kernel32” _

(ByVal lpBuffer As String, ByVal nSize As Long) As Long

To use this API function in both 32-bit and 64-bit Excel, you must declare two versions of the function by using two conditional compiler directives:

• VBA7 returns True if your code is using Version 7 of VBA (which is included in Office 2010).

• Win64 returns True if the code is running in 64-bit Excel.

Only one version of VBA can be installed on a system. So, if you have older versions of Excel installed and then install Excel 2010, the older versions will all be running VBA 7. Unfortunately, if you activate the VB Editor in one of these older versions and choose Help⇒About Microsoft Visual Basic, the dialog box won't report that it's running VBA 7.

Here's an example of how to use these directives to declare an API function that's compatible with 32-bit and 64-bit Excel:

#If VBA7 And Win64 Then

Declare PtrSafe Function GetWindowsDirectoryA Lib “kernel32” _

(ByVal lpBuffer As String, ByVal nSize As Long) As Long

#Else

Declare Function GetWindowsDirectoryA Lib “kernel32” _

(ByVal lpBuffer As String, ByVal nSize As Long) As Long

#End If

The first Declare statement is used when VBA7 and Wind64 are both True — which is the case only for 16-Bit Excel 2010. In all other versions, the second Declare statement is used.

Creating an International Application

The final compatibility concern deals with language issues and international settings. Excel is available in many different language versions. The following statement displays the country code for the version of Excel:

MsgBox Application.International(xlCountryCode)

The United States/English version of Excel has a country code of 1. Other country codes are listed in Table 26-1.

Table 26-1: Excel Country Codes

Country	Country Code
English	1
Russian	7
Greek	30
Dutch	31
French	33
Spanish	34
Hungarian	36
Italian	39
Czech	42
Danish	45
Swedish	46
Norwegian	47
Polish	48
German	49
Portuguese (Brazil)	55
Thai	66
Japanese	81
Korean	82
Vietnamese	84
Simplified Chinese	86
Turkish	90
Indian	91
Urdu	92
Portuguese	351
Finnish	358
Traditional Chinese	886
Arabic	966
Hebrew	972
Farsi	982

Excel also supports language packs, so a single copy of Excel can actually display any number of different languages. The language comes into play in two areas: the user interface and the execution mode.

You can determine the current language used by the user interface by using a statement such as:

Msgbox Application.LanguageSettings.LanguageID(msoLanguageIDUI)

The language ID for English is 1033.

If your application will be used by those who speak another language, you need to ensure that the proper language is used in your dialog boxes. Also, you need to identify the user's decimal and thousands separator characters. In the United States, these are almost always a period and a comma, respectively. However, users in other countries might have their systems set up to use other characters. Yet another issue is date and time formatting: The United States is one of the few countries that use the (illogical) month/day/year format.

If you're developing an application that will be used only by people within your company, you probably won't need to be concerned with international compatibility. But, if your company has offices throughout the world, or if you plan to distribute your application outside your country, you need to address a number of issues to ensure that your application will work properly. I discuss these issues in the following sections.

Multilanguage applications

An obvious consideration involves the language that is used in your application. For example, if you use one or more dialog boxes, you probably want the text to appear in the language of the user. Fortunately, changing the language isn't too difficult (assuming, of course, that you can translate your text or know someone who can).

The companion CD-ROM contains an example that demonstrates how to allow the user to choose from three languages in a dialog box: English, Spanish, or German. The filename is multilingual wizard.xlsm.

The first step of the multilingual wizard (found on the CD) contains three OptionButtons that enable the user to select a language. The text for the three languages is stored in a worksheet.

The UserForm_Initialize procedure contains code that attempts to guess the user's language by checking the International property:

Select Case Application.International(xlCountryCode)

Case 34 ‘Spanish

UserLanguage = 2

Case 49 ‘German

UserLanguage = 3

Case Else ‘default to English

UserLanguage = 1 ‘default

End Select

Figure 26-2 shows the UserForm displaying text in all three languages.

FIGURE 26-2: The Wizard Demo in English, Spanish, and German.

VBA language considerations

In general, you need not be concerned with the language in which you write your VBA code. Excel uses two object libraries: the Excel object library and the VBA object library. When you install Excel, it registers the English language version of these object libraries as the default libraries. (This is true regardless of the language version of Excel.)

Using local properties

If your code will display worksheet information, such as a formula or a range address, you probably want to use the local language. For example, the following statement displays the formula in cell A1:

MsgBox Range(“A1”).Formula

For international applications, a better approach is to use the FormulaLocal property rather than the Formula property:

MsgBox Range(“A1”).FormulaLocal

Several other properties also have local versions. These are shown in Table 26-2 (refer to the Help system for specific details).

Table 26-2: Properties That Have Local Versions

Property	Local Version	Return Contents
Address	AddressLocal	An address
Category	CategoryLocal	A function category (XLM macros only)
Formula	FormulaLocal	A formula
FormulaR1C1	FormulaR1C1Local	A formula, using R1C1 notation
Name	NameLocal	A name
NumberFormat	NumberFormatLocal	A number format
RefersTo	RefersToLocal	A reference
RefersToR1C1	RefersToR1C1Local	A reference, using R1C1 notation

Identifying system settings

Generally, you can't assume that the end user's system is set up likwe the system on which you develop your application. For international applications, you need to be aware of the following settings:

• Decimal separator: The character used to separate the decimal portion of a value.

• Thousands separator: The character used to delineate every three digits in a value.

• List separator: The character used to separate items in a list.

You can determine the current separator settings by accessing the International property of the Application object. For example, the following statement displays the decimal separator, which won't always be a period:

MsgBox Application.International(xlDecimalSeparator)

The 45 international settings that you can access with the International property are listed in Table 26-3.

Table 26-3: Constants for the International Property

Constant	What It Returns
xlCountryCode	Country version of Microsoft Excel.
xlCountrySetting	Current country setting in the Windows Control Panel.
xlDecimalSeparator	Decimal separator.
xlThousandsSeparator	Thousands separator.
xlListSeparator	List separator.
xlUpperCaseRowLetter	Uppercase row letter (for R1C1-style references).
xlUpperCaseColumnLetter	Uppercase column letter.
xlLowerCaseRowLetter	Lowercase row letter.
xlLowerCaseColumnLetter	Lowercase column letter.
xlLeftBracket	Character used instead of the left bracket ([) in R1C1-style relative references.
xlRightBracket	Character used instead of the right bracket (]) in R1C1-style references.
xlLeftBrace	Character used instead of the left brace ({) in array literals.
xlRightBrace	Character used instead of the right brace (}) in array literals.
xlColumnSeparator	Character used to separate columns in array literals.
xlRowSeparator	Character used to separate rows in array literals.
xlAlternateArraySeparator	Alternate array item separator to be used if the current array separator is the same as the decimal separator.
xlDateSeparator	Date separator (/).
xlTimeSeparator	Time separator (:).
xlYearCode	Year symbol in number formats (y).
xlMonthCode	Month symbol (m).
xlDayCode	Day symbol (d).
xlHourCode	Hour symbol (h).
xlMinuteCode	Minute symbol (m).
xlSecondCode	Second symbol (s).
xlCurrencyCode	Currency symbol.
xlGeneralFormatName	Name of the General number format.
xlCurrencyDigits	Number of decimal digits to be used in currency formats.
xlCurrencyNegative	A value that represents the currency format for negative currency values.
xlNoncurrencyDigits	Number of decimal digits to be used in noncurrency formats.
xlMonthNameChars	Always returns three characters for backward-compatibility; abbreviated month names are read from Microsoft Windows and can be any length.
xlWeekdayNameChars	Always returns three characters for backward-compatibility; abbreviated weekday names are read from Microsoft Windows and can be any length.
xlDateOrder	An integer that represents the order of date elements.
xl24HourClock	True if the system is using 24-hour time; False if the system is using 12-hour time.
xlNonEnglishFunctions	True if the system isn't displaying functions in English.
xlMetric	True if the system is using the metric system; False if the system is using the English measurement system.
xlCurrencySpaceBefore	True if a space is added before the currency symbol.
xlCurrencyBefore	True if the currency symbol precedes the currency values; False if it follows them.
xlCurrencyMinusSign	True if the system is using a minus sign for negative numbers; False if the system is using parentheses.
xlCurrencyTrailingZeros	True if trailing zeros are displayed for zero currency values.
xlCurrencyLeadingZeros	True if leading zeros are displayed for zero currency values.
xlMonthLeadingZero	True if a leading zero is displayed in months (when months are displayed as numbers).
xlDayLeadingZero	True if a leading zero is displayed in days.
xl4DigitYears	True if the system is using four-digit years; False if the system is using two-digit years.
xlMDY	True if the date order is month-day-year for dates displayed in the long form; False if the date order is day/month/year.
xlTimeLeadingZero	True if a leading zero is displayed in times.

Date and time settings

If your application writes formatted dates and will be used in other countries, you might want to make sure that the date is in a format familiar to the user. The best approach is to specify a date by using VBA's DateSerial function and let Excel take care of the formatting details. (It will use the user's short date format.)

The following procedure uses the DateSerial function to assign a date to the StartDate variable. This date is then written to cell A1 with the local short date format.

Sub WriteDate()

Dim StartDate As Date

StartDate = DateSerial(2010, 4, 15)

Range(“A1”) = StartDate

End Sub

If you need to do any other formatting for the date, you can write code to do so after the date has been entered into the cell. Excel provides several named date and time formats, plus quite a few named number formats. The online help describes all these formats (search for named date/time formats or named numeric formats).