Introduction

Excel made its debut on the Macintosh in 1985 and has never lost its position as the most popular spreadsheet application in the Mac environment. In 1987, Excel was ported to the PC, running under Windows. It took many years for Excel to overtake Lotus 1-2-3, which was one of the most successful software systems in the history of computing at that time.

There were a number of spreadsheet applications that enjoyed success prior to the release of the IBM PC in 1981. Among these were VisiCalc, Quattro Pro, and Multiplan. VisiCalc started it all, but fell by the wayside early on. Multiplan was Microsoft's predecessor to Excel, using the R1C1 cell addressing which is still available as an option in Excel. But it was 1-2-3 that shot to stardom very soon after its release in 1982 and came to dominate the PC spreadsheet market.

Early Spreadsheet Macros

1-2-3 was the first spreadsheet application to offer spreadsheet, charting, and database capabilities in one package. However, the main reason for its runaway success was its macro capability. Legend has it that the 1-2-3 developers set up macros as a debugging and testing mechanism for the product. It is said that they only realized the potential of macros at the last minute, and included them into the final release pretty much as an afterthought.

Whatever their origins, macros gave non-programmers a simple way to become programmers and automate their spreadsheets. They grabbed the opportunity and ran. At last they had a measure of independence from the computer department.

The original 1-2-3 macros performed a task by executing the same keystrokes that a user would use to carry out the same task. It was, therefore, very simple to create a macro as there was virtually nothing new to learn to progress from normal spreadsheet manipulation to programmed manipulation. All you had to do was remember what keys to press and write them down. The only concessions to traditional programming were eight extra commands, the /x commands. The /x commands provided some primitive decision making and branching capabilities, a way to get input from a user, and a way to construct menus.

One major problem with 1-2-3 macros was their vulnerability. The multisheet workbook had not yet been invented and macros had to be written directly into the cells of the spreadsheet they supported, along with input data and calculations. Macros were at the mercy of the user. For example, they could be inadvertently disrupted when a user inserted or deleted rows or columns. Macros were also at the mercy of the programmer. A badly designed macro could destroy itself quite easily while trying to edit spreadsheet data.

Despite the problems, users reveled in their newfound programming ability and millions of lines of code were written in this cryptic language, using arcane techniques to get around its many limitations. The world came to rely on code that was often badly designed, nearly always poorly documented, and at all times highly vulnerable, often supporting enterprise-critical control systems.

The XLM Macro Language

The original Excel macro language required you to write your macros in a macro sheet that was saved in a file with an .xlm extension. In this way, macros were kept separate from the worksheet, which was saved in a file with an .xls extension. These macros are now often referred to as XLM macros, or Excel 4 macros, to distinguish them from the VBA macro language introduced in Excel Version 5.

The XLM macro language consisted of function calls, arranged in columns in the macro sheet. There were many hundreds of functions necessary to provide all the features of Excel and allow programmatic control. The XLM language was far more sophisticated and powerful than the 1-2-3 macro language, even allowing for the enhancements made in 1-2-3 Releases 2 and 3. However, the code produced was not much more intelligible.

The sophistication of Excel's macro language was a two-edged sword. It appealed to those with high programming aptitude, who could tap the language's power, but was a barrier to most users. There was no simple relationship between the way you would manually operate Excel and the way you programmed it. There was a very steep learning curve involved in mastering the XLM language.

Another barrier to Excel's acceptance on the PC was that it required Windows. The early versions of Windows were restricted by limited access to memory, and Windows required much more horsepower to operate than DOS. The Graphical User Interface was appealing, but the tradeoffs in hardware cost and operating speed were perceived as problems.

Lotus made the mistake of assuming that Windows was a flash in the pan, soon to be replaced by OS/2, and did not bother to plan a Windows version of 1-2-3. Lotus put its energy into 1-2-3/G, a very nice GUI version of 1-2-3 that only operated under OS/2. This one horse bet was to prove the undoing of 1-2-3.

By the time it became clear that Windows was here to stay, Lotus was in real trouble as it watched users flocking to Excel. The first attempt at a Windows version of 1-2-3, released in 1991, was really 1-2-3 Release 3 for DOS in a thin GUI shell. Succeeding releases have closed the gap between 1-2-3 and Excel, but have been too late to stop the almost universal adoption of Microsoft Office by the market.

Excel 5

Microsoft took a brave decision to unify the programming code behind its Office applications by introducing VBA (Visual Basic for Applications) as the common macro language in Office. Excel 5, released in 1993, was the first application to include VBA. It has been gradually introduced into the other Office applications in subsequent versions of Office. Excel, Word, Access, PowerPoint, FrontPage, Visio, Project, and Outlook all use VBA as their macro language in Office XP. (Microsoft is clearly expanding their commitment to VBA among their product offerings.)

Since the release of Excel 5, Excel has supported both the XLM and the VBA macro languages, and the support for XLM should continue into the foreseeable future, but will decrease in significance as users switch to VBA.

