2.5 Problems and Incompatibilities

Note

Every Excel developer cherishes the hope that the latest version of Excel will be better and more stable than the previous one. To some extent this hope is fulfilled, but nonetheless it frequently happens that in running or testing VBA code, Excel crashes. (So save your files early and often!)

Note that Excel doesn't always crash completely. Sometimes, of course, there is the obligatory announcement of a system error, but Excel remains in memory and allows the most recently opened files to be opened, though without the possibility of saving them. To be able to continue working properly, you must completely terminate Excel. Under Windows NT/2000/XP you use the Task Manager. Under Windows 9x/ME hit Ctrl+Alt+Del . A task list appears from which you can select Excel and forcibly terminate it.

General Problems

VBA is still, as always, unoptimized for evaluating conditionals. A test of the form If x>=0 And Sqr(x)<3 leads in the case of negative x to an error. (This has been a problem with Visual Basic since its inception, so at this point I have given up all hope of any improvement on this score.)
In all versions of Excel there are problems with the operator Is , used for comparing objects. This operator is supposed to determine whether two variables refer to the same object. Unfortunately, it does not always work correctly.
Switching from the VBA development environment to Excel fails when the object catalog is the active window. You must first click in another window in the development environment.
Almost as troublesome is that toolbars from Excel occasionally appear in the development environment, where they tend to be in the way. The moment they are clicked on, one experiences a (usually unwished for) switch back into Excel.

Problems with MS-Forms Dialogs and Controls

Excel and most VBA commands are blocked as long as the input cursor is located in an MS Forms control in a worksheet. This can be prevented only in the case of buttons with TakeFocusOnClick=False. (The default setting, though, is True, and this is the reason that there are often problems with buttons in worksheets. The error messages that result are completely uninformative.)

If other controls are used in a worksheet, then the input cursor must be placed in a cell by means of program code (such as Worksheets(n).[A1].Activate ) in order to make certain that it is not directed at a control.

In general, the use of control elements in worksheets (as opposed to forms) causes enormous problems and triggers ” especially in Excel 2002 ”incomprehensible errors and sometimes even crashes of Excel. The example file 07/userform.xls is particularly affected, and in the new edition of this book several examples had to be deleted; these examples functioned without problem under Excel 2000, but under Excel 2002 lead to crashes.

Problems with Excel 2002 in Relation to Excel 2000

The changeover from Excel 2000 to Excel 2002 has resulted in relatively few VBA problems, although the complete compatibility promised by Microsoft is still nothing but wishful thinking. In testing the example programs in this book, the following problems, among a number of others, were discovered :

The default security setting for Macros is now HIGH instead of Medium. Therefore, VBA macros can be executed only after the setting has been changed to Medium (see the last section of Chapter 4).
VBE code for dynamic code changing can be executed only when the option Trust Access To Visual Basic Project in the dialog ToolsMacroSecurityTrusted Sources is activated. (This is not the default value.)
The properties Bold , Italic , and the like of the Font class no longer return, as they did previously, only True and False , but sometimes the value Nothing as well. Therefore, these properties can no longer be processed with Boolean variables. Instead, you should use Variant variables.
Many assistants and add-ins are no longer included and are available, if at all, only as Internet downloads. This affects, for example, the template wizards described in Chapter 9.
Pivot fields are now invoked differently (for example, " sum of xy " instead of " sum - xy "). Code that relies on the old formulation will no longer function correctly.
References of the form QueryTable. Name = "xyz" are not properly executed: If there was previously a QueryTable with the name "xyz" , then a new Query-Table is designated by "xyz_1" , "xyz_2" , and so on, even if the old QueryTable was deleted long ago and there is thus no possibility of confusion.
Buttons embedded in worksheets appear as still pressed down even after the mouse has been disengaged (that is, the representation of the button does not pop back to its initial position). There are no other problems that arise as a consequence, it is just that the buttons behave visually incorrectly.

Problems with Excel 2000 in Relation to Excel 97

There are also few compatibility problems between Excel 97 and its successors Excel 2000 and 2002. The biggest problem is the new location of configuration files. Something new is that the so-called Personal Macro Workbook is for the first time truly personal. That is, this file is created individually for each user and saved in its own particular location. Therefore, changes made to the file are no longer global for all Excel users. In principle, this is a positive development, but the possibility still exists of defining global macros. Details on Excel configuration files are given in Chapter 5.

Also involved with configuration files are the Application properties TemplatesPath and StartupPath , whose effect has changed with Excel 2000. They now refer to the personal templates and Xlstart directories (instead of the global directories as previously). Chapter 5 contains more information on these properties.

