Professional Excel Development

r3lody (Raymond Lodato) writes "Over the years, I've read a number of books on Excel programming. Each one seemed much like the previous one, generally talking about writing macros and creating data-entry forms. Professional Excel Development takes the concept quite a bit farther. Rather than giving you the same old tired lessons, this book goes into detail on exactly how to build professional level applications. It even explains how to make your Excel-based application look as though Excel had nothing to do with it. Suffice it to say, this ain't your daddy's Excel book." Read on for the rest of Lodato's review. Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA author Stephen Bullen, Rob Bovey, John Green pages 936 publisher Addison-Wesley Professional rating 10/10 reviewer Raymond Lodato (rlodato AT yahoo DOT com) ISBN 0321262506 summary A remarkably detailed 'how-to' book on creating complete applications using Excel as a base.

The authors, Stephen Bullen, Rob Bovey, and John Green, show a level of sophistication well beyond the norm. They'd rather teach you the proper way to program instead of teaching you how to use Excel. In fact, the first thing they do is distinguish five different levels of usage: Excel users, Excel power users, VBA developers, Excel developers, and professional Excel developers. The book is written for the highest level, so expect a lot of depth.

Rather than simply show how to record a macro and reuse it, they start by talking about coding practices, naming conventions and application structure. That's followed by an entire chapter on worksheet design, including names, styles, validation, formatting and controls. After a chapter on add-ins, they launch into the topic of dictator applications, that is, applications that completely take over the Excel interface and look like a regular, non-Excel program.

The following chapters go into much more detail about wringing every ounce of functionality from Excel, and then turning to the operating system and Visual Basic for more help. After discussing data manipulation with databases, they talk about using XLLs and the C API, VB.NET, and writing Help files to complete the application. The entire structure of the book builds around a time-entry application that is developed from a simple spreadsheet to a full-blown, production quality program. A CD-ROM is also included with all of the source code and multiple examples that are scattered throughout the book.

Reading Professional Excel Development is not something to be taken lightly. The authors have done a fine job putting together a cohesive methodology for using Excel as an application development platform. I know of no other book that covers this platform in such depth. At times I found myself lost in the details, but I suspect a "professional Excel developer" (which I am not) would be delighted in the depth of description and copious examples provided.

I tried to relate a lot of what Stephen, Rob, and John discussed to OpenOffice Calc, to see if it could be ported to an open source environment. I was surprised by how much actually came across. Granted, items in OpenOffice are sometimes in different places, or named differently, than their counterparts in Excel, but most of the same functionality is there. Unfortunately, most of the examples are written in VBA, which doesn't translate cleanly into OpenOffice. Still, with perseverance, you would probably be able to develop most of what is described in the book.

Professional Excel Development is an extremely well-written book that covers the use of Excel to a depth few authors have dared to tread. The text gives you the tools to build applications that are much more than automated spreadsheets. Almost any program your imagination can devise can be created using the techniques given, which is a testimony to the power of Excel. Bash Microsoft if you want, but they do sometimes come up with a winner, and Professional Excel Development allows you to take full advantage of its capabilities.

You can purchase Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Comments

Professional Excel Development

[ShaniaTwain]

Isn't this like painting a house with tomato paste?

you could do it, but why?

Re: Professional Excel Development

[Otter]

Excel is a fantastically powerful, flexible tool, and also has a portability advantage when working with people who have Office installed, but not Perl or Python (i.e. pretty much everyone). The fact that people here don't know how to do anything more complicated than adding column A to column B doesn't change that.

Now, if they'd only let me use a Mac at work, instead of making me run Excel on this hideous OS...

Obligatory link to the god of Excel programming...

Re: Professional Excel Development

[Anonymous Coward]

