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.
Isn't this like painting a house with tomato paste?
you could do it, but why?
Starsucks
Sounds like an oxymoron to me.
100% Insightful
So as long as the spreadsheet it's based on never has more than 65536 entries.... or has that limitation finally been removed?
--- I used to moderate, then I read the -1 articles and decided having to filter through them was not worth it.
Although it's necessary to get a good idea of how the tool (any tool) works, you really can't replace good experience working with a spreadsheet.
...without doing things a number of times and thinking through how the system you're going to be building will be used, you'll likely end up with something inferior. Only experience can help you out there. coding practices, naming conventions and application structure are a good start, but that's about it.
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.
I store my recipes online (the way nature intended)
hmm excel? isn't that what everyone here complains about leading to a huge mess of macros backed onto a spreadsheet that was never intended for it.
if you think something will grow beyond a triviality a database is a much better idea
note: i'm known as plugwash most places but i screwd up registering that here somehow in the past and now can't register
My daddy didn't need Excel to make grocery lists, nor PowerPoint to give a speech, nor Word to send a letter.
;D
My daddy also called "spreadsheet programming" "math" and did it in his head or with a slide rule.
I watched C-beams glitter in the dark near the Tannhauser gate.
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.
I fear that becoming pro will render my qualification for the upcoming Computer olympics void.
Damn that Olympic committee!
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.
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#.
Feed the need: Digitaladdiction.net
Suffice it to say, this ain't your daddy's Excel book. You know what, it's generalizations like this that make me sick. My father is a professional Excel developer, and he would be VERY disgusted to hear this comment.
It's called the real world, based on the ridiculous comments thus far I suggest a visit.
Is a light weight Free/free spreadsheet either written in Java or otherwise ported to MacOS X. I specifiy "light weight" because OO.org seems just as bulky and bloated as M$-ware.
Start Running Better Polls
it's called VSTO. You can creat a full expandable, scalable application with it.
I know, I am doing it now.
It doesn't use macros, or VBA. You can write clean OO code.
The Kruger Dunning explains most post on
Please don't let your students use basic or visual basic, it will destroy any chance of learning to write real code.
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.
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.
Don't disappoint your bird dog. Go to the range.
I've seen a hell of a lot of spreadsheets but these ones are brilliant. Space Invaders and Pacman coded in Excel.
Each cell represents a pixel.
"Who says nothing is impossible? Some people do it every day!" - Alfred E. Neuman
With some Excel(R) scripting, you can create sophisticated applications that really boost your productivity.
Here are some programs I use daily:
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.
This sig has been stolen. Return it to its original user for a reward.
Kind of reminds me of that time Kif programmed that Holo-Shed program in just 4 million lines of BASIC.
Homer no function beer well without.
I'm probably on the Excel crack pipe more than most people here and I would never call anything I do with it "development." I'm just building different types of models for financial analysis. The most complicated stuff I do involves linking to the Bloomberg DDE server to download closed end fund data and running Crystal Ball (a commercial Excel add-in) now and then for Monte Carlo simulations. Furthermore, I only use Excel to store limited types of data, such as weekly dumps of closed end fund data.
Bill Clinton: Pimp we can believe in. - The Shirt!!!
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.
You complain that your customers -- financial analysts all -- are building stuff in Excel. According to you this is bad. I kinda line up on their side of the fence on this one, sorry. You'll have to rip Excel out of their cold, dead fingers, or replace it with something much, much better. And that ain't Calc, which is a me-too, not an evolutionary step.
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.
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.
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
Excel is reasonably powerful, very customizable, and, most importantly, very accessible to the normal user.
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.
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!
You can have it fast, accurate, or pretty. Pick any 2.
So many (frankly) BS comments about Excel's relevance to development....
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
Repeat after me. Excel is not a database. Anybody with that many rows of data is likely not using Excel as a spreadsheet (performing calculations).
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.
I am becoming gerund, destroyer of verbs.
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.
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.
I'm currently working on an Excel/Access contract - not by choice, but I can't exactly install a PHP/PostgreSQL system as they have nobody that has ever heard of PHP let alone would know how to make a change to it.
.saved to true in an onOpen event doesn't fix the problem - it's bloody annoying!
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.
Time is an illusion. Lunchtime doubly so. - Douglas Adams