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 dimensions:||7.44(w) x 9.22(h) x 2.10(d)|
About 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.
Read an Excerpt
Chapter 1: Excel 2000: Where It Came FromTo 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 SpreadsheetsSpreadsheets 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 VisiCalcThe 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-3Envious 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...
Table of Contents
PART I: Some Essential Background.
Chapter 1: Excel 2000: Where It Came From.
Chapter 2: Excel in a Nutshell.
Chapter 3: Formula Tricks and Techniques.
Chapter 4: Understanding Excel's Files.
PART II: Excel Application Development.
Chapter 5: What is a Spreadsheet Application?
Chapter 6: Essentials of Spreadsheet Application Development.
PART III: Understanding Visual Basic for Applications.
Chapter 7: Introducing Visual Basic for Applications.
Chapter 8: VBA Programming Fundamentals.
Chapter 9: Working with VBA Sub Procedures.
Chapter 10: Creating Function Procedures.
Chapter 11: VBA Programming Examples and Techniques.
PART IV: Working with UserForms.
Chapter 12: Introducing UserForms.
Chapter 13: UserForm Examples.
Chapter 14: Advanced UserForm Techniques.
PART V: Advanced Programming Techniques.
Chapter 15: Developing Excel Utilities with VBA.
Chapter 16: Working with Pivot Tables.
Chapter 17: Working with Charts.
Chapter 18: Understanding Excel's Events.
Chapter 19: Interacting with Other Applications.
Chapter 20: Creating and Using Add-Ins.
PART VI: Developing Applications.
Chapter 21: Creating Custom Toolbars.
Chapter 22: Creating Custom Menus.
Chapter 23: Providing Help for Your Applications.
Chapter 24: Developing User-Oriented Applications.
PART VII: Other Topics.
Chapter 25: Compatibility Issues.
Chapter 26: Reading and Writing Text Files.
Chapter 27: Manipulating Visual Basic Components.
Chapter 28: Understanding Class Modules.
Chapter 29: Frequently Asked Questions about Excel Programming.
Appendix A: Excel Resources Online.
Appendix B: VBA Statements and Functions Reference.
Appendix C: VBA Error Codes.
Appendix D: ANSI Code Reference.
Appendix E: What's on the CD-ROM.
End-User License Agreement.
CD-ROM Installation Instructions.
Experience tells me that many spreadsheet users spend much of their time creating spreadsheets that other people use. Increasingly, spreadsheets designed with products such as Excel, 1-2-3, and Quattro Pro are suitable for tasks that once required custom programs written in traditional programming languages. Thanks to their macro and customization features, these products now can serve as development platforms for many types of applications. In this book, I cover every aspect of application development using Excel.
Why I wrote this Book
Over the past 15 years, I've written scores of spreadsheet reviews for the leading trade magazines. During this time, I've seen spreadsheets evolve from simple accounting worksheets to incredibly powerful applications. Microsoft Excel goes well beyond what most people consider the realm of spreadsheet software. I am particularly impressed with Excel's capabilities in the area of application development, especially development using the Visual Basic for Applications (VBA) macro language.
Quite a few advanced Excel books are available, but this book is still the only that deals with application development from a larger perspective. VBA is just one component (albeit a fairly large component) of application development. Excel is an extremely deep software product: It has many interesting features that lurk in the background,unbeknownst to the typical user. And you can use some of the well-known features in novel ways.
Millions of people throughout the world use Excel. I monitor spreadsheet-related newsgroups on the Internet, and it's very clear to me that people need (and want) help in the areas that this book covers. My guess is that only five percent of Excel users really understand what the product is capable of. In this book, I attempt to nudge you into that elite company. Are you up to it?
Most Helpful Customer Reviews
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.
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.
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.
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.