VBA is an object-oriented programming language that is identical to the Visual Basic 6.0 programming language in the way it is structured and in the way it handles objects. In future versions of VBA you will likely see VBA become increasingly similar to Visual Basic .NET. If you learn to use VBA in Excel, you know how to use it in the other Office applications.

The Office applications differ in the objects they expose to VBA. To program an application, you need to be familiar with its object model. The object model is a hierarchy of all the objects that you find in the application. For example, part of the Excel Object Model tells us that there is an Application object that contains a Workbook object that contains a Worksheet object that contains a Range object.

In Excel 5, VBA code was written in modules, which were sheets in a workbook. Worksheets, chart sheets, and dialog sheets were other types of sheets that could be contained in an Excel 5 workbook.

Excel 97

In Excel 97, Microsoft introduced some dramatic changes in the VBA interface and some changes in the Excel Object Model. From Excel 97 onwards, modules are not visible in the Excel application window and modules are no longer objects contained by the Workbook object. Modules are contained in the VBA project associated with the workbook and can only be viewed and edited in the Visual Basic Editor (VBE) window.

In addition to the standard modules, class modules were introduced, which allow you to create your own classes. Commandbars were introduced to replace menus and toolbars, and UserForms replaced dialog sheets. Like modules, UserForms can only be edited in the VBE window. As usual, the replaced objects are still supported in Excel, but are considered to be hidden objects and are not documented in the Help screens.

In previous versions of Excel, objects such as buttons embedded in worksheets could only respond to a single event, usually the Click event. Excel 97 greatly increased the number of events that VBA code can respond to and formalized the way in which this is done by providing event procedures for the workbook, worksheet and chart sheet objects. For example, workbooks now have 20 events they can respond to, such as BeforeSave, BeforePrint, and BeforeClose. Excel 97 also introduced ActiveX controls that can be embedded in worksheets and UserForms. ActiveX controls can respond to a wide range of events such as GotFocus, MouseMove, and DblClick.

The VBE provides users with much more help than was previously available. For example, as we write code, popups appear with lists of appropriate methods and properties for objects, and arguments and parameter values for functions and methods. The Object Browser is much better than the previous versions, allowing us to search for entries, for example, and providing comprehensive information on intrinsic constants.

Microsoft has provided an Extensibility library that makes it possible to write VBA code that manipulates the VBE environment and VBA projects. This makes it possible to write codes that can directly access code modules and UserForms. It is possible to set up applications that indent module code or export code from modules to text files, for example.

Excel 97 has been ported to the Macintosh in the form of Excel 98. Unfortunately, many of the VBE Help features that make life easy for programmers have not been included. The VBE Extensibility features have not made it to the Mac either.

Excel 2000

Excel 2000 did not introduce dramatic changes from a VBA programming perspective. There were a large number of improvements in the Office 2000 and Excel 2000 user interfaces and improvements in some Excel features such as PivotTables. A new PivotChart feature was added. Web users benefited the most from Excel 2000, especially through the ability to save workbooks as Web pages. There were also improvements for users with a need to share information, through new online collaboration features.

One long awaited improvement for VBA users was the introduction of modeless UserForms. Previously, Excel only supported model dialog boxes, which take the focus when they are on screen so that no other activity can take place until they are closed. Modeless dialog boxes allow the user to continue with other work while the dialog box floats above the worksheet. Modeless dialog boxes can be used to show a “splash” screen when an application written in Excel is loaded and to display a progress indicator while a lengthy macro runs.

Excel 2002

Excel 2002 has also introduced only incremental changes. Once more, the major improvements have been in the user interface rather than in programming features. Microsoft continues to concentrate on improving Web-related features to make it easier to access and distribute data using the Internet. New features that could be useful for VBA programmers include a new Protection object, SmartTags, RTD (Real Time Data), and improved support for XML.

The new Protection object lets us selectively control the features that are accessible to users when we protect a worksheet. We can decide whether users can sort, alter cell formatting, or insert and delete rows and columns, for example. There is also a new AllowEditRange object that we can use to specify which users can edit specific ranges and whether they must use a password to do so. We can apply different combinations of permissions to different ranges.

SmartTags allow Excel to recognize data typed into cells as having special significance. For example, Excel 2002 can recognize stock market abbreviations, such as MSFT for Microsoft Corporation. When Excel sees an item like this, it displays a SmartTag symbol that has a popup menu. We can use the menu to obtain related information, such as the latest stock price or a summary report on the company. Microsoft provides a kit that allows developers to create new SmartTag software, so we could see a whole new class of tools appearing that use SmartTags to make data available throughout an organization or across the Internet.

RTD allows developers to create sources of information that users can draw from. Once you establish a link to a worksheet, changes in the source data are automatically passed on. An obvious use for this is to obtain stock prices that change in real time during the course of trading. Other possible applications include the ability to log data from scientific instruments or industrial process controllers. As with SmartTags, we will probably see a host of applications developed to make it easy for Excel users to gain access to dynamic information.

Improved XML support means it is getting easier to create applications that exchange data through the Internet and intranets. As we all become more dependent on these burgeoning technologies, this will become of increasing importance.

Excel 2003

