Professional Excel Development 318
| 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.
excel?! (Score:2, Insightful)
if you think something will grow beyond a triviality a database is a much better idea
What the hell is going on today? (Score:1, Insightful)
This is throwing my entire perception of reality into question.
Re: Professional Excel Development (Score:3, Insightful)
Put another way, I don't do accounting in Python - why would I want to write applications in Excel? Spreadsheets are the right tool for quite a few jobs, but this isn't one of them.
Re: Professional Excel Development (Score:5, Insightful)
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 [plala.or.jp]...
all hail the ignorant masses! (Score:4, Insightful)
"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#.
I know where they use excel (Score:4, Insightful)
Re: Professional Excel Development (Score:5, Insightful)
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:Why are we encouraging this? (Score:1, Insightful)
Re: Professional Excel Development (Score:4, Insightful)
I'll give you that. You can do some cool stuff with Excel.
But why use it as essentially an application runtime when you could be using
If the advantage is supposed to be that it gives you a basis for storing data, my response would be that spreadsheets are not databases (as much as office workers like to pretend they are), and trusting production data to an XLS or CSV file is unwise.
The idea of developing "professional" application in Excel seems to me like ricing out a base model Civic and pretending it's a real race car. You can do it, and the Civic will go pretty fast, but why not get an actual sports car instead of putting a ton of time and effort into working around the limitations that come from starting with a family vehicle?
Years ago I worked at an internal corporate help desk, and I saw way too many people spending way too much time doing wack-ass shit in Excel when they could have used Access and done it all with a single SELECT statement.
This is even more extreme: developers tying themselves to what is not only a proprietary platform, but one that will almost certainly break their app as soon as MS releases the next version.
Re:Programming (Score:4, Insightful)
Malarkey. I started programming in Tandy BASIC in the early 1980's, and it didn't instill in me any bad habits that I was unable to shake once I moved on to Pascal, C, C++, Perl, Java, etc. "GOTO Considered Harmful"? In modern high level languages, yes, we have more elegant syntaxes for branching. But in assembly, what is a JMP instruction anyway but a GOTO?
Visual Basic, I have no experience with and do not wish to gain any.
Re:Uh, yeah.. (Score:5, Insightful)
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.
Productivity with VBA(R) scripting (Score:2, Insightful)
With some Excel(R) scripting, you can create sophisticated applications that really boost your productivity.
Here are some programs I use daily:
Re:all hail the ignorant masses! (Score:3, Insightful)
Umm no. The financial sector uses mainframes, minicomputers, serious database applications... and in many cases even the clients are on OS/2. You may see excel on the desktop of some mid-level folks in the sector, but claiming that 'the entire financial sector bases it's existance[sic] on Excel' is ludicrous in the extreme.
The stereotype/gratuitous insult about 'smelly nerds' just adds confirmation - you haven't a clue what you're talking about.
Re:This better be for Office 2003 (Score:3, Insightful)
My advice is not to build any huge applications in Pffice, as sooner or later your versions won't be up-to-date, and porting this kind of thing between different versions of Office has been a huge and messy exercise in the past, and there is no guarantee it won't be in the future. There is a place for large amounts of code, and it is not tied to Office-type calculations. It should be in libraries which can then be accessed by any application. This is why Windows allows COM/ActiveX development.
He picked the wrong title (Score:5, Insightful)
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: Professional Excel Development (Score:4, Insightful)
The client is willing to sign a cheque.
CC.
Hmmm... (Score:3, Insightful)
I would think you'd be happy at seeing someone try to help these people build manageable Excel models and simple Excel apps. Let's face it:
1) They will continue to do this, it's fundamental to doing their job. If they need to analyze something quickly, they can't wait for a bunch of quiche-eating Java weenies to argue about the object model for the next six months.
2) There's nothing you can do to stop them.
3) Really. Nothing.
4) So you might as well help them to do what they do, only do it better.
Reminds me of a Steven Seagal movie:
"Well, if I can't control you, I might as well use you, right, son?"
"That's affirmative, sir."
But that's probably too practical a viewpoint for this forum.
Re:Programming? (Score:2, Insightful)
excel separates geeks from non-geeks (Score:3, Insightful)
The mechanical engineers where I work for starters. Drives me nuts the ridiculous things they do with Excel when they should be using a programming language or something like Matlab..
You see, because they don't know any better, they assume that there aren't any better ways of doing ANY calculation or programming task other than writing an excel macro. And so they create all kinds of mind-numbingly complicated spreadsheets doing things like Simpson's rule integration and finite-element modelling, and are proud of them.
These guys are not stupid; they just have a massively greater tolerance for tedious & repetitive tasks than your average geek. In fact it's one of the main things that differentiaites geeks from non geeks, almost a sort of laziness, that means we would rather spend a day learning how to automate something with the proper tools, in a reusable fashion, than one hour doing it by hand each time.
Re:limits (Score:3, Insightful)
Re: Professional Excel Development (Score:3, Insightful)
Excel is a very good tool (unlike Access which has no redeeming qualities), but based on my experience, any time you try to develop an application on anything but a real application development system, your app will be difficult to create, difficult to debug and look like crap.
This includes Excel, Access, and as far as the looking like crap part, Java.
Re: Professional Excel Development (Score:5, Insightful)
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.
Programming is too hard... (Score:4, Insightful)
Excel is not a DB (Score:3, Insightful)
If you do need to perform calculations on a dataset this large then either use a true database for storage and do the calculating in the client code or get a specialist tool like Matlab that can be used for numeric analysis.
Re: Professional Excel Development (Score:4, Insightful)
Regards
Stephen Bullen
Re: Professional Excel Development (Score:3, Insightful)
What... can't hack it?
Let's put it this way. As an analyst (someone else here said that too), Excel is an indespensible part of my job. A lof of work I do is tedious and repititive. So, I write small macros to automate some of those tasks. The next thing I know, I've put a front-end on it and made an application out of it.
I can't re-write it in some other development system because:
1) I'm an analyst. And while I can program pretty well in a variety of languages, there is no budgetary justification for buying an IDE in some other language system
2) Excel with VBA gets the job done quickly and well-enough
3) I only intend to stay in this job for a year or two. The next person can use my app or leave it... but either way, they still have to manipulate the data on the spreadsheet.
4) Ultimately, (tied to #3), it's the data and not the application that's important. Other people need to use the data in the spreadsheet, and I'll be damned if I waste a lot of time writing an application that inticipates everything they'll want to do with the data. They know how to use Excel to manipulate it they way they want.
Uncommented coding in any language is a hardship, but it's also not exclusive to Excel and VBA. Lack of comments and poor programming practices are a symptom of a bad programmer, not a bad environment. And to be truthful, the VBA portion of Excel does, in fact, provide a decent development environment for a small-scale project. There are all kinds of facilities for structured programming - comments, functions/procedures, variables with scope and visbility.
Really, it's not so bad. As somone else said, Excel is probably one of Microsoft's best products.