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

In comparison with Excel 2000, Excel 2002 offers thirty-five new objects, though we are dealing here primarily with cosmetic changes. In the case of the few truly new developments (such as "smart tags"), it is doubtful whether they will find much use in practice. Moreover, it is difficult to employ the new objects in existing VBA code, because 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 the most security-conscious of readers of this book cannot consider this option for themselves . However, as a developer you should know that this possibility exists and that macros developed by you might not be able to be executed on another machine for the simple reason that VBA has not even been installed.

Pointer  

Further information on deactivation and deinstallation of VBA functions can be found in the knowledge base articles Q281954 and Q281953 , which at last sighting were located at the following addresses:

http://support.microsoft.com/default.aspx?scid=kb;[LN];Q281954

http://support.microsoft.com/default.aspx?scid=kb;[LN];Q281953

New and Changed Objects, Properties, and Methods

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 interpret this new feature as a possible admission by Microsoft that Excel is perhaps not quite so robust as it ought to be.

Worksheet 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 object has been expanded. 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 AllowEditRange[s] and UserAccess[List] .

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

Error checking TOOLSERROR CHECKING searches the active worksheet for a variety of errors, such as numbers stored as text, dates with two-digit year, and formulas that refer to empty cells. Whether such checking is to take place automatically in the background and which kinds of errors should be checked can be controlled via the ErrorCheckingOptions object. Errors that are found can be evaluated with the help of the Errors enumeration object.

Find Format With the Find and Replace 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 CellFormat objects, which are addressed via the properties FindFormat and ReplaceFormat .

Diagrams With InsertDiagram 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 joy in this new functionality is, alas, clouded by the fact that these new objects have been rather thoughtlessly designed and are replete with errors.

Pivot tables For the establishment and closing of a database connection to PivotTable objects the events PivotTableOpen and PivotTableClose come into play. These events are available under various names for the classes Application , Workbook , and Worksheet.

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 SmartTagXxx 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 object and associated Watches enumeration, individual cells from several worksheets can be displayed in an observation window (ToolsFormula AuditingShow Watch Window).

Pointer  

All Excel objects are documented in the object reference chapter of this book, Chapter 15. Objects that are new to Excel 2002 are specially marked .

Miscellaneous

.NET: Outside of Office, the dominant topic at Microsoft these days is .NET. Behind this abbreviation is hidden a new development platform that combines, among other things, 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 , described in Chapter 14, can be used under Excel 2000 and 2002.

Tip  

The Office programming language VBA is largely compatible with the Windows programming language Visual Basic 6, but is completely incompatible with the new .NET language Visual Basic .NET.

XML: Microsoft advertises Excel 2002 as XML compatible. (XML stands for Extensible Markup Language 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 of yet there are no other 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.