|
Dataface, Inc. |
|
Software and services without fluff or bit-twiddling! |
|
Excel Improvements—Our Advice |
|
Dataface, Inc. |
|
Software and services without fluff or bit-twiddling! |
|
ALTERNATIVE TECHNOLOGIES There is currently a choice of technologies for automating Excel: It is no longer practical to maintain the old-style macros that existed until about Excel Release 4, even though they may still run in a compatibility mode. VBA (Visual Basic for Applications) is program code developed in the Office environment. It is very similar to VB6 (Visual Basic Release 6) and therefore pre-dates .NET. VSTO (Visual Studio Tools for Office) is an extension to the Visual Studio development environment used by .NET developers.
A project executed in VBA will usually be delivered more cheaply and quickly than a project executed in VSTO and will run on all PCs that run Office, without requiring the .NET Framework. VBA will be supported by Microsoft for as long as Office is not a .NET application itself. When Office might be converted to .NET and how far VBA support will continue to exist beyond that are matters for speculation.
There are already rare applications where VSTO might be the recommended technology, for example if the spreadsheet should be drawing its data from a Web Service.
AUTOMATION TECHNIQUES The most effective way to control an enhanced Excel application is usually to add drop down lists and buttons to an initial spreadsheet called “Control Panel” or something similar. The alternatives are to add custom entries to the Excel menu bar, to add a pop-up form, or to use Excel as a component (visible or invisible) called by a regular VB6 or .NET program.
LIMITATIONS A few limitations are encountered in some projects: Cell-by-cell processing on large spreadsheets can be very slow and difficult to improve. The number of rows and columns per spreadsheet is limited and workarounds are difficult. If the Chart Wizard does not produce acceptable results, it can be difficult to control the appearance (such as the placement of captions), and the results can be data-dependent and unsatisfactory).
DATA ENTRY ALTERNATIVES One common application for Excel is the data entry of documents like expense reports. In a large organization it would be appropriate to consider InfoPath (part of MS Office 2003) as an alternative to Excel. InfoPath is a very attractive and powerful user interface, but the state of the product is still “quirky” and it can be difficult to specialize.
|