If you are using spreadsheets to do your accounting it sounds like maybe you still need to work on this "right tool for the job" thing (unless you've just written a fantastic double-entry system in VB for Excel).

And I can think of lots of reasons to write applications in Excel. The best one being that you probably already have it on your machine (no need to purchase a development environment). Another good one being that probably most of your users will have the "platform" to run your application. What else? How about an insanely good function library, including some amazing graphing tools?

I try to stay away from MS in general, but you can do a lot worse than developing in Excel.

Re: Professional Excel Development

[slantyyz]

A lot of people don't realize that Excel is sometimes a political choice for software development.

The very fact that Excel is considered a desktop application allows departments to get away (not necessarily rightly so) with creating applications in Excel without having to go through as many approval channels.

The fact that Excel has an IDE that lets you do many of the things you can do in VB allows you to do sophisticated application programming that can slip under the nose of some bureaucrats.

I spent a couple of years doing Excel programming after doing "Enterprise" development. It paid the same as my previous work, and for my client, Excel was the platform of choice, simply because the development effort didn't require the involvement of the IT department and its associated red tape. Once I got accustomed to the fact that I could do anything that I could do in a normal development platform, I quickly got over the fact that I was doing Excel work. I used to snicker at the thought of doing Excel and Access work before. But business logic is business logic, so I guess it doesn't really matter what platform you're working on.

Obviously Excel isn't the programmer's first choice, but it is a capable platform. The business reasons for using Excel as a development platform may be misguided, but it doesn't mean you shouldn't use the same practices that you would use to write anything in any other dev platform. I would think that the goal of the reviewed book is to ensure that.

Re: Professional Excel Development

[Stephen Bullen]

That's exactly what drove us to write the book. Over and over again, we've been called in to fix such Excel applications. Rather than say "Don't do that", we recognised that a great many people *do* develop business-critical applications using Excel, but don't have the toolset and mindset to write code that is robust, maintainable, etc. This book attempts to teach much of the discipline and techniques that that we'd expect in 'proper' development environments.

Regards

Stephen Bullen

Re: Professional Excel Development

[GCP]

When did Excel become a development environment?

It became a development environment in version 1.0.

Can I use Excel to compile my C++, Java and C# apps?

It sounds as though you're confused about what software development really is.

None of the Lisp or Scheme dev systems I use are capable of compiling your C++, Java, and C# apps. Likewise for my copy of Mathematica. Or the IBM mainframe I started out with or my current favorite Python/IDE combo. It sounds as though you don't realize that software development is a lot broader, with a much wider diversity of styles and tools, than your limited experience has acquainted you with.

Excel is analogous to a scripting language in some ways, with easy scripting of powerful built-in functionality but certain semantics are represented visually instead of textually.

It is also a functional programming platform that those with experience in functional programming immediately recognize and know how to exploit to great advantage. This is not an accident. Early spreadsheet engine designers were more familiar with functional paradigms than are most "C++, Java, and C#" programmers these days, so today's newbie programmers often don't even recognize the nature of the platform they're looking at.

Excel is not a "platform". Excel is an application that you can control through Automation.

Sure, sure. And Oracle isn't a platform, is it? Or Mathematica, or Matlab, etc. They're just big applications that you control through automation.

Ah, I can tell you have limited development experience with _real_ applications that don't use Excel or Access as a "back-end".

Oh, the irony is rich....

Of course Excel is a platform, and an excellent one for certain types of work. I've used it for sophisticated and flexible financial modeling, nuclear effects data analysis, and genealogical data organization, among other things.

I do agree with you about the quality of the built-in functions, though. I think the poster you were responding to may not realize how poorly implemented some of those built in functions are, from a numerical methods perspective. I tend to write my own implementations, using Excel primitives, instead of using Excel's fancier functions (e.g., random num generation, internal rate of return, etc.), and there are times when I'll prototype in Excel but end up doing the production implementation partially in C. The same can be said for Python, though. It doesn't mean that Excel and Python aren't real platforms.

Professional Excel Development?

[Shant3030]

Sounds like an oxymoron to me.

Programming?

[daeley]

My daddy didn't need Excel to make grocery lists, nor PowerPoint to give a speech, nor Word to send a letter.

My daddy also called "spreadsheet programming" "math" and did it in his head or with a slide rule. ;D

all hail the ignorant masses!

[Jailbrekr]

To quote the first two:

"Professional Excel Development? Isn't that an oxymoron?"

"Professional Excel Development is like painting a house with tomatoe paste"

The entire financial sector bases its existance on Excel. Brokers, bankers, and virtually every banking or stock related position has a deep dependance on Excel. Laugh all you like at the thought of a professional excel developer, but I highly doubt that these firms will simply drop Excel in favour of some smelly nerds coding in C#.

Re:Uh, yeah..

[ScentCone]

But it's certainly possible to approach the range of things that Excel can do (which are substantial, especially when you use ODBC, etc., to patch into huge databases, etc) with a professional set of skills and business sensibilities.

A well-conceived Excel file can carry with it everything you need for a pretty elaborate bunch of data crunching and presentation, and that can hop from machine to machine very easily. Sales people in the field working up quotes, or managers chewing on inventory info before making a buying decision at a meeting - certainly they could just "use" a spreadsheet to do all of that, but having a purpose-specific UI sitting on top of it (without having to drag around other runtimes, etc) can really help when you're dealing with non-power-users.

"Professional level" can also refer to presentation sensibilities. For example, wise use of font families and graphs can make the tool's output more useful in a wider variety of settings. A pro knows how to wrap it all up in a smooth package, even if some of the logic is very simple. But a huge, complex, what-if business plan package friendly to investor-type users... that's a very cool type of app, with Excel running under the hood.

Re:Uh, yeah..

[ALeavitt]

I'll bite: I'm masochistic enough to write complex apps in Excel. I'm pretty well-versed in Visual Basic, having used it since the 3.1 days, and I work in your typical cube farm. Everyone here "knows how to use Excel" but they often forget how to do those really complex things, like summing a column, or cutting and pasting. It got to the point where I was sick of dealing with inane help calls, so I started programming. Originally it was just going to be a few buttons to make everyone's life easier, but the project ballooned into a number of full-fledged programs that sit on top of Excel and hold the idiot-users' hands. I didn't initially set out to write a complex app in Excel, but I ended up doing it because it was the only way that many of the simpletons would accept and use the program... or even know how to run it.

He picked the wrong title

[InstBrad]

Had the book been called "Professional OpenOffice Development", this crowd would have fallen all over themselves praising it.

It gets real tiring listening to all the folks that couldn't say a nice word about a MS product if you paid them. Guess what folks... there are millions and millions of people that use Excel in very sophisticated ways. Why? Because it works well enough. With the help of this book, maybe it will work even better.

There are many ways to skin a cat. You haven't got a lock on God's One True Programming Language.

Re:SQL?

[Lemmeoutada Collecti]

I've been reading all the whines about this language or that language. Here is a challenge for you:

1) You have the following data sources: Oracle, Access, CSV Files, Microsoft SQL, and IBM DBase
2) You need to generate a set of reports for a management group (more than three PHB's involved)
3) One of these PHB's is the one who approves your paychecks
4) You have at your disposal the following tools:A full development environment with IDE for C++, 5) Visual Basic, Java, and (X)HTML, Microsoft Office Pro XP
5) The report has to be viewable by anyone at the company, all systems will have Office installed
6) The workstations are locked down, and installing is not an option on the user end
7) Some data will need to be gathered from other departments to complete the reports
8) The reports must be in a form that can be put into a Powerpoint, because the PHB says so
9) It is not 6:30 a.m. ***The meeting starts at 10:00 a.m.*** THIS MUST BE DONE IN TIME!

What tool and approach would you use?

I don't know about you, but I find that pumping data into Excel from Access using a bunch of linked tables and queries and doing the calculations and presentation in Excel to be a rapid way to keep receiving my paycheck. As much as I would love to have time to develop fully tested, object oriented, language of the day applications, I rarely have that option. Once I do develop something, it will change tomorrow. I am a data analyst. My job is not to build applications, it is to crunch numbers as rapidly as possible, analyze them, and explain in plain English what those numbers mean (sometimes in PHB English).

I am going to use the tools that help me accomplish my job most effectively. Given the constraints, none of the programming languages to date allow me to do the analysis as rapidly as Excel. And yes, I do write Excel code, macros, and complex formulas. I also reuse the same ones again and again. They are objects in my environment.

So if you have the time to leisurely tell your PHB that sure, the report he needs in three hours will take you six months to develop a solution for, we can certainly trade jobs!