Greetings and salutations, my fellow VBA-fearin’ congregation. Evangelizin’ Jeff here, spreading the good word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a high-yah pow-wah.
Our most pious Brother Jon Peltier (who’s fine presence is to mah left) broke his vow of silence over at the PeltierTech monastery to make a most inspirin’ observation during his recent confession Highlight a Specific Data Label in an Excel Chart:
Because I’ve been doing a lot of coding lately, my first thought was an approach using VBA. Then of course I came to my senses, and worked out a non-programmatic approach.
If possible, it’s usually advantageous not to rely on VBA for such tasks.
Hallelujah, brother!
My visionary brother is right: if there’s one rule of VBA that you should religiously observe, it’s to let the application be the application, where ee-fishin’ tah do so. A whiles back, I jokingly spoke it alike this:
The serenity prayer for Excel:
Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and the wisdom to know the difference.
(And I particularly chuckled at Excel Ninja BobHC’s response: You been on them tablets again.)
This sentiment is echoed in the commandments given us in Professional Excel Development (written by those latter-day-saints Bovey, Wallentin, Bullen, and Green):
This Good Book evangelizes that Excel developers “…shalt be divided into five different categories”:
- Basic Excel users, whom generally use Excel for fairly simple tasks, but as their exposure to Excel grows, so does the complexity of their worksheets and use of complex worksheet functions, PivotTables, and Charts.
- Power Users, whom have a broad understanding of Excel’s functionality, and occasionally use snippets of VBA from the Net or via the Macro Recorder, but their code tends to be messy, slow, and hard to maintain.
- VBA Developers, whom make extensive use of VBA – perhaps too much…to the point that they tend to use VBA to tackle practically every problem.
- Excel Developers, whom realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality, augmented by VBA where appropriate.
- Professional Excel Developers, whom know more languages than your typical Babel Fish.
That leap from VBA developer to Excel developer is worth striving for. (Don’t bother striving to be a Professional Excel Developer…they are so nerdy that they get about as many dates as your typical cloistered monk or nun). Unfortunately gaining the wisdom to jump from that third class to the forth one ain’t easy, and dedicated sermons on this matter are few and veryfar between.
Far too often the likes of yours truly are often so focused on leading you not into temptation and instead down a righteous path, that we simply never take the flock anywhere near enough to temptation so that we might cautiously peer at it from a safe distance and say in our most solemn and hushed tone “That way surely leads to hellfire, damnation, and eternal recalculation”. No siree, I’m afraid we usually opt instead to simply get the flock away from there.
However, help is at hand, sinners. Forums such as our very own Chandoo.org/forum are a great place to get guidance on such spirited matters…particularly if you ask the right question, such as “What is the best way to achieve X using Excel version Y”. But you’ll need to ask an open question based around what you are trying to accomplish, rather than being overly focused on how you are trying to accomplish it.
For instance, if you ask “How can I efficiently achieve X with VBA“ then that is all you will get…answers about the most efficient way to do it within the confines of the particular tool you have specified. Which will often not be the most efficient way. In fact, I’ve lost count of the number of times where someone has asked for a formula or VBA solution to some devilishly complicated problem – and got something devilishly complicated formula or code as a result – when a mere PivotTable would have sufficed. Or when some very simple Structured Query Language (SQL) via the in-built (but antiquated) Microsoft Query interface would have nailed it.
[Aside: SQL is basically a database language use to perform the database equivalent of lookups and to crunch numbers, or to conditionally join large datasets based on multiple complex conditions. SQL can be directly leveraged by Excel with minimal programming. Heck, you can use SQL to do stuff with NO programming whatsoever via Microsoft Query – a handy (if ancient) little interface bundled into Excel that will look familiar to any Access users. For an excellent Excel-centric introduction to SQL, read Craig Hatmaker’s amazing Beyond Excel: VBA and Database Manipulation blog. Chandoo also has a great guest post by Vijay – Using Excel As Your Database – on this subject. Ignore all the naysayers and unbelievers in the comments who say “Excel shalt not be used a database” for they know not what the point is. Which is that yea Excel doeth speak in SQL tongues at a pinch, and SQL is pure salvation when it comes to manipulating data, be it Big Data, Small Data, or Somewhere-In-Between data.]
Not to mention the miracles even a layperson can perform if they have the almighty Excel 2010 and PowerPivot installed. Or Excel 2013’s Data Model, which lets you mash up data from Excel Tables and serve them up directly as PivotTables with not a VLOOKUP or Macro in sight.
The end of Excel ain’t nigh…
Every release, Excel gets stronger and stronger. Excel 2010 offered us sinners significant improvements over previous versions…giving us things like Slicers and the free PowerPivot add-in. Excel 2013 takes a giant leap forward in allowing us to leverage off of inbuilt functionality to do things that we would otherwise require tons of complex code and complex formulas to achieve. Had Excel 2013 been launched 10 years ago, I simply wouldn’t need to have been a-preaching VBA and SQL to as many unbelievers as I have. If we keep abreast of these changes, then as the functionality of Excel ramps ever up, our code can ramp down accordingly.
The bottom line here is this: if thou strive to be a really good Excel developer then thou best get to know what’s behind just about every nook and cranny of the Excel application itself. Particularly the newly prophesied ones (yea the power of PowerPivot compels you,according to that dark preacher Mike Alexander). So go and explore all those mysterious things on the ribbon. You don’t have to master all of them…but it sure does help if you have an inkling of what they all do. Not just the obvious things like Tables and PivotTables, but the more mysterious ones like Slicers, Data Validation, and What-If-Analyis. And also the completely hidden ones like Goto Special. Not only do all those things do things natively that would require many Shekels of VBA code to replicate, but most are completely addressable from VBA to boot. Meaning an Excel Developer can simply say “Excel – do that thing with this data“.
Before you try to bend Excel to your complete command, study it well. No matter how much you want to jump right in tinker with Excel’s very soul, don’t discount what’s effectively printed on the outside of the box. If you do, you’re just another lazy devil writing hellish code.
Feel free to leave your own theological questions and musings in the confessional box below. Unless it’s to say that you don’t like Pokey LaFarge. Keep that to yourself. Because I love ’em. Saw them live in Wellington a couple of weeks back. Definitely worth checking out if they come to a town near you.
11 Responses to “VBA Serenity”
When I stop laughing i'll read the rest!! lol Brother Jon !!!
Dont get me wrong, love Chandoo.org and every email (up to a few days ago) was very valuable. But this longwinded jokiness is just a little to 'off the point' to read when you are pressed for time.
Point taken, Johan. Some of my articles are definitely way longer than the average here. Here’s my thoughts on your comment – and I do appreciate you making it in a constructive manner…it’s much better than a rather unhelpful ‘I’m no longer a fan’.
So here goes.
This article is about something that’s important if you want to become an Excel developer, but fairly abstract. This makes it quite hard to pin down with concrete examples. So I thought I’d have a bit of fun with it.
The problem is, not everyone who visits here wants to become an Excel developer, let alone wants to be subjected to my idea of ‘fun’. Apologies to readers who fall into either – or quite likely both – camps.
But that key message is important: that the deeper you get into VBA, the more you need to realize you want to actually avoid VBA where prudent. So my so-called serenity prayer may well be tongue-in-cheek, but at the same time it is very good advice, and it’s the kind of advice I wish I had seen back when I was learning VBA. In fact, I’m going to repeat it, in case it got lost in the brashness of my original post:
Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and the wisdom to know the difference.
So important concept, but how do you underscore it with some kinds of practical examples? I don’t know. The best I could do was reference the equivalent message from the widely regarded Professional Excel Development handbook – which for all I know someone might buy and find useful. And if someone does, then this post served a useful purpose.
The second best way I could come up with was to point out that the kind of peer review you can get from the likes of a forum are invaluable when learning how to use VBA efficiently. Because the best VBA leverages so much off the app to which it is tightly bound that it is much shorter as a result. Unlike my post and this comment. In fact, had I just used the above words instead of the much lengthier blog post, we probably wouldn’t be having this discussion in the comments now. 🙂
Of course, the trouble when trying to talk about stuff like this is that just like the five categories of users that the book talks about, the readers of this blog also sit all over the Excel User spectrum too. Not all readers are at the point where they would get any value out of this post. Some are. Some of those that aren’t, will be in due course – particularly if they keep reading (and if I don’t scare them off). And they just might remember this article, or refer back to it, or stumble across it again when they are ready for it. That happens to me all the time, where I read something on say Daily Dose of Excel and think “That is beyond me/of no interest to me/irrelevant to me” and then a year or two later I’m searching through that same site trying to find out where the hell it was.
The article I did before this one about Tables, Pivottables, and Macros also had some padding on the bones. But mostly it was meat. On the padding side it had a fairly short couple of paragraphs of introduction (Chandoo would probably have used just one) and a quick couple of images easily skipped over (and again Chandoo would probably have used just one) and then jumped pretty much into the meat and bones of a good topic. And it had a couple of youtube clips at the end that I'm sure would be easily ignored by anyone not interested.
It used lots of images to underscore things, and those images were actually drawn from a PowerPoint training presentation I give regularly that users tell me works for them because it shows rather than tells.
A lot of the remaining length is also due to some code that actually does something pretty awesome if you spend any time with PivotTables. That code can save you anywhere from a minute to ten minutes a day.
Overall I’m pretty happy with that article, and it was pretty well received if you look in the comments. In fact, one of those comments is the highest compliment anyone has ever paid me on this blog:
Laughed my socks off, but more importantly, learnt a massive amount. Given me a whole new perspective on the development of dynamic reports and charts. This is really the way applications should be taught.
Again, I realise my delivery doesn’t suit every one, and the content isn’t relevant to everyone. But Excel is a big universe, and there’s a lot to cover.
I freely admit, sometimes I ramble on too long. In fact, sometimes I find myself writing something because I think someone I know in the development community will get a kick out of it. Hence the oil painting of Jon Peltier.
But sometimes the length of my articles has less to do with such extra padding than you might think. Some things simply take longer to talk about than others, because they are difficult subjects. And those are sometimes the things I pick off, because not many others have tackled it.
Take my recent article Handle Volatile Functions like they are dynamite that I posted a couple of weeks back. That article is really long. It’s necessarily in-depth because it's a hard subject that otherwise only geeks would come across via say MVP Charles Williams' site. It certainly isn't for someone with just a passing interest of Excel. It is for an intermediate audience or for basic users who want one day to become intermediate.
It's an incredibly valuable lesson, and I have enormous pride in that article. Took me days and days of writing and re-writing, and that will pretty much go in the book I’m writing with pretty minor editing.
Even though it’s long and fairly advanced, I think that Chandoo.org is the best place that article could sit – even though it will go over the heads or interests of perhaps 90% of readers – because the remaining 10% simply won’t see this stuff otherwise. And it’s that 10% that design models and templates that the remaining 90% of us end up using or inheriting. That 10% can make the remaining 90%’s jobs a pleasure, or pure hell.
Thanks again for your comment. I’ll bear it in mind. You’re not the only one who feels that way. But at the same time, not everyone feels that way. And at the end of the day, I do this for fun, not for profit. So I’ll write in a style that I enjoy writing in while (hopefully) teaches somebody something useful. Until Chandoo confiscates the keys.
Regards,
Jeff
@ Johan, so read it when you have time, simple! or dont read it, just as simple! boom!
Haha. I see what you've done there, made light of Christianity and mock the Bible. How funny and courageous. Looking forward to next week's post where you lampoon Islam and the Quran.
Sorry you feel the above makes light of Christianity and mocks the bible. I don't see any scorn or contemptuousness in what I wrote, so while I accept that the article did offend you (my bad) I don't accept your claim that my article mocks the bible.
On re-reading it, I can't entirely discount that from another person's perspective it could be construed to make light of Christianity...although that perspective isn't mine, and that certainly wasn't the intent.
This is the traps of writing articles for a blog with a very wide, cross-cultural readership. I'm in New Zealand, and don't have a heck of a lot of the Christian tradition to go by (although arguably even I should know that I was going to unintentionally offend someone with the above). Plus I'm a bit of a larrikin to boot.
I've decided that rather than annoy aspects of Chandoo's readership - a readership that isn't mine - with my fairly irreverent writing style, I'm going to spread my wings and start up my own blog in the near future. Till then I'll be on my best behavior if I post anything back here. (After then, I'll likely be a lot more irreverent. But it will be my readership and soul to lose).
Say it ain't so bra-tha!
It will be a pity to see you go. Please make sure you give us your blog site if you do end up leaving.
And if Excel doesn't end up working for you, I'm sure the Tonight Show will leap at the chance of benefiting from some of your comedic writing. You sure do have a talent for communicating what could be construed by some as staid topics in a refreshing and engaging way.
Hi Jeff. Thanks for your reply. Yeah, I know you can't know everyone's personal situation. If you picture being in school and everyday the other kids make fun of you that your Dad's a preacher. Then read the post, got cross and fire one off, a pretty blunt one at that.
But its obvious you put a lot of effort and time into writing your posts, and I'm sure its extra hard to make them fun and entertaining.
Liked your James Bond macros and your explosive functions, good for kids who don't want boring academic stuff.
Whether you're staying on Chandoo or going out on your own, I'm sure you will have followers, and I'm sure I'll read your other stuff too.
Anyway, it hit a nerve as I think that stuff is important and people make light of it too easily, but I know what its like to be put down and didn't mean to make you feel put down.
That's a nice reply, Bored. Hey, sorry I hit that nerve. I'm a real fan of Excel...I'm nothing if not enthusiastic about it. And that's why I went with the evangelism theme to this post...particularly given the object of evangelism is conversion and spreading the word, and I wanted to capture a bit of that evangelical zeal. A bit of Robert Duvall say from The Apostle. (Come to think of it, James Brown's music is pretty evangelical).
What I was aiming for was a bit of pastiche I guess, which seeks to celebrate the work it imitates. As opposed to parody, which seeks to mock. But one person's pastiche is another person's parody ... something that makes one person (i.e me) laugh out loud will undoubtedly make many more people roll their eyes at best, and unsubscribe at worst.
I guess that's why I want to find my own audience on my own blog...because I won't have any worries about causing problems within someone else's readership base - meaning I won't suffer from the self-censorship that inevitably comes with writing to someone else's audience.
At the same time, I'm the kid who was the class clown. I want to write a laugh-out-loud Excel blog. The kind where I can say "You don't come here for the Excel, do you..."
-----
On re-reading it, I can’t entirely discount that from another person’s perspective it could be construed to make light of Christianity…although that perspective isn’t mine, and that certainly wasn’t the intent.
-----
I can. At no point did this post mock Christianity. Jeff picked a voice that was fast paced and entertaining and via that voice, communicated some good information.
The voice is a little cartoonish because, at least in the world view of most people who read for fun, the southern preacher is a cartoon character. Ok - so a cartoon character who preaches homophobia, encourages complete scientific ignorance amongst their followers, and plays with dangerous snakes for lols - but cartoony none the less.
(was that the lampooning you were looking for?)
Jeff,
You're always welcome to write on the Bacon Bits blog, where the tone has been ridiculous and irreverent since day one.
Not one of my twelve readers have ever complained there.
Send me an email. mha105 at yahoo.com