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.
limits (Score:2, Interesting)
Experience helps too (Score:4, Interesting)
Knowing how the technology works is one thing, but knowing how to set up your tables and the data they contain is key. Depending on your use of the spreadsheet, and what you hope to get out of it, there are different ways you can organize worksheets/data.
but what I really want (Score:3, Interesting)
Yes people develop excel, no not with VBA (Score:3, Interesting)
I know, I am doing it now.
It doesn't use macros, or VBA. You can write clean OO code.
Excel - geeky clever stuff (Score:2, Interesting)
Each cell represents a pixel.
Re:Uh, yeah.. (Score:5, Interesting)
Re:Is excel really for development? (Score:2, Interesting)
Excel is a great "Swiss Army Knife" (Score:3, Interesting)
Case in point - I served as a Mormon missionary for two years in South America. Five months of that was in the mission office making sure things ran smoothly (running logistics, renting apartments, materials, mail, etc.). The mission consisted of roughly 200 people.
It didn't take long for me to realize that our office of four people was overstaffed and that two of us could be replaced if we used our technology resources more efficiently. Due to the widely different conditions missions around the world operate in (it's impossible to run 300+ units in more than 100 countries the same way), the church does not provide much in the way of standardized software - missions are left on their own.
I was charged with creating programs to track inventory and missionary statistics (baptisms, contacts, etc.). I COULD have written the whole thing in C or C++ (only languages I knew at the time), but I opted for Excel and Visual Basic for Applications because I knew that it was very unlikely that there would be a steady stream of missionaries in years to come that could code in C or modify things if necessary. But, I was able to teach myself enough VBA in 24 hours to create programs that could do everything I wanted them to do, and they're still used today. They also turned out to be flexible enough to be used elsewhere - I hear that four other missions are using variants of the software I created almost 3 years ago. In the end, our office staff was cut down to two, and even those without great technical knowledge can be used to fill the jobs.
Excel really is like a Swiss Army Knife - not really the best at anything, but versatile and easy enough for anybody to use.
Re:SQL? (Score:5, Interesting)
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!
Re: Professional Excel Development (Score:3, Interesting)
The reason I use excel is because I can do just about anything I want (using a nice library of API function wrappers I've written) and it doesn't cost anything - they won't buy me a full copy of VB6 so I can get away from the stupidity of using a spreadsheet to 'host' an application... Not that I'm a big fan of Access but I use that in preference to excel now for one reason - you can pass parameters using the cli. Excel is very weird from this p.o.v - you can sort of pass a parameter from the cli but only if it's exactly 4 chars! At least that's what I found with excel97 and I haven't bothered checking since I found that Access is more useful - well if you don't mind having to parse your command line parameters in a macro (not VBA) and then calling a VBA function to handle the paramters. I didn't say it wasn't ugly, I just said it worked.
I just wish they'd let me use perl or Java/JNI - I'd give common lisp a shot too if I could work out how to access the win32 api. .Notice that they're all free - I know I won't get a budget.
Excel's relevance to the real world. (Score:2, Interesting)
Where I work we have a team of ten people working with Excel directly supporting about double that number of traders (more than that if you look at our strategic projects). Every other software development group in our firm has more developers than traders paying for developers. We use Excel because it provides a UI that is unrivaled (and because of its rather good - but still not quite good enough for us - recalc engine). We're not idiots, we are the type of people who run multiple boxes and OSes at home, and our Excel development projects are almost psychotic in the way they push the boundaries of what is possible. We started with Life, then Tetris, then someone had to go and write an non-VBA Excel raytracer(!); more seriously we have written a macro meta-language for Excel and sophisticated profiling tools - as a firm we run distributed computing out of Excel, and I've worked with a single spreadsheet (dev time about 4 man months) that did everything (and then some) that an entire system (dev time 6 man years) did at a fund management firm I had worked at previously. That's what is possible with Excel and why it remains a popular choice in business.
It's easy to be really very bad in Excel whilst thinking you're an expert. I've lost count of the number of people I've interviewed who call themselves experts but have no idea about what a volatile function is, or how to use maps/dictionaries in Excel VBA (if you don't know these, and think you're good - you're not!). You need to be a good coder, good designer, and have an appreciation for user interface design in order to be of any real use - just like when using a "real" language, except possibly more so, because it's so easy to do things really wrong in Excel. In short, don't underestimate the value of books like these.
Best regards,
Daniel
Re: Professional Excel Development (Score:3, Interesting)
This is only so true . . . this is also why I believe OpenOffice has the potential to really improve productivity in this area (drag 'n drop database creation integrated into the spreadsheet).
Re:Excel is an OUTPUT format (Score:2, Interesting)
Access would cost a load to install on all the machines where as they already have Excel. Due to this we're creating the questionnaires in Excel which each member of staff can access and make changes to. The Access database then grabs the data from each completed questionnaire where statistical reports are generated.
I hate Excel but it does have it's uses. I especially wish there were less bugs in it, for example: Try inserting an Active-X combo box in to an Excel 2000 worksheet and assign its ListFillRange to assign its options - then save the file, reopen and try to close it again and you always get prompted "do you wish to save?" even if you make no changes - even setting
Haydn.
Re: Professional Excel Development (Score:3, Interesting)
I can too, with PKZip.
You've got a point, but my boss has a Project Management System written in Access 2000 by a friend of his, and it is my opinion that it would be easier to rewrite the app with Visual C++ rather than make the changes and improvements we need. It would take a little longer up front, but save huge amounts of time in the long run. Of course, I'm far from an expert in Access, but it's a big hassle to use and for something written in 2000, it looks like a state-of-the-art GUI from say, 1990.
As with any tool by Microsoft (including MFC), if you stick to the narrow domain of functionality it supports, it's an easy too to generate a GUI, but the moment you want to do something a little off the path, you'll find yourself completely lost in the weeds.