The Web is a ubiquitous part of modern life. With the Web's increasing importance in the world, Microsoft has shifted focus to providing revisions that respond to this growing relevance in the computing world. For this reason, you will see that many of the changes to Excel 2003 are changes that reflect an increasingly connected world.

In addition to new Internet features, you will find extended workbook capabilities, new functionality for analyzing data, better support for XML and sharing workbooks on the Internet, and an improved user experience.

XML, or eXtensible Markup Language, is a self-extensible hypertext language. Ultimately, XML is text and a public, non-proprietary industry standard that is easy to extend for a wide variety of uses and moves easily over the Internet. Due to the greater support for XML Excel data it will become increasingly easier to share spreadsheet data with other Enterprise solutions that use XML too.

Greater support exists for managing ranges as list and the user interface experience is enhanced by adding features that permit modifying, filtering, and identifying these lists.

You can more easily share and update your Excel data with Windows Sharepoint Services. For example, changes made in Excel to lists shared on Sharepoint Services are automatically updated on Sharepoint Services. Support for modifying data offline and resynchronizing the data next time you connect makes it easier for busy users to work disconnected, for example, during the Great Blackout of 2003.

A couple dozen new and powerful statistical functions have been added to Excel, in conjunction, with side-by-side workbook comparison, greater reference information, and support for linking to wireless devices like Tablet PCs will offer you and your users with a more productive total user experience.

What This Book Covers

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language.

The pages that follow have been divided into three sections:

• Programming
• Advanced features for Excel
• New features that facilitate online information sharing

And, a comprehensive, updated object model reference has been included in Appendixes A, B, and C.

This book has been reorganized, moving almost all of the programming information upfront. In addition, new chapters have been added with a greater emphasis on object-oriented programming, error handling, and writing bulletproof code. In these chapters you will learn about everything, from encapsulation, interfaces, error handling and debugging, to writing AddIns, programming to the Windows API, and managing international issues.

The middle section of the book describes new and advanced features for Excel users. These features—like new support for ranges and lists—are essential to making the most out of Excel as a productivity and programming tool.

The third part of this book talks about increased resources for sharing Excel data on the Internet.

Because this book is primarily a programmer's reference, the greatest emphasis is on programmers. However, because so many of you like features geared more toward users we left these chapters in and updated them where it was appropriate to do so.

Version Issues

This book was first written for Excel 2000 and has now been extended to Excel 2003 as a component of Office XP. As the changes in the Excel Object Model, compared to Excel 97, have been relatively minor most of this book is applicable to all three versions. Where we discuss a feature that is not supported in previous versions, we make that clear.

What You Need to Use this Book

Nearly everything discussed in this book has examples with it. The entire code is written out and there are plenty of screenshots where they are appropriate. The version of Windows you use is not important. It is important to have a full installation of Excel and, if you want to try the more advanced chapters involving communication between Excel and other Office applications, you will need a full installation of Office. Make sure your installation includes access to the Visual Basic Editor and the VBA Help files. It is possible to exclude these items during the installation process.

Note that Chapters 13 and 14 also require you to have VB6 installed as they cover the topics of COM AddIns and SmartTags.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.

As for styles in the text:

• We highlight important words when we introduce them
• We show keyboard strokes like this: CtrlA
• We show file names, URLs, and code within the text like so: persistence.properties
• We present code in two different ways:

In code examples we highlight new and important code with a gray background.

The gray highlighting is not used for code that's less important in the present context, or has been shown before.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at http://www.wrox.com. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.

To find the errata page for this book, go to http://www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list including links to each book's errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

In Case of a Crisis…

There are number of places you can turn to if you encounter a problem. The best source of information on all aspects of Excel is from your peers. You can find them in a number of newsgroups across the Internet. Try pointing your newsreader to the following site where you will find people willing and able to assist:

• msnews.microsoft.com

Subscribe to microsoft.public.excel.programming or any of the groups that appeal. You can submit questions and generally receive answers within an hour or so.

Stephen Bullen and Rob Bovey maintain very useful Web sites, where you will find a great deal of information and free downloadable files, at the following addresses:

• http://www.bmsltd.co.uk
• http://www.appspro.com

Another useful site is maintained by John Walkenbach at:

• http://www.j-walk.com

Wrox can be contacted directly at:

• http://www.wrox.com—for downloadable source code and support
• http://p2p.wrox.com/list.asp?list=vba_excel—for open Excel VBA discussion

Direct queries can be sent to:

• [email protected]

(Keep in mind that efforts would be made to answer all queries but you may find yourself in a line behind others. It is a good idea to post your query to several sources at once to get the fastest help and a diverse set of responses to choose from.)

Other useful Microsoft information sources can be found at:

• http://www.microsoft.com/office/—for up-to-the-minute news and support
• http://msdn.microsoft.com/office/—for developer news and good articles about how to work with Microsoft products
• http://www.microsoft.com/technet—for Microsoft Knowledge Base articles, security information, and a bevy of other more admin-related items

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.
2. Read the terms of use and click Agree.
3. Complete the required information to join as well as any optional information you wish to provide and click Submit.
4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.