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
Excel book review? IRC chat log? "Converting Users to Open Source- Why Do You Care?"? Has Slashdot suddenly become not so Slashdot?
This is throwing my entire perception of reality into question.
Free of Flash! Free of Flash!
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.
I'm happy for the authors and the glowing review of their work, but I continue to wonder that "production" apps be written with that tool (but then, I wonder about VB apps, too). Amazing apps can be written using Excel (I know, I've written some, but only for me) but I think there is a danger in granting spreadsheets the imprimatur of "production". Not because Excel isn't up to the task but more because of the casual treatment of the world of spreadsheets in IT. I don't believe I've ever seen project management and version control around spreadsheets though both are possible. Also, the level of sophistication to create these applications introduces another problem (in my opinion...): the sophistication required is far beyond the typical Excel user and/or spreadsheet creator (in my anecdotal experiences), and the allure of Excel as a real programming tool is non-existent for the typical IT person... leaving Excel in an interesting netherland.
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?
I gave up with the idea of an useful sig...
It isn't worth the trouble to do it in Excel, expecially not anything professional!
Buckle down and do it for real--write an application. Heck, even VB6 is a step up from that.
Note: I actually tried to do a large project in excel, utilizing Pivot Tables, VBA, and outside DLLs. It was a MESS! The speadsheet would corrupt monthly, sometimes weekly, and it took forever to compile the data in. Later on I rebuilt it as a SQL Server, MSOLAP, and ASP.NET application. It hasn't crashed once since then.
Bad User. No biscuit!
It's called the real world, based on the ridiculous comments thus far I suggest a visit.
No, it isn't. My daddy's Excel book steered clear of the trite cliches.
UTF-8: There and Back Again
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
Do people really "develop" in Excel?
Yes, they do. They start as newbies, then they begin to grow up...
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
Well spreadsheets are not programs - but there is no reason not to attach behaviour to a spreadsheet. You probably are happy to write code that manipulates a database - just think of the spreadsheet as a noddy database perhaps.
That's what they say/said about browsers.
> Call me cynical but I don't think it's possible to create "professional level
> applications" using Excel.
If you mean `for a living` then people write them in Excel, Access, Javascript, batch files... I mean, is this a snobbery thing, or are you making a judgement on the aesthetic appeal of apps?
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.
Well, despite the fact I'm a little dubious about using excel as a programming language/platform, it reminded me of a program I had seen many years ago that was 'interesting'.
:-/ )
:-)
It's called Baler and basically took a Lotus 1-2-3 spreadsheet (with macros) and compiled it into a self executing exe. (Yes it was that many years ago that 1-2-3 was the dominant s/sheet under DOS
So I did a quick google and found there is a visual baler. Check out
http://the-ciba.com/vbaler/vbaler.html
Knock yourself out, if that's your kind of thing
So does Anonymous Coward have good karma?
Oh, like I'm the ONLY Slashdotter who got their start in some flavor of BASIC. What's true of me is true of AN ENTIRE GENERATION of programmers.
Tell me, what language did YOU start with? Did you spring fully formed from your father's head, clutching a disk full of flawless C code?
It's just a spreadsheet. But FFS use a real programming environment to produce applications. Hell, even use an MS programming environment. "Developing" an application on Excel is the height of short sightedness, it's positively myopic.
Deleted
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?
Which is fine if you want to program in assembler. The elegant syntaxes in high level languages are there for a reason - to help make code easier to maintain and develop.
As soon as anybody questions or otherwise scoffs at use of a Microsoft product, the first thing you need to do is question their connection to reality. Remember - nobody ever got fired for using Microsoft. Secondly, further alienate the indivudal with name calling. The name should reenforce a tenious grasp of reality and alienate the individual. "Smelly nerd" was an excellent choice. Note that "hippie" or "zealot" would have also been acceptable (although these terms tend to be a bit more agressive and may alienate the attacker).
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.
Yes, it's an MS product and yes there are better ...
... the very things geeks get paid to build and maintain, oh, and then there's the matter of America's overall increased productivity
... indeed, I always seem to be looking for a better one
however, consider how widespread Excel really is and you will see more people doing more, which in turn requires more administration and infrastructure
mostly it's good because there's a blind barrage of dummies books, but so few deeper ones that hit the mark
Words to men, as air to birds.
Didnt we just have a story about how bad of an idea it was to use a spreadsheet for this sort of thing?
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.
Not True. Not even close.
:p
I have no great love for VB, but I used it this week. I wrote a stock management system, from scratch, in about 20 hours.
(MySQL backend). Its currently running the warehouses of a multinational company (after the sudden failure of a legacy system).
VB is rubbish for games, heavy maths, or just about anything else that requires speed or small code, however it IS almost untouchable for RAD.
When time is ticking, I use it like an elastoplast - Fix the problem right now, make it elegant when you've got time.
The fact that I know and use VB well does not mean I can no longer write assembler programs in a hex editor, nor does it mean I can't write mechanical stress analysis systems in C++.
(And to the poster above who said nerds us C#, you missed out the word 'masochistic')
Windows in 6 Bytes (IA-32) : 90 90 90 90 CD 19
Touche!
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.
Agreed. I've written relatively complex "apps" with Excel and Access. However, I found them always to be flakey at best. Database connectivity sounds nice, but often the size of the data overloads Excel (65,536 rows the last time I used it). Even with much less data it can come to a crawl. And because it can't be multithreaded users get annoyed that it's "locked" and have to wait. What annoyed me the most was the inadequate error reporting. Most messages were useless when dealing with an "app" and I couldn't trap absolutely everything for the user.
Developers: We can use your help.
Wow! I'm surprised. Not only does someone have experience of linking (wrestling) with the Blooomberg API they have a closed end fund specialism! Me too! You also reference crack pipes, which makes me question whehter you are, infact, me...
Nothing like a difficult day when trying to build an analysis of an option chain, realising its necessary to use the Bloomberg API, and spending hours figuring out what bulk data spec to use with repeated calls to the helpdesk. C or VBA makes little difference there!
Have you ever given Datastream a go? For funds their data is a bit more vigorous than Bloomberg (their economics database is awesome), plus they're $5k/terminal vs Bloomberg's $20k.
I'm interested, what exactly do you do? Arb? Pensions?
Earning above median pay for a while now, and I started with BASIC. I believe out of the "Basic Apple BASIC" book. Still have it at home.
I know there are some people who cut their teeth on Java nowadays. In my experience, they are more broken then those of us who had to do assembly and C at one point. Java coddles you too much, and lets you do too many things you think are sophisticated without real work. In my experience, Pascal and C/C++ are still good learning languages. Java isn't so bad at it, but you have to dump classes and GUI's in fairly early, because standard terminal I/O in java is horrific, and you can't write a free standing function. It's nice to know that if I have to look at a hex dump of machine code with the assembly next to it, I can figure it out. You can't tell if your compilers is broken if you can't do that.
So I'll have to provide a second piece of anecdotal evidence that BASIC doesn't fundamentally break you.
Kirby
So it was limited to however many boxes could be jammed into 8x10 inches. That's no reason to diss it.
FORTRAN? Surely a typo - you meant to write 'a mixture of Lisp, Algol, and assembler depending on what I'd been thinking about with John McCarthy and Marvin Minsky that week'?
We're pretty much addicted to Bloomberg since we use them for lots of other purposes (ie preferred stock analysis, regular equity analysis, MA transaction searches). We do valuations of privately held companies and largely use publicly traded companies as valuation guidelines. If you go to my website (see sig), I've got links to some publicly available projects I've worked on using Bloomberg and Excel; namely our dividend policy study and my voting stock premium study.
A really fun thing to do wit Bloomberg is to create marketing lists since they give you the names of corporate officers. The corporate officer list (and company address for that matter) are only available as bulk fields, but I figured out how to break them up to create mail merge fields. The only downside is that I still need a secretary to add "Mr." or "Ms." to the spreadsheets!
Bill Clinton: Pimp we can believe in. - The Shirt!!!
Well, did you ever hear of free software, and that it does have development environments?
Excel is nice for quickly hacking something together, and I understand that at some point people will get the idea to do more complicated stuff with it, but that doesn't change that it isn't the right tool for anything that ever requires to be changed when working large-scale with data. I see you can use a lot of VB, and add a Form, but then there isn't really much of Excel left that you work with, except maybe the SUM function, which isn't exactly rocket science.
Using Excel for VB is like using DOS for the integrated BASIC - it is a dinosaur.
I realize you could hide nasty stuff in a programming language, but it must be very easy to do that in Excel by accident.
I'm still trying to figure out what people mean by 'social skills' here.
Just remember that a "professional" is simply someone who gets paid to do a task. It can be anything from lawnmowing to a surgeon. Being a "professional" does in no way imply being good at what you do. It only means that you get paid to do it.
Call me cynical but I don't think it's possible to create "professional level applications" using Excel.
.exe file, and the other in an .xls file.
If what you're saying is true, you shouldn't be able to write professional level applications using Visual Basic either. You can do almost as much in Excel as you can in Visual Basic. The main difference is that one program results in an
That of course, is not to say that Excel should be your platform of choice, but if that's what your client wants or needs, then give it to them.
Now you got me ::confused::.
I would even write an application in PROLOG if I get paid (once when I coded (had to), I for the fun of it wrote matrix multiplication in (then TURBO) PROLOG, which actually was no fun but was possible).
CC.
TaijiQuan (Huang, 5 loosenings)
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.
Well, it is unfortunate, but, in the real world, guess what? People do use excel. plenty of people work in some sort of environment with . To those people, sometimes the only tool you have, is excel. I know, Im one of them, Ive had to make API calls from VBA in excel, to make an app for users. What Id like to see is someone make a C++ compiler in excel, cause then I can use it, and claim it isnt a "program" its a "spreadsheet" and then use the output, because we are allowed self made programs :p
AKA TypoDaemon AKA TheCafFiend sure, life's a bitch, but how long are you dead for?
I love my hammer. I can use it for 101 things. The most amazing thing is that if someone at work has a flat and no jack or tire wrench I can help them with my favorite tool!
I can change anyones tire with just my favorite hammer. It might not be the best tool for the job but it sure gets the job done. I usually have to break the lug-nuts off because the technique is not something the average car owner possesses.
If Auto mechanics knew this they could save money on buying tools. The would just need one big hammer to get the job done.
Your Average Joe
As a bonus you users likely already know how to use Excel.
John McAfee 'It was like that time I hired that Bangkok prostitute; to do my taxes, while I fucked my accountant'
Yes, at home, I'm a *nix guy. At work, I use excel. It all started with estimate sheets. It was a simple way to look at a large number of data and have it mean something. Hours on the bottom, materials and subcontractors on the top. Multiply by amounts and markup, and you have a number. Sum the column, and you have the total job cost.
But when figuring out how much material is needed, it works nice. I take the sign size and add 6" to each side(for large signs) and that gives me a nice rough square footage to order.
How many clips do I need for a flexable face? Well, that's (length+width)*4.
How much vinyl? height*width*.75.
Screws? (height+width)*2/1.5
Neon footage for channel letters? height*3*letters+letters
people SELL programs for things like this.
The previous has been a secret message to my comrades.
I think it is possible to develop decent front-ends using excel.. you could probably tie it in with a dot net backend however that is nto my specialty.
Excel has a decent enough interface, and the coolest excel thing that i have seen is an excel page opened in-line in the browser linking to a database back-end over the net via remote procedure calls.
The end result was that data retreived from a database by the RPC macro was displayed in excel in a browser which on it's own isnt too hard. The cool part was that if you changed the data in excel, the macro sent the changes back to the server, thus updating the database.. w00t excel front-end to a database driven system
Although this was effectively a macro, it proves that excel can be a decent interface for your on-line system/ not sure if it can be used in the real world effectively as it opens you up to a plethora of MS security holes but it is pretty cool
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.
We have enough MS paid shills out there already, Enderle, Didiot etc
Help fight continental drift.
you shouldn't be able to write professional level applications using Visual Basic either.
Ding! We have a winner!
Glonoinha the MebiByte Slayer
...on porting this all to OpenOffice Calc, Gnumeric and KSpread.
Or to put it another way, Microsoft must pop another champers every time someone does something like this. Part of their announced current strategy is to get people locked into MS Office's foibles (presumably for monopolistic exploitation yet again, which says something about the effectiveness of the US-DoJ - and won't it be fun if the EU takes exception to that?) and this book looks like a dead-centre component of just such a strategy.
Have a guess what one of the el-primo blockers for Linux adoption in the enterprise is?
Can y'all say it with me? "LEMMINGS!" )-:
Got time? Spend some of it coding or testing
VisiCalc and Lotus123 were a major reason that PC use exploded in the business world, driving the hardware and software revolution that makes us all so happy now. Excel is one of the most valuable applications in existence. http://www.bricklin.com/visicalc.htm
...what seems wrong about the title, as extracted from bn.com at 22:34 EDT on 27 April 05:
Professional Excel Development: The Defenitive Guide to Developing Applications Using Microsoft Excel and VBA
Hint - what the hell is the 5th word?
out
Dude,
Check your attitude there. Regardless of how silly people are, its a silly mistake to think you're doing yourself anything but a disservice going around and thinking of everyone at work as an "idiot-user" or "simpleton".
I don't know where you work but most places people aren't very good with Excel its because they don't need it day to day, and they're not interested - it just isn't their main purpose in life to learn Excel. In any case if you're this public about your attitude chances are no one around you likes you much and no one would put you either in a customer facing position or in a people management position. That might be fine by you, but it's also probably why you're bored and jaded with working in a "cube farm".
These posts express my own personal views, not those of my employer
three words i never expected to be in a sentence in that order, unless proceded with "no such thing as"
That's why, for one.
blarg.
Anyone mentally coherent that can also dig out Steven Seagal movie quotes on the fly and tie them to a post justifying allowing hypothetical financial analysts to use Excel at any level they desire deserves to be on my friends list, if for no other reason than the randomness of it all. +1 Chaos Theory
We are one consciousness experiencing itself subjectively. Back to you with the weather, Bob!
People In The Real World Use Excel.
I am one of those people; this is my story.
I was originally hired at my organisation to sort folders and lower level admin support.
I saw most things done were just pure repedative production of exported csv's from filemaker (prodominantly mac environment). So I just automated them with external data. excel formulas were completely alien to people here (!). people had been hired and paid thousands of dollars to produce simple excel worksheets (I'm talking SUM functions).
Then I find out that we're meant to be reporting on all this data entered into this filemaker database, for which upon investigation there was ABSOLUTELY NOTHING PLANNED that I had seen to generate results.
I didn't know filemaker backwards, I'm not a programmer. I probably could be if I could be bothered, but we needed results there and then.
My most logical conclusion was to create an excel file importing all of the data which could be refreshed, which i did. this enabled me to produce actual results and charts about the project.
It took me about a month, I hadn't done so many sumif and countif's in my life, validating everything but basically it provided an insight into the projects status that hadn't been achieved before. Which doesn't sound like much to this crowd, but the data gained from basically converting a filemaker database to an excel report probably determined the future funding of the project. A worthwhile nation-wide (AU) Government project.
Sometimes Excel just has to be used.
(Note, that the datasets we work with are often tens of thousands of rows, and anywhere from 10 to 100+ paramaters)
Would it be more like 5 or, say, 7 (x10K) rows? last time I've checked, Excel can not deal with more than 64K rows (or columns, for that matter), it might have changed, but I would not want to imagine dealing with a Gb dataset in Excel...
Paul B.
.... to beat a few people around the head with...
Being my team's local Excel guru (every team has one)... I was (un)lucky enough to work on a project in my previous job that involved documenting someone's badly written Excel/VBA code after he suddenly left the company.... the guy had clearly just recorded a whole bunch of macros then copied and pasted stuff around, with minor editing... no comments, no naming conventions... ech... painful 4 months of my career...
But seriously, this book is mainly useful to beat people around with, because people like him definitely aren't gonna read it.... they see "Professional" and "development" in the title and they ain't gonna open it.... More so, ub3r-l33-g33k-developer types aren't gonna read it either.... they're all too high'n'mighty'n'l33t to develop in Excel.....
None the less, I'm ordering my copy, so I can slap people around with it whenever they ask me to develop something huge for them.... I'm not even a developer by trade!!!
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.
We just updated an Office 97 (German) to Office 2000 (German); now, when the user opens some of her old documents, Excel suddenly becomes a mixture of a german-and-english program, with some english menus and some german buttons (or vice-versa) here and there, and some of the functions do not even work properly. If Excel is used to create a new document, everything is o.k., and all the menus and buttons are german.
I was not able to find an option like "program language" to fix the problem.
It just seems to be a software error - I am even almost sure, that the reason for errors like this one is nothing else than really *bad* software design.
In my job I have to fix approx. 3 MS Office installations per day (we have got ~ 6,000 clients with MS Office installed), so I even avoid using MS Office with simple documents (no macros, no "programming"...), because of its unreliability; there is almost always something that does not work anymore, when I use an Excel 2000 file with Excel 2002, or after an Update of MS Office.
Do we really want to use such unreliable software as the foundation of our own projects, maybe even for processing mission critical data?
What about software design rules like:
"5 Rule of Simplicity:
Design for simplicity; add complexity only where you must."
"6 Rule of Parsimony:
Write a big program only when it is clear by demonstration that nothing else will do."
Do we really need to use a 187 Megabyte piece of complex, untransparent code with some 1,000 bugs when 150 lines of code can do the job?
So, *PLEASE* do not script MS Office; let real programmers write real programs.
Fair point - excel is good for prototyping, or running once-off spredsheets. I'm coming from the POV where my company's accountants & marketing people are running off entire applications built in pasword-protected excel that could, and should have been adapted to proper applications years ago ...
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
This has been a large per portion of my current job. Yes I do cry each night I'm forced to do it.
To Slashdot or not to Slashdot. That is the question (that will cause me to fail an interview)
I had to ask,
Right now I'm looking for a open alternative to it's OLAP Cube analisys tool... it is a really powerfull data mining tool, and there are a lot of people here that loves statistics and pretty graphics.
I already found some Java projects that provides a OLAP server (Mondrian), and a visualization tool (jPivot)... but they're overkill... an elephant-hunting-shotgun to hit a fly... and I realy don't have the skills to build a complete java-based solution from the ground up.
So, there are open alternatives to Excel programming?
---- You know how some doctors have the Messiah complex - they need to save the world? You've got the "Rubik's" complex
When I first started working with spreadsheets back in 1988 on SuperCalc, I only has basic knowledge of computers then. In 1989, that knowledge was then transferred to Lotus 123 V2.3, which I had a summer employment while still in high school that involved using the spreadsheet program. Namely, the reason why I got the employment is simple. My level of skills to pick up programming terminology was very quick, which I also worked with BASIC in 1984, which my teacher had me type in the program into BASIC character for character and run the program for spelling lessons back in 7th grade.
In my college years, I also had employment, which do you know how many things people did by hand, which I thought was rather tedious and time consuming to do by hand, thus I turned to the computer to do the work for me? Started out with me working in Lotus 123, v3.1 for payroll stuff. I then went to work at the university and did athletic stuff in Excel 5.0. However, as the funds got tight cause I was only working part time while going to school full time, I ended up having to find other employment, which was a very rough part of my life. Eventually cause of my Accounting education, I started working through Accountemps, which I helped very places get more things done on the computer. I then landed a permanent job, which I still work for the company despite the division has been sold 2 different types since my employment with the company 6 years ago.
I started out doing a lot of things in Excel 97, which when I was working in Excel 97, I hated it. Even to this day, I still think Lotus 123 v 2.3 is better than Excel 97 cause Excel 97 is so unstable and unreliable. Anyhow, after I got a lot of smaller tasks done so fast, my boss handed me a job that he thought was going to take me 3 days to get done, but I got it done in 3 hours just by using my comptuer skills with Excel 97.
Well after that, I was then moved from Accounting and out to Production. At that point of time, they had 2 people doing their production reports, one person gathered the information and typed it in, and another person, who initially had set it up, worked the information through the system. Tell you the truth, what they had was a basic framework of the production reporting system, but lacked so much of the automations. It was like building a house without running water, so you would have to go out to the well and fetch your water by hand.
I told my boss, give me 2 weeks to get it more automated, as I'm for one not withstanding to do things inefficiently. I got the automations fully in place within 2 weeks time, for what we had at that time. We still had to do paper logs and input the data by hand, but at least, we didn't have to do any of the calculations or copying and pasting by hand anymore.
However, the system took 35 to 40 minutes just for all of the calculations to take place within Excel 97. This was a vast improvement cause it now meant that it only took 1 person up to a total of 5 hours to do the work, but I still wasn't pleased with the results cause for what I had in place, I knew it shouldn't had taken that long to calculate, even on a P2, 266MHz system. That led me to think of Excel doing redundant calculations, which then at that time, I knew about macros, but never had worked with macros up to that point of time. I also knew, in order to speed it up by knocking out the redundant calculations, I would have to take off the automatic calculations, but I also didn't want to do it by hand either, thus macros was the only thing that would fit the job.
Well I started messing around with the macro stuff and once I got to learn the VBA environment, I had created a relatively simple macro that opened and closed each workbook in their respective order, and ran the calculations via code using the Worksheet.Calculate Method. Once, I setup the code, and ran it, it only took the program 3 to 5 minutes to run all of the calculations and charts.
Well while I got the later part done, the IT department
DNA?
Ok... how 'bout this real world example:
My employer currently has a big problem predicting order shipment dates, due to many system- and process-related issues. There's a 3-year, multi-million dollar IT project underway to build our next-generation systems, which will hopefully solve this problem. Unfortunately, our customers can't wait that long.
So, management has put together a project team and given them 90 days to do as much as they can to improve this situation. The problem is that they don't even know the root causes of the problem yet. They need data to help determine that.
IT is so backlogged with the next-gen project that it would take them more than 90 days to scope this data assignment, much less develop anything.
Using Excel and Access, I was able to build an app in about 2 hours that provides the reports they need. Since they're still scoping out the problem, they need "tweaks" to the report every week, which I can do in minutes, without working through IT red-tape that would normally take weeks.
I haven't built a long-term solution. I've built a 90-day solution to help them scope a long-term problem.
The point is that the "best" solution usually takes a long time to develop -- sometimes longer than your customer can wait. "Hacked" solutions like Excel/Access, while definitely not final-state solutions, can fill the gap nicely.
- Rob Cottrell
Perhaps for Excel there is a bit to say about naming conventions since the Microsoft ones may not be obvious. As long as it's information and not over-eager cluebie evangelism.
-- Ed Avis ed@membled.com
Sorry, yesterday was a really, really long day. That having been said, however, I can honestly say that regardless of where I've worked, there are always some really stupid people who think they know what they're doing and just end up making my life more difficult. It's not just that they don't know Excel; it's that they think they know Excel, but they can't actually do anything. In many cases, it is actually requisite for the job that they are doing - they actively take on work involving creating spreadsheets - and then I have to pick up the slack because of their overconfidence. I have no problem with people who aren't interested in using Excel. My problem is with people who need to use Excel, and use it every day, but never bother to learn how to do anything because they know that I'll be here to bail them out with a program that never needed to exist in the first place. It's a waste of my time and company resources, so it gets my knickers in a twist. Sorry if I offended with the rant, but I feel justified in my sentiments.
This sig has been stolen. Return it to its original user for a reward.
"A database is a collection of information stored in a computer in a systematic way"
there you go, Excel is a database.
Oh and this statement:
"Anybody with that many rows of data is likely not using Excel as a spreadsheet "
Is false. Seen it.
The Kruger Dunning explains most post on
I don't know 'bout you but I'd rather spend a few minutes thinking and write 15 lines than just dive in and write 115 lines like most basic educated guys seem to do.
I don't know why you seem to think 'started with BASIC' is equivalent 'doesn't do any planning'.
Even when I wrote in BASIC, at age 8, I wrote out pseudocode descriptions of program behavior (and often drew FLOWCHARTS!) before typing a single line number.
Choice of language is frankly irrelevant to the argument you are now making.
The elegant syntaxes in high level languages are there for a reason - to help make code easier to maintain and develop.
Look, I'm not saying that anyone should be writing code in BASIC today -- the language has been obsoleted. But in the past, it had its place as a legitimate introductory language.
And while I'm happy that high level languages make code easier to maintain and develop than ever before, a GREAT programmer needs to grok what's happening at the low levels, too. If all you know is Java and have never had to push a register value to the stack or even make a call to malloc(), your prgramming education is incomplete.
Just putting in my "yes it happens experience."
I will admit to writing code for a major company (in the top 100 of Fortune 500) that ran behind Excel. It was ugly when it was given to me and it was about the least ugly it could be when i turned it back over to them for the fact that it was still written behind Excel.
The code was completely independent and self-sufficient in that it could check itself against a network copy of the code and update itself should the dates not match. It read in information from a DB2 backend, interacted with a 2nd program controlling window events and sending keystrokes and did everything that would make you cringe.
At the end of the day, the new program was 3x as fast as it was before, and had all the "features" added above that they needed in it. And i was very scared of my future and any dealings i might have to do with the company.
The scariest part was that for the period of time they were using the code before i updated it, it was obsolete and producing improper numbers. Which they would alter by hand and did not always match what the corrected output was supposed to be. The other scary part is that they were using a lot more than just the one program.
A friend of mine just recently started doing the job i left and he has seen and understands the scariness. He will try to make his dent and eventually leave as well.
--
Hi,
You didn't offend me in the least. It just sounds like you're capable and that if you're getting frustrated and lashing out that might be holding you back.
I'd suggest you consider the consequences of not hand holding. Instead next time someone takes on work they're not capable of point them to the right resources to get the job done instead of doing it for them. If you're in a position to do this without risking your own position within the company, you need to change your reputation from being someone who'll bail their workmate out by doing their work for them, to someone who'll bail someone out by showing their workmate how to do the task.
These posts express my own personal views, not those of my employer
Leaving aside the issue that has grabbed most posters' attention (why program with Excel), the PED book is interesting in its own right if you program Excel.
Speaking as the owner of ten Excel books - some excellent - the first 100 pages of PED have taught me (e.g.) that Excel gets unstable once the module size gets too big and how better to encapsulate UserForm modules. It's terrific for the reader who tends to throw code together without much planning (I confess to knowing somebody like that...)
This book is not a good first book for VBA programmers (they say as much, recommending another author) but will be of use to those who have had some practice. It's not compendious - I think it irresponsible to mention Conditional Formatting without saying that it doesn't work right THROUGH VBA. This I found out by experiment (TWO DAYS MY WORKING LIFE...)and Microsoft have now confessed (KB 895562 for the severe anorak). There are other things I would have expected to find, but this makes me feel well-informed. The index is skimpy. But that's all the bad I can find - this is a concise book of 900 pages (odd though this may sound), well written by three people who have built effective Excel applications.
The book gets the reader some of the benefits of the authors' experience, and the price is modest. An example from my own experience - going to my employer last year, telling them that I couldn't fix the instability of my Excel app. They went with my suggestion (abandoning the project), at a cash cost of around USD 50,000 for my time to that point. It turns out that my biggest module was five times bigger than the stability limit. Splitting the module - less than a day's work.
The great strength of Excel is its flexibility - you can re-purpose your data very easily. That's also the central weakness: it's all too easy to commingle data and logic, and not always easy to audit a spreadsheet. Second, experience teaches that Excel statistical functions are not of the best quality. They have the great merit of being easily available - burgers for the data-hungry.
VBA shares these weaknesses. It's a great tool for throwing together a quick-and-dirty procedure, and if you just want to solve today's problem today this may be appropriate. It's also really easy to write bad code. As far as I can tell, doing it better requires the same type of disciplines as in other development environments - e.g. VBA claims to do garbage collection but you had better not trust VBA to do it reliably if you are likely to bump into a memory limit (for more on limits, see www.decisionmodels.com/memlimits.htm). The authors offer some pragmatic recommendations but their underlying approach is to be appropriately systematic. I hope and expect that this attitude is catching. I admit to being a sloppy programmer - PED has already improved my code.