Microsoft Excel 2000 Power Programming with VBA

Microsoft Excel 2000 Power Programming with VBA

2.7 4
by John Walkenbach, Baird

With the latest version of Visual Basic for Applications and the new Office Component Object Model, Excel 2000 now provides an amazing platform for the development of custom spreadsheets and turnkey applications for a wide range of business needs. With the expert guidance of John Walkenbach, a leading Excel expert better known as "Mr. Spreadsheet," you'll quickly


With the latest version of Visual Basic for Applications and the new Office Component Object Model, Excel 2000 now provides an amazing platform for the development of custom spreadsheets and turnkey applications for a wide range of business needs. With the expert guidance of John Walkenbach, a leading Excel expert better known as "Mr. Spreadsheet," you'll quickly learn how to harness the full programming power of Excel 2000 — from UserForms to class modules.

Product Details

Publication date:
Product dimensions:
7.44(w) x 9.22(h) x 2.10(d)

Read an Excerpt

Chapter 1: Excel 2000: Where It Came From

To fully appreciate the application development features available in Excel 2000, it's important to understand where this product came from and how it fits into the overall scheme of things. If you've worked with personal computers over the past decade, this information may be old hat. If you're a trivia buff, this chapter is a gold mine. Study this chapter, and you'll be a hit at the next computer geek party you attend.

A Brief History of Spreadsheets

Spreadsheets are a huge business, but most of us tend to take this software for granted. In fact, it may be hard to fathom, but there really was a time when spreadsheets were not available. Back then, people relied instead on clumsy mainframes or calculators and spent hours doing what now takes minutes.

It all started with VisiCalc

The world's first electronic spreadsheet, VisiCalc, was conjured up by Dan Bricklin and Bob Frankston back in 1978, when personal computers were unheard of in the office environment. VisiCalc was written for the Apple 11 computer, an interesting little machine that is something of a toy by today's standards. But in its day, the Apple 11 kept me mesmerized for days at a time. VisiCalc essentially laid the foundation for future spreadsheets, and its row-and-column-based layout and formula syntax are still found in modern spreadsheet products. VisiCalc caught on quickly, and many forward-looking companies purchased the Apple 11 for the sole purpose of developing their budgets with VisiCalc. Consequently, VisiCalc is often credited for much of the Apple II's initial success.

In the meantime, another class of personal computers was evolving; these PCs ran the CP/M operating system. A company called Sorcim developed SuperCalc, a spreadsheet that also attracted a legion of followers.

When the IBM PC arrived on the scene in 1981, legitimizing personal computers, VisiCorp wasted no time porting VisiCalc to this new hardware environment. Sorcim soon followed with a PC version of SuperCalc.

By today's standards, both VisiCalc and SuperCalc were extremely crude. For example, text entered into a cell could not extend beyond the cell--a lengthy title had to be entered into multiple cells. Nevertheless, the capability to automate the budgeting tedium was enough to lure thousands of accountants from paper ledger sheets to floppy disks.

Lotus 1-2-3

Envious of VisiCalc's success, a small group of computer freaks at a startup company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathan Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. I remember seeing a large display ad for 1-2-3 in the Wall Street Journal--the first time that I'd ever seen software advertised in a general interest publication. Released in January 1983, Lotus Development Corporation's 1-2-3 was an instant success. Despite its $495 price tag (yes, people really paid that much for software), it quickly outsold VisiCalc, rocketing to the top of the sales charts, where it remained for many years--it was, perhaps, the most popular application ever.

Lotus 1-2-3 not only improved on all the basics embodied in VisiCalc and SuperCalc, but also was the first program to take advantage of the new and unique features found in the powerful 16-bit IBM PC AT. For example, 1-2-3 bypassed the slower DOS calls and wrote text directly to display memory, giving it a snappy and responsive feel that was unusual for the time. The online help system was a breakthrough, and the ingenious "moving bar" menu style set the standard for many years. One feature that really set 1-2-3 apart, though, was its macro capability, a powerful tool that enabled spreadsheet users to record their keystrokes to automate many procedures. When such a macro was "played," the original keystrokes were sent to the application--a far cry from today's macro capability but definitely a step in the right direction.

1-2-3 was not the first integrated package, but it was the first successful one. It combined (1) a powerful electronic spreadsheet with (2) elementary graphics and (3) some limited but handy database features. Easy as 1, 2, 3--get it?

Lotus followed up the original 1-2-3 Release 1 with Release 1A in April 1983. This product enjoyed tremendous success and put Lotus in the enviable position of virtually owning the spreadsheet market. In September 1985, Release 1A was replaced by Release 2, a major upgrade that was superseded by the bug-fixed Release 2.01 the following July. Release 2 introduced add-ins, special-purpose programs that can be attached to give an application new features and extend the application's useful life. Release 2 also had improved memory management, more @ functions, four times as many rows as its predecessor, and added support for a math coprocessor. It also enhanced the macro language, whose popularity exceeded the developers' wildest dreams.

Not surprisingly, the success of 1-2-3 spawned many clones--work-alike products that usually offered a few additional features and sold at a much lower price. Among the more notable were Paperback Software's VP Planner series and Mosaic Software's Twin. Lotus eventually took legal action against Paperback Software for copyright infringement (for copying the "look and feel" of 1-2-3); the successful suit essentially put Paperback out of business.

