Microsoft Office Tutorials and References
In Depth Information
Chapter 10. Excel Applications
Chapter 10. Excel Applications
Simply put, we can define an Office application to be an Office "document" (for instance, an
Access database, Excel workbook, Word document, Word template, or PowerPoint presentation)
that contains some special customization. This customization usually takes the form of a
combination of VBA procedures and menu and/or toolbar customizations and is generally
designed to simplify or automate certain tasks. It may provide utilities , which are programs for
performing a specific task, such as printing or sorting.
This may seem like a fairly liberal definition. For instance, if we add a single custom menu item to
a Word template that simply adds a closing (Sincerely yours, etc.) to the end of a Word document,
we could consider this template to be a Word application. However, it is doubtful that we could
get anyone to buy this Word application!
The point we want to emphasize is that an Office application is quite different from a traditional
Windows application, such as Excel itself. Traditional Windows applications are built around a
main executable file. In the case of Excel, this file is called excel.exe . Of course, a complex
application like Excel involves many additional supporting files, such as additional executables,
help files, object library files, resource files, information files, ActiveX control files, and the
ubiquitous DLL files.
On the other hand, Office applications do not revolve around standalone executable files. Rather,
they are created within an Office document. In particular, an Access application is created within
an Access database, an Excel application is created within an Excel workbook, a Word application
is created within a Word document, and a PowerPoint application is created within a PowerPoint
presentation. Office applications can be created within Office templates or add-ins as well.
This raises a whole new set of issues related to the distribution of Office applications. In
developing an Office application for distribution, we must immediately deal with two issues.
Where do we put the code for this application, and what means do we provide the user to invoke
the features of the application? The first issue is complicated by whether we will allow the user to
have access to the application's code and data or not.
The answers to these questions depend, not surprisingly, on the nature of the application.
10.1 Providing Access to an Application's Features
I recently created an Excel application for a well-known fast food company. The company wanted
to send out data on sales and other things to its field offices, in the form of a rather complicated
Excel pivot table. They wanted the field personnel to be able to filter the pivot table by various
means (thus creating smaller pivot tables) as well as generate a variety of charts showing different
views of the data. (The complete application involved other features, but this will illustrate the
In particular, the main pivot table contains several types of data (sales, transaction counts, and so
on) for several Designated Marketing Areas (DMAs) and store types (company, franchise, or
both). One feature of the application is a chart-creating utility for this data. But where should the
code for this feature go and how should the field personnel be given access to this charting utility?
Since the charting utility directly involves the pivot table, it seems reasonable in this case to
simply place a command button labeled Make Chart(s) directly on the pivot table worksheet.