The Application properties TemplatesPath and StartupPath are also related to the configuration files. Their effects have changed with Excel 2000. They now refer to the personal Templates and Xlstart directories (instead of, as previously, the global directories). Further information on these properties can be found in Chapter 5, in the section on Excel-specific methods and properties.

Additionally, there was a series of sporadically occurring small problems whose cause has remained unclear. For example, Excel would crash until a previously undeclared variable was explicitly declared as Variant ; there would occur an "automation error" until a Select was changed to an Activate ; and so on.

There can be problems with deleting worksheets: If they are deleted with Worksheets( ).Delete , then it sometimes happens that the resulting file is internally defective. It can still be saved, but any future attempt to load it leads to Excel crashing. It is not clear under what circumstances this error occurs. (However, it has nothing to do with the Delete method, since the same problem can also occur when the worksheet is deleted manually.)

Compatibility Problems with Respect to Excel 5 and Excel 7

The following information is valid if you wish to upgrade directly from Excel 5 or Excel 7 directly to Excel 97, 2000, or 2002. In this case the situation is rather bleak.

Note	Even Microsoft admits to upgrade problems and lists on its web site http://support.microsoft.com/default.aspx?scid=KB;EN-US;q162721& (this is the knowledge base article Q162721 in the MSDN library) no fewer than 75 separate problems.

Needless to say, then, the following list is incomplete. It does, however, list the most important problems. Most of the problems mentioned here are, to be sure, niggling details. But it is often such "details" that cost a full day's work searching for errors.

When a worksheet is accessed, many methods function only with Worksheets(n), but not with Sheets(n) (even if in both cases the same worksheet is referenced).
The formatting of charts via program code gives results different from those of Excel 7.
VBA code that accesses Selection often gives difficulties. This is even more annoying in that as a rule the automatic recording of a macro is the source of the problematic code. Try the following remedy: Change the lines
```
  object  .Select or  object.  Activate Selection.method 
```
to
```
  object.method  
```
This same course of action is also valid, of course, when Selection is used by means of With for several lines. (Give the actual object instead of Selection when using the With command.)
Links to OnEventXxx properties have been stored in the Excel file since Excel 97, which was not the case with Excel 5/7. Most Excel 5/7 applications rely on the fact that when a file is loaded, all OnEventXxx properties are empty. This is no longer the case, which can result in serious problems.

There are, for the most part, problems with the attempt to replace OnEventXxx procedures by the event procedures newly introduced in Excel 97. Excel suddenly complains that it can no longer find the procedures stored in OnEventXxx properties.

The problem is exacerbated by the fact that there is no possibility to determine all initialized OnEventXxx properties. It is necessary, rather, to test whether the property is being used in the immediate window for every conceivable OnEventXxx property (for every worksheet!). If it is, then you must delete the property by defining an empty character string "" . Enjoy!
In Excel 7 the method OpenText provides True or False as a return value according to whether the importation of data was successful. Since Excel 97 this method can no longer be used as a function, and there is no longer a return value. Possible errors must be caught with an error-handling routine.
The Syntax of the parameters Destination and Connection for specifying external sources of data using the method PivotTableWizard has changed. Excel 7 program code no longer runs in general.
MS Forms (new since Excel 97) can be decorated with drawing elements, text fields, or other Office objects. There are, however, many fewer visual formatting possibilities than there were for forms in Excel 5/7.
In compatibility mode for the display and management of forms from Excel 5/7 a problem occurs when you attempt to link a range of cells to a listbox:
```
 Set listbox.List = Sheets("Table1").[a1:a4] 
```
The direct linking of ranges of cells to lists is apparently no longer supported. You must either execute a loop over all the cells and insert the entries individually with AddItem or use the MS Forms listbox of Excel 2000.

Menu bars and toolbars, new since Office 97, would constitute a chapter in themselves , and such a chapter would not be only about improvements.

No new event procedures can be linked to existing menus that were created on the basis of the Menu objects of Excel 5/7. (Strictly speaking, the link functions, but it is not saved.) Thus you can continue to use existing menus, but you cannot change them. The transformation to new CommandBar menus is possible only manually and with considerable effort.
There is no longer a menu editor. The manual production of new menus is accomplished through the form ViewToolbarsCustomize and is tied to hundreds of mouse clicks. Context menus can still be changed only with program code.
Changes in predefined menus are no longer saved in the Excel file, but separately for each user in his or her own file. For this, additional code is necessary if changes in existing menus or toolbars are to be made in Excel applications. (New toolbars can be attached as in Excel 5/7.)