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)
Call me cynical but I don't think it's possible to create "professional level applications" using Excel.
In fact, who is masochistic enough to even attempt writing a complex app in Excel? Let alone write a book about it...
Do people really "develop" in Excel?
My Tech Posts on Twitter
Mr Burns: Excel(lent!)
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!
``Excel'' and ``professional'' still don't go together, though it's getting better.
See what I've been reading.
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!
Please don't let your students use basic or visual basic, it will destroy any chance of learning to write real code. It's been said before by smarter people than me and it's _true_ folks.
... Standards and Practices !
PenGun
Do What Now ???
returns http://shanesworldgalleries.com/maeva/1.jpgShanesw orld
maybe Microsoft didnt trademarked the string "excel"?
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.
Isn't this like painting a house with tomato paste?
you could do it, but why?
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.
The authors, Stephen Bullen, Rob Bovey, and John Green, show a level of sophistication well beyond the norm
If they showed that then they would not develop in Excel at all. Why would /. run a puff piece on one of the most reviled of M$ bloatware?
an ill wind that blows no good
No, it isn't. My daddy's Excel book steered clear of the trite cliches.
UTF-8: There and Back Again
Sounds like an oxymoron to me.
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
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.
You can write excel applications that use .net. No vba, no macros.
It is used for scorecarding, reporting, analysis, etc. The spread sheet is just a presentation layer, if you will.
Would I use a spreadsheet to create an automated sprinkler system? no.
But I would use on to display data in a report form, like every other single fortune 1000 company there is.
The Kruger Dunning explains most post on
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:
Hold on... I almost have my "etch-a-sketch and spirograph" Excel-based programs complete! Now with this book, I'll get them posted ASAP.
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?
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
Professional Excel Development
What else did your daddy do? Did he do something dirty in the garden shed when mommy was away?
I emplore you to continue in your mockery of tapping one's plowshare into soil and spilling fertilizer among the grass.
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.
n/t
than reading the comments of a bunch of open-source fanatics on Excel Development. Most of the people commenting have never even SEEN Excel...It's too funny!
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.
Lemme get this straight - excel is a spreadsheet application. It's for accountants and other number crunchers who dont know how to develop.
Anyone with an ounce of nouce who can develop an application geared towards shredsheet-based reporting will drop the sucker straight into a database and do some real crunching on the fucker. Full stop.
Once your done, output it back to excel, for sure. The power of excel is being able to look at, filter, sort and graph data outputted by systems - without any programming skills.
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.
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.
Either there is a joke I missed, or it's mansisses in your sig
***It's Kharma vs. Kharma***
So it was limited to however many boxes could be jammed into 8x10 inches. That's no reason to diss it.
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.
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.
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.
At last - Slashdot is headed in the right direction. I've been reading and reading and waiting and waiting for more articles on Microsoft products. I can't get enough on ZDNET and the rest of the true seekers of the TRUTH on other Microsoft supported sites. The other systems like Linux don't support Excel so they are just wasting time with thier wannabe software. Keep publishing great stories about Microsoft and thier great products - it's the stuff that matters.
We have enough MS paid shills out there already, Enderle, Didiot etc
Help fight continental drift.
Yes, Professional Excel Development sounds like a sleeper hit to me. Here at Military Intelligence we can really use it, so when I run out to pick up our jumbo shrimp lunch, I'm going to spend some of my extra money on a single copy of this so we can do some accurate estimates. I'm already an amateur expert in Excel, so this has real potential. I may even take it along on my working vacation so I can mix some higher education with a little planned spontaneity. Those who bash Professional Excel Development have clearly misunderstood. For me it's kind of an essential luxury.
JMP
...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
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.
Excel consistently (although only with certain spreadsheets-- is that still consistent?) changes the settings on my task bar.
I like "always on top" and "hidden" for my taskbar. Excel changes this to just plain "always on top" blocking my emacs minibuffer.
It is definitely Excel that is doing this. I have observed this for at least five years now (Win95, Win2000, WinXp).
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.
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
"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
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.
--
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.