Excel 2016 Power Programming with VBA (2016)

Part IV. Developing Excel Applications

Chapter 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.

On the Web

And now that Microsoft Office is available on the web and on mobile devices such as tablets and phones, I expect compatibility issues to get even more complicated. These non-desktop versions of Office do not support VBA, add-ins, or features that rely on ActiveX controls.

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.

Note

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 because these types of issues are too numerous and complex.

Tip

A good source for information about potential compatibility problems is Microsoft’s support site. The URL is www.support.microsoft.com. Information at this site can often help you identify bugs that appear in a particular version of Excel.

Microsoft Office Compatibility Pack

If you plan to share your Excel 2016 application with others who use an Excel version before Excel 2007, you have two choices:

· Always save your files in the older XLS file format.

· Make sure the recipients of your files have installed Microsoft Office Compatibility Pack.

Microsoft Office Compatibility Pack is a free download available at www.microsoft.com. When installed, Office 2003 users can open, edit, and save documents, workbooks, and presentations in the new file formats for Word, Excel, and PowerPoint.

Keep in mind that this compatibility pack doesn’t endow earlier versions of Excel with any of the new features in Excel 2007 and later versions. It simply allows those users to open and save files in the new file format.

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.

Determining Excel’s version number

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. Use the VBA Val function to make the conversion. The following function, for example, returns True if the user is running Excel 2007 or later:

Function XL12OrLater()

XL12OrLater = Val(Application.Version) >= 12

End Function

Excel 2007 is version 12, Excel 2010 is version 14, Excel 2013 is version 15, and Excel 2016 is version 16. No version 13 exists, presumably because some people think it’s an unlucky number.

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.

On the Web

Ron de Bruin, a Microsoft Excel MPV in the Netherlands, created a web page with many examples relevant to VBA compatibility between Excel 2011 for Mac and Excel for Windows. The URL for the web page is http://www.rondebruin.nl/mac.htm.

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

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).

On the Web

The book’s website contains an example that demonstrates how to allow the user to choose from three languages in a dialog box: English, Spanish, or German. The example is in the multilingual wizard.xlsm file.

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

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

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).