Slashdot Mirror
Professional Excel Development
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.
My advice is to not build huge applications in Office unless you have absolutely up-to-date versions. There are certain points in Office 97 / 2000 where you get to a critical load area of your code and suddenly die. Function calls are in the help but don't quite work properly. Old products aren't supported. I'm buying this book because I need it, but building an app with older tools - something many office users are relegated to - is not that great an idea.
Note: this could all change in Office 2003.
My little site.
Heaven forbid any of the traders in the investment bank I work in get a hold of a copy of this. It's bad enough as it is trying to get them to move over to the manageable, scalable applications we build for them without encouraging them to build more spreadsheet solutions.
Powered by onion juice.
Nope.. It's still there in Office 2003
I personally work for an S&P 500 company whose primary role is as a bank, and providing financial services to other companies.
I whole-heartedly agree that excel is used a lot when it comes to sharing data amongst colleagues or when providing reports.
But the serious number crunching, the real work and administration is done mostly with Mainframes and Oracle databases, which are accessed by Delphi, J2EE, and proprietary C++ apps.
Not that my experiences invalidate your assertion. I just figured I would toss in my own anectdote.
Gailin
I wish there was a fscking blue pill
Yeah, God forbid you should bother to read all the way to the end of that sentence.
Excel is more portable than a Perl script in the sense that nobody (to a reasonable approximation of nobody) has Perl installed, or would know what to do with it if they had it. (Yes, I'm sure some way to generate freestanding, cross-platform Perl executables exists. I was about to tell flamers not to bother, but, come to think of it, I'd love to see a link.)
What I'm listening to now on Pandora...
Yup, people do it, you just can't stop them.
Last month I worked on a solution that automated the production of analysis spreadsheets that had previously taken 1 person 20+ hours a week to enter data into. We're talking monsters that have 30000+ formulas in them, you have no choice but to turn automatic recalculation off. They're now done automatically and waiting for them when the users come in now.
What did the customer do? Grin from ear to ear, and start building more spreadsheets that will need to be automated... Number crunchers are funny people, but they sign the checks.
This book is $32.99 on the Amazon site - save yourself $17 and get it there instead. Here's the link (no value add for myself)
https://comerford.net
Actually those limits are per sheet. A workbook can have (I think) 256 sheets, so the total is 65000 * 256 * 256. That said, for some applications excel is a reasonable compromise. Use the built-in functions to let people enter and manipulate the data, add a few macros to do some magic and you've got a slick application in a hurry. Users trust excel and feel comfortable with the spreadsheet look. Duplicating the look and feel with some other application is a lot of work for not much benefit. k
Are you serious?
bFinance house:
Back office: Provide accounting and valuation methods. Typically work off a c-tree database system. Little reason to reinvent the wheel in this role, the scope of back office has been covered by 20 years+ by existing systems.
Middle office: Provide performance and risk analysis. This may be abstract benchmarking to defined verification of back office. Work tends to be more 'ad-hoc' than back office, may use a variety of systems, often likely to include linking to a (several) 3rd party databases and importing that data to a flexible ad-hoc environmeny for analytical manipulation: Excel VBA + 3rd party database APIs are an ideal fit.
Front office: Make the investment decisions. Based of a variety of models. Usually involves quant. I question: What can a mainframe do that a 3GHz P4 with data/filtered data loaded in memory can't do? 5-10 years it was a different question. Desktops today, with the investment manager using Excel, Matlab, SAS, whatever, are able to do what they want. Manipulating 5 million database entries takes seconds with decent code. What is a mainframe needed for other than promote incredably inefficiient databse lookups?
The serious database apps used tend not to be that serious. Back office do things on 30 year old tech which is audited and trusted. Front office use constantly changing models hacked together with whatever whim the imvestment mnager has, middle office tie in front to back office.
There is no fantastic use of mainframes or serious databse applications. Analytical databases used tend to be 3rd party (Bloomberg, Datastream, Reuters). The only serious in-house database I have experience with was set up by the IT department: 1 million ish records, 3 years late, 2 million USD over budget. That's a joke. A modern desktop can perform a huge amount of calculations on the fly. A good aspect of the increase in calculation power these days has led to the use of computing power as a commodity and people are paying much more attention to the specification and interpretation of financial models. Screw mainframes, they're defunct, cheap computer power fuels interpretation/analytics of stats.
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. Exactly that. And realising that in some cases, the business reasons are quite valid too, such as your application having an almost entirely user-configurable UI.
Firstly, thanks for the review!
Lots of the comments here have been about whether or not it is 'right' to develop applications based around Excel. As one of the authors of this book, I think that misses the point. The simple fact is that in the real world, there are lots of people who do develop such applications and really need to know some techniques that can make their programs much more robust, maintainable, etc.
When teaching our children about the facts of life, we can lecture them about abstinence, let them know about lifestyle choices that might or might not be relevant, or we can teach them how to be prepared (physically and emotionally), safe, considerate and responsible. The latter is the attitude we've taken towards Excel development, while also teaching some advanced techniques that might help their activities.
FWIW, more information about the book and a few sample chapters are available from my web site at http://www.oaltd.co.uk/ProExcelDev.
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.
"Those who have never entered upon scientific pursuits know not a tithe of the poetry by which they are surrounded."
Part of my confusion about the premise for this book is (like I said) what it's supposed to buy you.
A great many applications start in Excel for no better reason than that's what's on the desk of the business user (i.e. not a trained IT developer) starting out recording and writing VBA code to make their main job easier. As their knowledge of VBA increases, they do more and more complex and involved things, but typically don't think about many of the topics 'professional' developers would consider - such as whether the solution is scalable, maintainable, robust, secure, etc.
It's not until people realise that their application has become business-critical that they start to think about the risks, and might even ask the IT Dept to help in managing it. The first time IT see the application, it's a mess of unstructured, uncommented, undocumented code.
The premise for the book, then, is to try to get those 'developers' to think about the bigger issues early on, and provide the techniques and mindset for them to approach their development in a manner that will (hopefully) result in their Excel-based applications being much higher quality and easier for IT to adopt, if that need arises.
What about making a real professional job using a [Postgre|My]SQL database instead and a web server? Instead of something that will just break by itself with time?
FWIW, Chapter 23 explains how to do exactly that - using Excel as the fun, friendly and flexible front-end that all your users know well for the client-side data-entry and presentation, but communicating (2-way) with a web service for remote access to the data. The web service then stores the data in any format you like.