21
Understanding Compatibility Issues
In This Chapter
- Increasing the probability that your Excel 2016 applications will also work with previous versions of Excel
- Declaring API functions that work with 32-bit Excel 2016, 64-bit Excel 2016, and earlier versions of Excel
- Being aware of issues when you’re developing Excel applications for international use
What Is Compatibility?
Compatibility is an often-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 2016 applications will work with earlier versions of Excel for Windows and Excel for Mac. 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 2008 for Mac 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 2016 and Excel 2007 both use the same file format. If your application uses features that were introduced in Excel 2010 or later, you can’t expect that Excel 2007 users will magically have access to these new features.
Excel is a moving target, and you can’t guarantee complete compatibility. In most cases, you must 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 2007 through Excel 2016 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: Microsoft itself is responsible for some types of compatibility issues. For example, as I note in Chapter 18, index numbers for shortcut menus haven’t remained consistent across Excel versions.
- Windows versus Mac issues: If your application must work on both platforms, plan to spend lots of time ironing out various compatibility problems. Also, note that VBA was removed in Excel 2008 for Mac but then came back in Excel 2011 for Mac.
- Bit issues: Excel 2010 was 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 you can ensure compatibility in only one way: Test your application on every target platform and with every target version of Excel.
Avoid Using New Features
If your application must work with both Excel 2016 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 2003 and later, you should use Excel 2003 for development; then test thoroughly by using later versions.
A useful feature introduced in Excel 2007 is Compatibility Checker, shown in Figure 21.1. Display this dialog box by choosing File ➜ Info ➜ Check for Issues ➜ Check Compatibility. Compatibility Checker identifies any compatibility issues that might cause a problem if the file is opened using an earlier version of Excel.
Figure 21.1 Compatibility Checker.
Unfortunately, Compatibility Checker doesn’t look at the VBA code — which is a prime candidate for compatibility problems. However, you can download Microsoft Office Code Compatibility Inspector (search for it at www.microsoft.com). 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. Inspector adds comments to your code to identify potential problems and also creates a report. The Microsoft Office Code Compatibility Inspector was written for Office 2010 and apparently has not been updated since (but it still installs). Figure 21.2 shows a summary report.
Figure 21.2 A summary report from Microsoft Office Code Compatibility Inspector.
But Will It Work on a Mac?
A common problem that I hear about is Mac compatibility. Excel for Mac represents a small proportion of the total Excel market, and many developers choose simply to ignore it. The good news is that the 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. And, as I noted, Excel 2008 for Mac had no support for 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
WindowsOS = Application.OperatingSystem Like"*Win*"
End Function
Subtle (and not so subtle) differences exist between the Windows versions and the Mac versions of Excel. Many of these differences are cosmetic (for example, different default fonts), but others are more serious. For example, Excel for Mac doesn’t include ActiveX controls. Also, some Mac versions use the 1904 date system as the default but Excel for Windows uses the 1900 date system by default, so workbooks that use dates could be off by four years.
Another limitation concerns Windows API functions: They won’t work with Excel for Mac. If your application depends on such functions, you need to develop a workaround.
Here’s an example of a potential compatibility problem. If your code deals with paths and filenames, you need to construct your path with the appropriate path separator (a colon for the Mac, 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 and then modify the application so that it works on the other platform. In some situations, you’ll probably need to maintain two separate versions of your application.
You can make sure that your application is compatible with a particular Mac version of Excel in only one way: Test it thoroughly on a Mac — and be prepared to develop some workarounds for procedures that don’t work correctly.
Dealing with 64-Bit Excel
Starting with version 2010, you can install Excel 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 and add-ins created using the 32-bit version will work fine in the 64-bit version. Note, however, that ActiveX controls will not work in the 64-bit version. Also, 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:
Declare Function GetWindowsDirectoryA Lib"kernel32" _ (ByVal lpBuffer As String, ByVal nSize As Long) As Long
The following declaration works with Excel 2010 and later (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 and later).
- Win64 returns True if the code is running in 64-bit Excel.
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 64-Bit Excel 2010 and later. 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 21.1.
Table 21.1 Excel Country Codes
| Country Code | Country/Region | Language |
| 1 | United States | English |
| 7 | Russian Federation | Russian |
| 30 | Greece | Greek |
| 31 | The Netherlands | Dutch |
| 33 | France | French |
| 34 | Spain | Spanish |
| 36 | Hungary | Hungarian |
| 39 | Italy | Italian |
| 42 | Czech Republic | Czech |
| 45 | Denmark | Danish |
| 46 | Sweden | Swedish |
| 47 | Norway | Norwegian |
| 48 | Poland | Polish |
| 49 | Germany | German |
| 55 | Brazil | Portuguese |
| 66 | Thailand | Thai |
| 81 | Japan | Japanese |
| 82 | Korea | Korean |
| 84 | Vietnam | Vietnamese |
| 86 | People’s Republic of China | Simplified Chinese |
| 90 | Turkey | Turkish |
| 91 | India | Indian |
| 92 | Pakistan | Urdu |
| 351 | Portugal | Portuguese |
| 358 | Finland | Finnish |
| 886 | Taiwan | Traditional Chinese |
| 966 | Saudi Arabia | Arabic |
| 972 | Israel | Hebrew |
| 982 | Iran | Farsi |
Excel also supports language packs, so a single copy of Excel can 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 U.S. 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 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 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 or someone you know can translate your text).
The first step of the multilingual wizard 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 21.3 shows the UserForm displaying text in all three languages.
Figure 21.3 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 (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 21.2 (refer to the Help system for specific details).
Table 21.2 Properties That Have Local Versions
| Property | Local Version | Return Contents |
| Address | AddressLocal | Address |
| Category | CategoryLocal | Function category (XLM macros only) |
| Formula | FormulaLocal | Formula |
| FormulaR1C1 | FormulaR1C1Local | Formula, using R1C1 notation |
| Name | NameLocal | Name |
| NumberFormat | NumberFormatLocal | Number format |
| RefersTo | RefersToLocal | Reference |
| RefersToR1C1 | RefersToR1C1Local | Reference, using R1C1 notation |
Identifying system settings
Generally, you can’t assume that the end user’s system is set up like 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 21.3.
Table 21.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 the VBA 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(2016, 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 in the cell. Excel provides several named date and time formats, plus quite a few named number formats. The Help system describes all these formats (search for named date/time formats or named numeric formats).