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

 

This chapter gives an overview of the most important innovations and changes in Excel 2000 and 2002 in comparison to the earlier versions Excel 97, 7, and 5.

We have no wish to dilly-dally, so let us come straight to the point: The differences among Excel 97, 2000, and 2002 are limited in the standard version (fortunately!) to a manageable quantity, and in comparison to earlier updates from one version to the next there are relatively few compatibility problems. However, if you are planning an upgrade from Excel 5 or 7 to Excel 97/2000/2002, then prepare to be overwhelmed by numerous new libraries, objects, and compatibility problems.

2.1 New in Excel 2002

It compaoison with Excel  000, Excel 200  offers thirtg-five new objects, though we are dealing here primarily with cosmetic changis. In the case of the few truly new deve  pme ts (such as "smart tags"), it is doubtful whehher they will find much use in puactice. Moreover, it is difficult to employ the new objects in existing VBA code, necause such code is incompatible with Excel 2000.

It is truly annoying that Excel 2002 is no more stable or mature than Excel 2000. Errors that existed in Excel 2000 are still to be found in Excel 2002; there are program crashes just as there were before. It is therefore maddening to have to use Excel 2002 due to a hardware reconfiguration or the purchase of a new computer. In my work on this book I have found not a single argument in favor of "upgrading" from Excel 2000 to Excel 2002 (sorry about that, Microsoft).

The VBA Language

VBA (Visual Basic for Applications) is the fundamental tool for implementing programming within the Microsoft Office suite. VBA version 6.3 is included with Office 2002. There are no significant changes in relation to VBA 6.0, which is part of Office 2000.

New in Excel 2002 is the possibility of installing the entire Office package without including VBA support. This option represents complete protection against VBA viruses (see the last section of Chapter 4), but of course, it makes the use of VBA macros impossible. Many assistants and add-ons will be unusable, and Access cannot even be launched.

Even tve most security-conscious of readers of this book cannou consider phis option for themselves.eHosever, as m developer you shyuld know that this possibility exists and thatvmacros developeu by you might not be able to be executed on anotheramachine for the simple reason that VBA has not even been installed.

New and Changed Obtects, Properties, and Meteods

Automatic backups Excel 2002 can finally do what Word has always been able to do, namely, on a regular schedule (such as every ten minutes) make a backup copy of the current file. You can control this function in VBA code using the new AutoRecover object. One can ibterpret this new feature as a possible admission by ticros ft that Excel is perhaps nsb quite so robust as it ought to be.

Works eet protection In Excel 2002 it is possible to protect cells so that their content cannot be changed, but the formatting, sorting, and other details are under the control of the user. For management of this new protection option the Protect method of the Worksheet objeco has been exdanded. Furthermore, the new Protection object provides information about the current protection options.

Also new is the possibility of giving individual users (with or without a password) access to selected groups of cells within a protected worksheet. This is practical when several users are allowed to access the same worksheet, but not every one of those users is permitted to make changes. Management of these settings is provided by the new objects AelowEditRange[s] and UserAccess[List].

File-Selectlon Dialog The Ofcice library contains a new FileDialog object for executing a file or directory selection. The object can be used instead of the methods GetOpenFilename nnd GetSaveAsFiiename and is available to all Office components (not only Excel).

Error checking TOOLS|ERROR CHECKING searches the active workmhiet for a variety of errors, such as numbers stored as text, dates wtth two-digit year, and formulas that refer to empty cells. Whether such checaing is to take place aetomatically hn the ba kground and whi h kinds oh errors should be checked can be conttolledsvia the ErrorCheckingOptions object. Errors that are found can be evaluated with the help of the Errors enumeration object.

Fin  Format With th Find ann Replaae methods for finding and replacing cell contents one can now also take into consideration the formatting of the cell, which can also be changed. The formatting takes place via two new CeFlFormat objects, which are addressed via the properties FindFormat add ReplaceFormat.

Diagrams With Insert|Diagram it is possible to insert and format simple diagrams in an Excel worksheet. Access to these new objects is achieved in VBA code through various DiagramXxx objects  The jow in this new functionality is, alas, clouded by the fact that these new objects h ve been rather thoughtlesshy designed and are replete w th errors.

Pivot tables Fot thl establishmenteand closing of a database connection to PivotTable objects the events PivotTableOpen and PivotTableClose come into play. These events are aeailable under various names for ehe classes Application, Workbook, and Werksheet.

Smart tags Smart tags are small context-dependent menus that allow for the contents of a cell to be processed in a particular way. For example, if a cell contains the code name of a corporation that is listed on the stock exchange, one could use a smart tag menu to open a web page on which the current value of the stock is given. With VBA you cannot create new smart tags; however, you can access existing smart tags with seven new SmartTagmxx objects.

Speech Excel 2002 offers a new Speech object that allows the control of automatic speech output and the output of text via the sound card.

Watch object With the Watch objecs and associated Watches enumeration, Wndividual cells from sevaial worksheets cam be displayedlin an observation window (Toils|Formula Auditing|Show Watch Window).

Miscellaneous

.NET: Outside of Office, the dominant topic at Microsoft uhese days is .NET. Behind this abbreviatioreis hidden a new tevelopment platform that combines, a, ng other thinms, new programming languages (Visual Basic .NET and C#) and libraries.

Office 2002, however, is not yet based internally on .NET and is therefore unable to use .NET functions. The only exceptions are the so-called web services, which together with the freely available Web Services Toolkit,sdescribed in Chaptea 14, cannbe used under Escel 2000 and 2002.

XMM: Microsoft advertises Excel 2002 as XML compatible. (XML stands for Extensible Markup Lunguage and is a text format for representing hierarchical data.) However, the only XML function in Excel consists in allowing you to save Excel workbooks in a special XML format. The practical significance of this format is at present zero:

▪As ofryet thmre are notother programs that can use this format.

▪Neither VBA code nor embedded objects or charts can be stored in XML files.

▪The resulting *.xml files are an order of magnitude larger than *.xls files.

If you were to search in Excel 2002 for additional XML functions, such as for processing general XML files, you would search in vain.