Presented by Steve Harshbarger
Steve Harshbarger is a director at Micro Modeling Associates (MMA), Inc., a Solution Provider Partner developing custom business applications for the Fortune 1000 using Microsoft® technology. He currently manages MMA's Washington, D.C. practice, and will be heading up the new MMA San Francisco office in mid-1997. Steve is coauthor of Microsoft Excel 97 Developer's Handbook (Microsoft Press, 1997), Official Microsoft Intranet Solutions with Microsoft Office 97, Microsoft FrontPage™ 97 (Microsoft Press, 1997), and Microsoft Excel Professional Techniques (Que, 1994). He is a frequent speaker at Microsoft-sponsored events including Tech·Ed, VBITS, Developer Days, and various product launches. Steve is also a Certified Public Accountant which gives him a unique perspective on business solutions. Prior to joining Micro Modeling, Steve was a senior consultant at KPMG Peat Marwick in Washington, DC.
Phone: (301)657-1385
Fax: (301)657-0025
Email: harshbargers@micromodeling.com
Microsoft® Excel 97 is a significant move forward for the developer of custom applications. This white paper provides and overview of the major new features and architectural changes of interested to the Microsoft Visual Basic® Applications developer. It is assumed that the reader is already familiar with Microsoft Excel development and the Microsoft Excel object model under the Microsoft Excel spreadsheet version 7.0.
The following are the new features of Microsoft Excel 97 of interest to the developer. Each will be discussed in detail in this paper.
New Visual Basic Applications development environment. A new common development environment for Visual Basic Applications is now shared across the Microsoft Office office suite except the Microsoft Access database. Referred to as the Visual Basic Editor (VBE), it runs in a separate window from Microsoft Excel, and provides enhanced editing, debugging, and form design tools. The Visual Basic Editor is a subset of the Visual Basic 5.0 IDE.
New Microsoft Excel event model. An expanded event model consistent with Visual Basic is now available for Microsoft Excel. The Microsoft Excel spreadsheet, including the worksheet, workbook, and chart supports a robust set of event procedures.
Greater user interface control. A number of features that support development of sophisticated worksheet-based forms have been added. These include conditional formatting, data validation, and more granular control over protection of worksheets and objects.
Enhanced data features. Pivot tables can manage large data sets and perform more calculations on the underlying data. A new QueryTable object manages embedded database and Internet queries, and even provides an event model.
New forms and controls. A common forms package shared by Office 97 is now available. More functional dialog boxes can be designed in an environment identical to Visual Basic, complete with form events and custom controls. A new set of standard controls can be used on forms and embedded directly into Microsoft Excel worksheets.
Internet features. The new WebQuery feature can retrieve data from the Internet and copy it into a workbook. Microsoft Excel can read and write Hypertext Markup Language (HTML) documents and even support special HTML tags to create pivot tables and autofilters from tables. Hyperlinks can be embedded into workbooks. A new Hyperlink object exposes this functionality programmatically.
Common Office 97 objects. Common to Office 97 programs are CommandBars, a unified object model for both menus and toolbars. A new drawing object model called Shapes manipulates embedded graphical elements. In addition, the new Office Assistant Help feature is programmable through Visual Basic Applications.
New add-in model. There is a new model for producing and distributing add-ins across Office 97 programs. This topic is covered in detail in another session.
Microsoft Excel 97 has a brand new development environment known as the Visual Basic Editor (VBE). The Visual Basic Editor is shared between Microsoft Excel, the Microsoft Word word-processor, and the Microsoft PowerPoint® 97 presentation graphics program, and is a subset of Visual Basic 5.0. Microsoft licenses it to third-party products.
The Visual Basic Editor has a set of new productivity features for developers, including keywords, object properties and methods, and function arguments. The debugger adds “data-tips” (you can hold the pointer over a variable to see its value) and a watch window so you can drill into complex data structures like objects, user-defined types, and arrays with a simple tree control.
The Visual Basic language extensions originally introduced in Visual Basic 4.0 are still available. These include conditional compilation and class modules. Finally, the Visual Basic Editor itself has an object model so you can write add-ins to automate and customize the development environment.
Microsoft Excel now supports a rich set of events that replace and extend the Auto_Open, Auto_Close, and On events previously available in the product. Events are now exposed in a manner similar to that of Visual Basic-as event procedures behind objects, and, in this case-Microsoft Excel objects instead of forms and controls. Events are available for the following Microsoft Excel objects:
In the Visual Basic Editor, event handling code is written as an event procedure for the particular event:
Each worksheet in a workbook is independent and therefore has its own set of event handlers specific to each worksheet. The Worksheet object provides the following set of events.
Activate and Deactivate. These events fire when the worksheet is activated or deactivated respectively.
BeforeDoubleClick. This event fires when the user double-clicks the worksheet, but before the default behavior occurs, such as cell-editing mode. You can override the default behavior by setting the Cancel argument of this event procedure to True.
BeforeRightClick. This event fires when the user right-clicks the worksheet, but before the default behavior occurs, such as displaying a shortcut menu. You can override the default behavior by setting the Cancel argument of this event procedure to True.
Calculate. This event fires every time the worksheet recalculates.
Change. This event fires every time a value in the worksheet changes. A Range object representing the cell or cells changing is passed in as an argument.
SelectionChange. This event fires when the selection is changed, such as, when the cell pointer moves. A Range object representing the new selection is passed in as an argument.
The Workbook object provides the following events for the workbook:
Open. This event fires when the workbook is opened and is analogous to the previous Auto_Open macro.
BeforeClose. This event fires when the workbook is about to be closed and is analogous to the previous Auto_Close macro. Through its Cancel argument, you can prevent the workbook from closing in this event.
Activate and Deactivate. These events fire when the workbook is activated or deactivated, respectively. Note that if two windows are open on the same workbook, activating the second window will not fire these events.
BeforePrint. This event fires before the workbook is printed and gives you the option to cancel printing through an argument.
BeforeSave. This event fires before the workbook is printed and gives you the option to cancel printing through an argument.
NewSheet. This event fires when a worksheet is added to the workbook, passing a reference to the new worksheet as an argument.
WindowActivate, WindowDeactivate, and WindowResize. These events fire on the activation, deactivation, and resizing of a workbook window. Note that these are specific to each open window when more than one window is open on a single workbook.
AddinInstall and AddinUninstall. These events fire when the workbook is installed or uninstalled as an add-in using the Microsoft Excel Tools menu, Add-ins command. This gives authors the ability to write custom installation and uninstallation code for their applications.
Workbook worksheet events
The Workbook object also provides a set of event which duplicate the worksheet-level events. This will be useful because the worksheet events are specific to each worksheet, but if you want an event to be the same for every worksheet in a workbook, you can use the workbook worksheet events and only write them once. These events fire for new worksheets added at run time as well. As for order of execution, any worksheet-specific events will fire before the workbook-level worksheet events.
The names of the workbook-level events are the same, except Sheet is added as a prefix. An argument is added to each event to pass in a reference to the worksheet for which the event is firing. Other than that, these events work the same as the worksheet-level versions:
SheetActivate, SheetDeactivate
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetChange
SheetSelectionChange
Charts also provide a set of events:
Activate and Deactivate. These events fire when the chart is activated or deactivated, respectively.
BeforeDoubleClick. This event fires when the user double-clicks the worksheet, but before the default behavior occurs, such as cell-editing mode. You can override the default behavior by setting the Cancel argument of this event procedure to True.
BeforeRightClick. This event fires when the user right-clicks the worksheet, but before the default behavior occurs, such as displaying a shortcut menu. You can override the default behavior by setting the Cancel argument of this event procedure to True.
Calculate. This event fires when the chart is recalculated.
DragOver and DragPlot. DragOver fires when a range of cells is dragged over a chart, and DragPlot fires when the cells are dropped.
MouseDown, MouseMove, and MouseUp. These events fire when the mouse is pressed, moved, and released, respectively. The coordinates of the mouse are passed in as arguments. These can be used as input the GetChartElement method of the Chart object, which can be used to determine which chart element is located at the given coordinates.
Resize. This event fires when a chart is resized.
Select. This event fires when any element of a chart is selected. The ElementID argument indicates which element was selected, and the Arg1 and Arg2 elements provide further information about that element, if appropriate.
SeriesChange. This event fires when the user changes the value of a chart data point.
Creating an Event Sink for embedded charts
The following text is quoted from the Microsoft Excel 97 help file, and explains the concept of “Event Sinks” very well:
Events are enabled for chart worksheets by default. Before you can use events with a Chart object that represents an embedded chart, you must create a new class module and declare an object of type Chart with events. For example, assume that a new class module is created and named EventClassModule. The new class module contains the following code:
Public WithEvents myChartClass As Chart
After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for this object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)
Before your procedures will run, however, you must connect the declared object in the class module with the embedded chart. You can do this by using the following code from any module:
Dim myClassModule As New EventClassModule
Sub InitializeChart()
Set myClassModule.myChartClass = _
Worksheets(1).ChartObjects(1).Chart
End Sub
After you run the InitializeChart procedure, the myChartClass object in the class module points to an embedded chart on Worksheet1, and the event procedures in the class module will run when the events occur.
The Microsoft Excel Application object also provides an event model. Like an embedded chart, you’ll need to set up an event sink to respond to it. One unique event, NewWorkbook, is provided to fire whenever a new workbook is added to the workspace. The other events are application-level worksheet and workbook events, similar to the concept introduced in “Workbook Worksheet Events “). Refer to the Microsoft Excel 97 Help file for more information about these events.
New features that support development of worksheet-based forms include conditional formatting, data validation, and control over protection of worksheets and objects.
With conditional formatting Microsoft Excel automatically reformats a cell or range of cells based on its value (or based on the result of a formula derived from its value). Unlike simple number formats (which can change based upon a number’s sign), conditional cell formatting encompasses fonts, borders, and patterns. This feature is particular well suited to EIS applications where you can data fit some criteria.
Data validation is the ability to have data integrity edits defined and enforced by Microsoft Excel for particular cells or ranges of cells. Using this feature, you can: limit entries in any cell to specific ranges of whole numbers, decimal numbers, dates, and times; text to a limited number of characters; predefined pick lists of values; and special validations through formulas. You can also define an input message to automatically display upon activation of the cell, and error messages to display when invalid data is entered.
There are a set of new properties for worksheets, charts, and pivot tables that allow greater control over what a user is allowed to do with these objects at run time. You will typically set these properties in code.
Workbook properties
EnableSelection can be set to xlUnlockedCells to prevent the user from selecting locked cells, or to xlNoSelection to prevent the user from selecting any cell on the worksheet.
By setting the ScrollArea property to a range of cells, the user will be unable to scroll the worksheet beyond that range, or select cells outside that range.
EnableAutoFilter, EnableOutlining, and EnablePivotTable can be set to True to enable these respective features on a worksheet even when it is otherwise protected.
EnableCalculation, when set to False, prevents Microsoft Excel from calculating the worksheet during automatic recalculation. Instead, the worksheet must be recalculated manually through user commands or through Visual Basic Applications code.
Chart properties
ProtectSelection, when set to True, prevents chart elements from being selected and also prevents the Click and DoubleClick events from firing.
ProtectFormatting, when set to True, prevent the chart’s formatting from being changed.
ProtectGoalSeek, when set to True, prevents the user from moving chart data points with the pointer.
ProtectData, when set to True, prevents the series formulas from being modified.
ChartObject properties
The following applies to the ChartObject object, which represents the frame in which a chart is embedded in a worksheet.
ProtectChartObject, when set to True, prevents an embedded chart from being moved, sized, or deleted.
PivotTable properties
EnableDrillDown, when set to False, prevents drilling down to the detail behind an item by double-clicking it.
EnableFieldDialog, when set to False, prevents the dialog box for formatting PivotFields from displaying.
EnableWizard, when set to False, prevents the PivotTable Wizard dialog box from displaying.
In addition to these properties, there are many properties of the PivotField and PivotItem objects that allow for finer control over individual parts of pivot tables.
Pivot tables have a new feature called server-based page fields that can be used to manage memory when dealing with large data sets. For PivotFields in the page position, setting the ServedBased property to True will cause the pivot table to query only data into its cache that matched the currently displayed value in the page field. Thus, each time the selection is changed in the page field, the query is reexecuted and the cache rebuilt. For large data sets, this can help manage the size of the cache. Of course, for small data sets, this will probably slow performance since it incurs a database hit every time the selection is changed. Having this property gives you the option of tuning the pivot table cache according to the situation. Note that when a field is server-based, the All selection is not available since the whole data set is never in memory at once.
The simple Microsoft Query-based database query facility has been expanded in Microsoft Excel 97. It is now easier to pass parameters into queries, and to control how data is returned to the worksheet. For example, cells can be inserted or formulas copied to adjacent cells. Queries can also be run asynchronously.
The QueryTable object provides a programmatic interface to these queries, so you can run and manipulate them though code. The QueryTables collection contains all the QueryTables in a given worksheet. The QueryTable object also represents Web queries, which are discussed in a subsequent section.
Finally, QueryTables provide two events, BeforeRefresh and AfterRefresh. You must set up an event sink to use these events.
A common forms package shared by Visual Basic Applications and Visual Basic is now available. More functional dialog boxes can be designed in an environment identical to Visual Basic, complete with all the common form events. The following standard controls are available as well:
| · Label | · Frame |
| · TextBox | · CommandButton |
| · ComboBox | · TabStrip |
| · ListBox | · MultiPage |
| · CheckBox | · ScrollBar |
| · OptionButton | · SpinButton |
| · ToggleButton | · Image |
| · RefEdit (a control used to “point” at ranges on a worksheet while a modal dialog box is displayed) |
These controls support full-event models similar to those in controls you will find in Visual Basic.
One useful feature in the worksheets in earlier versions of Microsoft Excel that are not in Microsoft Excel 97 was the ability to limit the data type entered into a text box by setting a property. You must now write code to validate data types after the data has been entered.
The majority of the new standard ActiveX controls set can be embedded into worksheets, as can custom ActiveX controls. The properties, methods, and events can be accessed and used within the Visual Basic Editor.
The standard ActiveX controls have certain Microsoft Excel-specific extensions, such as the ability to link a list box to a cell range.
There are a number of issues to be aware of when considering embedding ActiveX controls in a workbook:
The old controls (still available on the Forms toolbar) do not have any of these issues and are a viable option for development. Of course, the new controls will improve over time with subsequent releases of Microsoft Excel.
Microsoft Excel 97 has a set of new features for Internet and intranet development.
With Web Queries, Microsoft Excel retrieves data from a Web site over the HTTP protocol and places it directly into a worksheet. Parameters can be passed from cells, hard-coded lists, or through prompts. In code, Web queries can be created and executed using the QueryTable object. Incidentally, this object also represents database queries, so one unified object can be used for different sources of data.
Microsoft Excel can read and write HTML files, and because of the Microsoft Excel grid-like nature, handle HTML tables. Web authors will find it a viable tool for creating table-based HTML content.
Microsoft Excel-specific HTML extensions
Microsoft Excel supports several special HTML attributes for tables that can cause Microsoft Excel to display table data as pivot tables, with autofilters and subtotals. These attributes are ignored by regular Web browsers. This means that you can create Web content which Microsoft Excel users can get extra benefit out of, but which is still available to ordinary users.
Hyperlinks to Web sites, other areas of a workbook, and other Office files can be embedded directly into workbooks. Text in cells and graphic objects can serve as hyperlinks. The Hyperlink object model controls this functionality programmatically.
All Office 97 applications have a new programming model for menus and toolbars. Since menus and toolbars have been merged into variations on the same thing in the user interface, it is fitting that their object representation is unified in the CommandBar objects. Using these objects, you can modify existing menus and toolbars as well as create new ones from scratch.
As the following diagram shows, the Microsoft Excel Application object contains a collection of CommandBar objects. This collection contains each built-in menu, toolbar, and shortcut menu in Microsoft Excel. You can create a new menu or toolbar by calling the Add method of the CommandBars collection.
Each CommandBar can contains a collection of controls represented by the CommandBarControls collection, which is accessed by the Control property. Each control must be one of the following three specific types:
Office 97 introduces a common set of drawing tools accessed through the OfficeArt toolbar. The programming equivalent is the Shapes object model. In Microsoft Excel, the worksheet contains a Shapes collection of Shape objects. A Shape can represent any graphic object embedded onto a worksheet, and can represent the container for embedded controls.
Properties that describe a Shape are organized into subobjects for clarity. Although there are more than depicted in the following diagram, the major ones are:
The ShapeRange The object can be used programmatically to manipulate a group of shapes as if they were one. Once you assign a group of shapes to a ShapeRange, it contains the same subobjects and properties of an individual shape.
There are a number of issues to consider when migrating applications from previous versions of Microsoft Excel to Microsoft Excel 97. The most comprehensive document on these issues is a whitepaper available on the Microsoft Office Developer’s Web site (www.microsoft.com/officedev) entitled Migrating Solutions to Microsoft Excel 97.
For more information on Microsoft Excel 97 development, the Microsoft Excel 97 Developer’s Handbook, by Eric Wells and Steve Harshbarger, is available from Microsoft Press.
For more information on developing with Microsoft Excel 97, Office 97, and other Microsoft tools for intranet applications, try Official Microsoft Intranet Solutions by Micro Modeling Associates, Inc., also available from Microsoft Press.
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Microsoft Press, PowerPoint, and Visual Basic are registered trademarks and ActiveX and FrontPage are trademarks of Microsoft Corporation.
Other product or company names mentioned herein may by the trademarks of their respective owners.