In the summer of 1989, Lotus shipped DOS and OS/2 versions of the long-delayed 1-2-3 Release 3. This product literally added a dimension to the familiar row-and-column-based spreadsheet; it extended the paradigm by adding multiple spreadsheet pages. The idea wasn't really new, however; a relatively obscure product called Boeing Cale originated the 3D spreadsheet concept, and SuperCalc 5 and CubeCalc also incorporated it.

1-2-3 Release 3 offered features that users wanted, features that ultimately became standard fare: multilayered worksheets, the capability to work with multiple files simultaneously, file linking, improved graphics, and direct access to external database files. But it still lacked an important feature that users were begging for: a way to produce high-quality output.

Release 3 began life with a reduced market potential because it required an 80286-based PC and a minimum of 1 MB of RAM -fairly hefty requirements in 1989. But Lotus had an ace up its corporate sleeve. Concurrent with the shipping of Release 3, the company surprised nearly everyone by announcing an upgrade of Release 2.01 (the product materialized a few months later as 1-2-3 Release 2.2). Release 3 was not a replacement for Release 2, as most analysts had expected. Rather, Lotus made the brilliant move of splitting the spreadsheet market into two segments: those with high-end hardware and those with more mundane equipment.

1-2-3 Release 2.2 wasn't a panacea for spreadsheet buffs, but it was a significant improvement. The most important Release 2.2 feature was Allways, an add-in that gave users the ability to churn out attractive reports, complete with multiple typefaces, borders, and shading. In addition, users could view the results on-screen in a WYSIWYG (What You See Is What You Get) manner. Allways didn't, however, let you issue any worksheet commands while you viewed and formatted your work in WYSI-WYG mode. Despite this rather severe limitation, most 1-2-3 users were overjoyed with this new capability, for they could finally produce near-typeset-quality output.

In May 1990, Microsoft released Windows 3.0. As you probably know, this software changed the way people used personal computers. Apparently, the decision makers at Lotus weren't convinced that Windows was a significant product, and the company was slow getting out of the gate with its first Windows spreadsheet, 1-2-3 for Windows, which wasn't introduced until late 1991. Worse, this product was, in short, a dud. It didn't really capitalize on the Windows environment and disappointed many users. Consequently, Excel, which had already established itself as the premier Windows spreadsheet, became the overwhelming Windows spreadsheet market leader. Lotus came back with 1-2-3 Release 4 for Windows in June 1993--a vast improvement over the original. Release 5 for Windows appeared in mid-1994.

In mid-1994, Lotus unveiled 1-2-3 Release 4.0 for DOS. Many analysts (including myself) expected a product more compatible with the Windows product. But we were wrong; DOS Release 4.0 is simply an upgraded version of Release 3.4. Because of the widespread acceptance of Windows, this should be the last DOS version of 1-2-3 to see the light of day.

Over the years, spreadsheets became less important to Lotus (its flagship product turns out to be Notes). In mid-1995, IBM purchased Lotus Development Corporation. Two more versions of 1-2-3 became available, but it seems to be a case of too little, too late. Excel clearly dominates the spreadsheet market.

The most recent versions of 1-2-3 feature LotusScript, a scripting language similar to VBA (see Figure 1-1). Spreadsheet developers haven't exactly embraced this language with open arms. In retrospect, Lotus probably should have licensed VBA from Microsoft...

Meet the Author

About the Author John Walkenbach is a principal of JWalk and Associates Inc., a Southern California-based consulting firm specializing in spreadsheet application development. The top-selling author of two dozen spreadsheet books, he is also the "Spreadsheet Tips" columnist for PC World. He developed the Power Utility Pak, an award-winning utility package for Excel, and maintains the Spreadsheet Page, a popular Internet Web site.

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >

Microsoft Excel 2000 Power Programming with VBA 2.8 out of 5 based on 0 ratings. 4 reviews.
Guest More than 1 year ago
The reviews titled 'disorganized' are exactly on target. Even the most basic information like reading information into or out of the spreadsheet is buried without comment in lines of code. New features are largely missing. The book has value only if you stumble across (because deliberate search is not possible) exactly the code item you want. A far more up-to-date, better orgainized and more authoritative work with a lot less blather is Kofler.
Guest More than 1 year ago
The author guides you through step by step in a very detailed manner but one might lose the big picture at the end of it. I was a beginner with no prior programming experience but I truly find it easy to understand the content.
Guest More than 1 year ago
Although there is a lot of detail in this book, it is hard to find what you want. Some very basic items are buried in lines of code and not even commented upon in the text itself. The book may be worth having as a reference of last resort, but is of little value for learning how to do anything. And the arrangement of information does not make it easy to find stuff.
Guest More than 1 year ago
Although it contains useful odds and ends, this book reads like a compendium of ''answers to readers' questions'' rather than a thought-out presentation of material. Even when you know what you want (rather than prospecting for how to do things) there is no telling where the info is, or even if it's here. My mode of use has been: if I can't find an answer anywhere else, maybe it's here somewhere.