2.2 New in Excel 2000

Language Features

VBA (Visual Basic for Applications) is the basis of all programming within Office. Office 2000 includes VBA version 6 (as opposed to VBA 5 in Office 97). Most of the changes are trivial:

There are several new functions for working with and formatting character strings: Join, InstrRev, Replace, Split, MonthName, WeekdayName, FormatCurrency, FormatDateTime, FormatPercent, and FormatNumber.
There are a few small improvements in object-oriented programming. You will still have to do without genuine inheritance (actually the determining feature of object-oriented programming languages), but at least now the keyword Implements makes possible the further ”albeit halfhearted ”use of existing classes.
In any case, for the daily practice of programming in Excel it is more important to be able to define classes with their own properties (keywords Event and RaiseEvent ).
With CallByName you can execute properties and methods by passing the name as a character string. In many cases this provides greater flexibility.

New and Changed Objects, Properties, and Methods

Directories The new property UserLibraryPath of the Application object provides the path to the local directory with add-in files. The effect of two Application properties has changed: TemplatesPath and StartupPath refer to the local Templates , respectively Xlstart , directories. (On the other hand, in Excel 97 the two properties give the path to the global Templates and Xlstart directories.) Unfortunately , there are no new properties to determine the global Templates or Xlstart directories.

Charts The labeling of the coordinate axes can now be scaled. Thus instead of displaying numbers like 21,000,000 or 22,500,000, the scaling factor "millions" can be given, resulting in the numerical values 21 and 22.5. For VBA programming this feature is controlled by several new DisplayUnitXxx properties.

Also new are the so-called pivot charts: These are ordinary charts ( Chart objects) whose contents, however, can be altered dynamically by certain pivot fields (as with pivot tables). The VBA control takes place via the new PivotLayout object, which is invoked by the property of the same name belonging to the Chart object.

Database Applications

Excel is, of course, NOT a database program. However, it is very often used for the analysis of externally stored data (for example, in the form of charts or pivot tables). Therefore, access to external data has always been of great importance to Excel programmers.

Usually, the simplest way to extract data from a database is by means of the program MS Query, which is launched via DataGet External DataNew Database Query. The settings implemented in MS Query can be controlled with the QueryTable object. In Excel 2000 this object is equipped with a host of new properties.

The new ADO library ( ActiveX Data Objects ) offers considerably more flexibility and above all the possibility of making changes to databases. The DAO library, which was previously used for this purpose, can still be employed, but it will not be developed further by Microsoft. Therefore, new database applications especially should use the ADO library. (However, there is seldom any reason not to retain existing applications that make use of DAO.) An introduction to ADO programming can be found in Chapter 12.

A popular method of analyzing data within Excel ”no matter whether the data come from traditional database systems or from a Data Warehouse ”is by means of pivot tables. These tables have existed for a long time, but in Excel 2000 they come equipped with a variety of extensions (about thirty new properties and methods). Pivot tables are the main focus of Chapter 13.

Internet

Although Office 2000 is touted by Microsoft as the "Internet office," from the point of view of VBA programming there is relatively little in the way of innovation:

HTML Export/Import Excel offers new functions for exporting Excel objects (for example, a range of cells ) in HTML format as well as for importation of data from HTML files. These functions can be run using program code with the objects PublishObject (Export) and WebQuery (Import).

Web Components These are controls suitable for the Internet in which an Excel table (or diagram) can be represented. While not all Excel functions are available in the controls, many, in fact, are. The advantage is that the data can be manipulated dynamically in the HTML document. However, there are associated disadvantages, the most fundamental of which is that web components are supposed to be used only by those possessing an Office 2000 license. To this extent the web components are unsuitable for the Internet, seeming to be rather designed for use in intranets (in large companies, for example).

E-Mail With the Workbook property EnvelopeVisible you can insert various text fields for the input of e-mail addresses and subject lines in the upper border of the table window. This makes it easier for the user to send a workbook interactively.

Miscellaneous

Dealing with Files (FSO Library) If you wish to access files or directories or need to read from or write to text files, then instead of the usual commands ( Open , Close, Print, etc.) you can use the new FSO library ( File Scripting Objects ). The objects defined there offer not only greater elegance in accessing data, but also for the first time they offer unicode support. Unfortunately, the FSO methods are not suitable for working with binary data ”for that you must still use the traditional functions.

Forms or Dialogs (UserForm) Forms can now be opened independently ( Show vbModeless ). That is, Excel visible below the form, can be used without the form having to be closed. Just be sure not to use these new functions in combination with the RefEdit control for input of ranges of cells. Otherwise, Excel loses control over the keyboard focus and can be terminated only through brute force methods (task manager or Ctrl+Alt+Del )!

