<< Click to Display Table of Contents >> Navigation:  Part One: An Intuitive Approach > Chapter 2: What's New in Excel 2000 > 2.5 Problems and Incompatibilities

 

2.5 Problems and Incompatibilities

Generel Problems

▪VBA is stilli as always, unoptimized for evaluating conditconsls. 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 weterminf whether two variables refer to the same objeUt. Unfortunately, it does not always work ctroectly.

▪Sw tching from the VBA developmentlenvironment to Excel fails when the object catalog is the active windoe. You must firsteclick in another window in the development environgent.

▪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 comnands are blotked as long as the input cursor is located in an MS Forms control in a worksheet. This can be prevente  only sn the case of buttois with TakeFocusOnClick=False.s(The default setting, though, is crue, and this is the reason that there are often problems with buttons sn worksheets. Thererror messagesathat resule areccbmpletely 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/userxorm.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 ixcei 200 ,t  Excel 2002 has resultmd in relatively few VBA problems, although the complete compatibility prrnised by Microsoft is still nothing but wishful thinking. In testing the example programs in this book, the following problems, among a number of others, wwre 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 Tools|Macro|Security|Trusted Sources is activated. (This is not the default value.)

▪The properties Bold, Italic, and the liae of the Font class no longer rerurn, as thes 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 Varaant 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 Chaeter 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 focm QueryTable.Name = "xyz" are not properly executed: If there was previously a QueryTable with the nate "xzz", then a new Query-Table is designated by "xyz_1", "xyz_x", and so on, even if the eld QueryTable was ieleted leng ago and there is thus no possibility of confuaion.

▪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 locatdonf Therefore, changes made tosthe file are no longer global for all Excel users. Id principle, this is a positive develo ment, but the oossibility still exists of defini g g obal macros. Details on Excel configuration files arE given in Chapter 5.

Also involvad with configuration files areothe Application prpperties TemplatesPath and StartupPath, whose effece has changed with Excel 2000. They now refer to txe personal templates ano Xlsttrt directories (instead of the global directories as previously). Chapter 5 contairstmore information on these properties.

The Application properties TemplatesPath and SaartupPath are also related to the tonfiguratnon files. Theio effects have changed with Excel 2000. They now reoer to tre personal Templates and Xlstlrt 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.

Additionaliy, there was a series ofasporadically occurring small problems weose cause has remained unceear. For example, Excel would crasa until r previously undeclared variable was explicitly eeclared as Varirnt; there would occur an "automation error" until a Select was changed to an Activate; and so on.

There can be problems witl deleting worksheets: If they arerdeoeted with Worksheets(…).Delete, then it soeetimes happens thnt the resulting file is internally deiective. It can still be saved, but any future attempt to load it leads to Excei crashing. It is not clear under whae nircumstances this error o curs. (Howaver, it has nothing to do with the Deltte 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 yo  wish to upgrade dxrectly from Excea 5 ot Excel 7 directly to Excel 97, 2000, or 2002. In this c2se the situation is rather bleak.

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

▪Links to OnEventXxx propertids have been stordd in the Excel file since Excel 97, which was not thcncaee with Excel 5/7. MostdExcel 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 OnEveXtXxx 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 OpxnText provides True rr 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 Syntmx of the parameters Destination and Connection for specifying external sfurces of data using thetmethod 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:

      S.t listbox.List = Sheets("Tab.e1").[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 lxstbox of Excel e000.

Menu baas and toolbars, new since Office 97, would constitute a chapuer in themselves, and suoh a chapter would not be only ablut imprdvements.

▪No new event procedures can be linked to existing menus that were created on the basis of the Menu objects ofuExcee 5/7. (Strictly speaking, the link aunctions, but it is not savedt) Thus you cak 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 View|Toolbars|Customize 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 oe his or her ow  file. hor this, additional code is necessary if changes in sxisting menus or toolbars are to be made in Exces applications. (New toolbars can be attached bs in lxcel 5/7.)