Importing Text A frequent source of aggravation with previous versions of Excel was the attempt to automate the importation of ASCII files. Fortunately, there is progress to be reported . Finally, the decimal point and the thousands separator (for separating groups of three digits in the representation of numbers) can be controlled by additional parameters of the method OpenText. For importing text the revised QueryTable object can be used.

Configuration Files The locations of configuration files have changed again ”as has been the case with all previous versions. Everyone's favorite game is Look for me!

Help System The entire help system has been completely revised and is now based internally on the so-called HTMLHelp System. As far as content goes, the information offered is, in fact, often correct, but the problem is in finding the information. The F1 key by no means always leads to the desired result, and the full text search that was available in the earlier help system has for some incomprehensible reason disappeared. In its place there is now the so-called Office Assistant, who (or which, depending on user preference), however, is not an adequate substitute. Its results are often quite good, but much more often are simply useless. (Don't we already have enough assistants, and even these are always in the way?)

If you wish to equip your own Excel applications with their own help files, you can now use HTMLHelp for that purpose. For developing your own help files you should make use of the HTMLHelp Workshop, which is available for download at the Microsoft web site.

Euro Support In older editions of Office the euro symbol could be used only after installation of special updates. In this respect Office 2000 has made a certain amount of progress, but there is much on the theme of the euro that seems not to have occurred to Microsoft. The function EuroConvert , which can be used as an Excel add-in function, is not documented, and one can seek in vain other aids for converting existing tables from a given European currency into euros. (The section on the euro in Chapter 5 attempts to redress this lack, to the extent that such redress is possible.)

2.3 New in Excel 97

New in this version are the development environment and the notion of userdefined forms (MS Forms library). Moreover, almost half of the approximately 120 Excel-7 objects have been replaced by new objects. Additionally, countless new objects have been introduced.

The Development Environment

The most obvious change in Excel 97 was the separation of the development environment from Excel. This separation takes some getting used to, but it has a number of advantages. The greatest advance is the extension of incomplete keywords by an automatically displayed list of choices or by Ctrl+spacebar .

Module Sheets The division into application and programming components also has had effects on programming: The enumeration object Modules and the object class Module were no longer supported for module sheets and are currently available only for reasons of compatibility.

Protection of Module Sheets Due to the separation of Excel into application and VBA components the protection functions for modules were also revised. That would not have been so bad if a minimum of compatibility had been retained. That is not, unfortunately, the case: Hidden and protected modules in Excel 7 are displayed in Excel 97 as though they were unprotected . After all, you always wanted your customers and users to know how you programmed all those functions, didn't you?

VBA Language Concepts

Events The management of events was completely reworked in Excel 97. Whereas in Excel 5 and 7 a small number of predefined events were attached to OnEvent properties, now events are controlled in Visual Basic: For all possible events ”such as activating a worksheet ”the names of the event procedures are preset (such as Worksheet_Activate ). When you fill this procedure with code, the code is then automatically executed when the event occurs. Even events associated to objects that in the development environment are not shown in their own modules can be received via the detour of a class module.

The down side of the new concept of an event is that Excel programmers were perhaps a bit too enthusiastic: Almost every object is equipped with countless events. In the process clarity has been lost. It remains to be seen whether there will really be applications for all these events.

Class Modules Now in VBA as in Visual Basic new object classes with methods and properties can be defined (though without their own events and without Enum constants). However, the implementation gives the impression of being only half complete. Furthermore, there remains the question of whether there is a great need for programmer-defined class modules within Excel applications.

Collection Object The Collection object represents a convenient alternative to fields. The advantage over normal fields is that Collections do not have to be declared in advance in a particular size . Moreover, any text can be used as an index (instead of consecutive numbers).

User-Defined Functions In Excel 5 and 7 one could associate user-defined worksheet functions of various categories, which were exhibited by the dialog INSERTFUNCTION (the function assistant of the time). Since Excel 97 this possibility no longer exists officially, and all user-defined functions are associated with the data type user-defined . (This restriction can be gotten around; see section 5.7.)

Changed and Extended Objects

A large number of Excel objects were introduced in version 97, or were changed or associated to other libraries (and thereby renamed ). The most important changes ”to the extent that they have not been made obsolete by Excel 2000 ”are discussed in the following pages. A complete listing can be found in the VB help in Excel 2000 (start in the development environment!), by searching in the office assistant for Changes to the Microsoft Excel 97 Object Model.

The most important additions to the Workbook object have to do with the shared use of an Excel file by several users (sharing functions). To make an Excel file available for shared use, you must save the file using SaveAs with AccessMode:=xlShared as a shared file. (Manually, the sharing of Excel files is accomplished not with SAVE AS, but with ToolsShare Workbook.)

To terminate sharing, the method ExclusiveAccess is available. (With it the current workbook is saved under the current name.) The current state can be inferred from the property MultiUserEditing . There are a number of properties and methods for maintaining, managing, and synchronizing shared-use files: AcceptAllChanges, AutoUpdateFrequency, AutoUpdateSaveChanges, HighlightChangesOnScreen, HighlightChangesOptions, KeepChangesHistory, ListChangesOnNewSheet , PersonalViewListSettings , PersonalViewPrintSettings , ProtectSharing , RejectAllChanges and UnprotectSharing .

With the FormatCondition object the formatting of a cell or range of cells ( Range object) can be made dependent on the cell's contents. For example, you can arrange to have the color of a number change when the number exceeds a value contained in a comparison cell. There can be at most three conditions per cell. Manually, you can achieve this type of formatting with FormatConditional Formatting. (Similar effects could be achieved in earlier versions of Excel by conditional number formats. However, there were many fewer layout possibilities and no VBA interface.)

With the Validation object one can define validation controls for the input in cells. For example, input can be restricted to a particular format (e.g., date) or to within a range of values. Such rules can be formulated manually with DataValidation.

With the Hyperlink object one can provide references to Excel worksheets as well as to other files (local or on the Internet).

An entire family of objects ( Shape, ShapeRange, ShapeNode, GroupShapes, etc.) replaces the drawing objects of Excel 5/7 ( Arc, Line, etc.). Aside from the drawing objects, which are now called AutoShapes objects and offer much more in the way of layout possibilities, all the remaining objects in tables are controlled by Shape objects: controls, OLE objects, object groups, etc.

New Properties and Methods

By setting AutoScaleFont=True one can achieve for a variety of objects (AxisTitle, LegendEntry, etc.) that the size of the text is accommodated to the size of the object.

FormulaLabel=xlColumn/RowLabel the contents of a cell can be defined to be the name for the row or column the given cell heads. These names can then be used in formulas, thereby making formulas more readable. It is necessary that AcceptLabelsInFormulas be set to True (default setting). Manually, FormulaLabel can be changed with INSERTNAMELABEL.

In Figure 2.1 FormulaLabel=xlColumnLabels holds for the cells A1:C1. Thus in C2 the formula = income-expenses can be used. If a different text is input into A1 Text, then the formula is automatically changed!

Figure 2-1: Example of FormulaLabel

Warning

The feature just described continues to exist in Excel 2000, but there it is deactivated by default! To use it in Excel 2000, you must first in ToolsOptionsCalculation activate the option Accept labels in formulas.

A host of new formatting possibilities come from new properties of the Range object. IndentLevel sets how far the content of a cell is to be indented. (The permissible range is from 0 to 15.) With Borders it is now also possible to draw diagonal lines through a cell (previously, a cell could only be framed). With Orientation the text can be oriented between ˆ’ 90 and 90 degrees (previously, only multiples of 90 degrees were possible). The Orientation property can also be changed for many other objects, such as labels on charts.

Object Libraries

Menus / Toolbars (Office Library) You have no doubt noticed that the appearance of menus and toolbars has changed. The consequence for programmers is that the Toolbar and Menu objects have been replaced by a family of new CommandBar objects.

This results not only in serious changes for the management of custom menus and toolbars, but also in a number of incompatibilities. There is no longer a menu editor. Instead, in the creation of new menu bars one must allow oneself to be tormented by a number of incomprehensible pop-up menus (the starting point is ViewToolbarsCustomize). The only advantage in return for the time spent in adaptations and alterations is that the new menu bars and toolbars and the basic Office object library can be used together in all components of Office.

Forms (MS Forms Library) For setting up forms there is the new MS Forms Library. The forms created from it look just like the forms in Excel 5 and 7, but the forms editor is handled differently, and the management of the form by program code is considerably different. There are two fundamental advantages: You can now set up multipage forms (in the manner of the options forms) and use external controls (so-called ActiveX controls).

As opposed to the traditional menus and toolbars, Excel 5/7 forms are also supported in Excel 2000. That is, the forms editor is still provided. However, in the execution of VBA code for the management of forms one encounters frequent error messages. There are, then, considerable compatibility problems to some extent.

VBIDE Library The development environment is programmable via its own library: Microsoft Visual Basic for Applications Extensibility, VBIDE in the object catalog, fundamental object VBE . The library is of benefit when you wish to improve the usefulness of the development environment or change the code of an Excel file by means of program code.