A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
In the first part of my manifesto, I outlined why I think we should really change the way we code. The important takeaways of that article were:
(1) the Hungarian Notation coding style is old and should no longer be preferred;
(2) we cannot simply code for ourselves anymore; and
(3) the implications of the older coding style has made some programmers question whether VBA and Excel are really even still relevant anymore.
Before moving forward, I should be clear: Hungarian Notation is a symptom of the overall problem. That Hungarian Notation is still championed is a demonstration of how Excel development has been left behind as compared to other technologies. Below, we’ll go beyond Hungarian Notation and address many of the common perceptions of Excel development.
Right now, the United States (and, from what I understand, the rest of the world) has a shortage of computer science/data analysis professionals. In the next decade, the demand for developers will greatly outstrip the current supply. I know of many computer science professionals who won’t go into Excel development because they don’t view it as a professional development platform. I know of many accounting and finance professionals proficient with macros who would never think of themselves as developers.
The fact is many of these individuals could help address the workforce demands of the next decade. But there’s virtually no crossover: the computer science major decides not to use Excel to solve a problem, and the company he interns for purchases a large unstable product from a vendor—or just simply abandons the idea altogether. The accounting professional wants to link all her workbooks together with an Access database backend, but her boss remembers how macros crashed her machine several years ago and frowns on the idea. A sophomore in his Information Systems class silently questions everyday why anybody would ever use VBA.
These are anecdotes I’ve pulled from my own experience and those of my friends. And, to be sure, I’ve found organizations who understand quite well how useful Excel and VBA are. But such organizations are fewer these days (at least, in my experience). Excel is not taken as seriously as say Tableau or QlikView. And unless we do something, there’s little reason to expect this to change.
It just works is the compatibility level Microsoft has committed to Visual Basic 6.0, the language on which VBA is based. Microsoft keeps VBA around because there’s still demand for it. But even they discourage the use of Hungarian Notation in other languages.
So why should we use or even prefer VBA? The typical answer is that it can do a lot of with little effort. In fact, if you ask folks about the future of Excel desktop and VBA, the standard answer is there will always be place for VBA (as there will always be a place for Excel desktop).
I’m not here to disagree. But there’s an undertone of defeatism here; we use VBA because it just works. We can’t imagine a world without it, because so many spreadsheets at big companies still make use of it (what will happen if it were eliminated?). People should know VBA in case their local VBA Expert decides to retire.
None of these points however promote the objective advantages of VBA. Rather they point to the fact that VBA still exists, and then conclude the Office suite is much better with it than without. It’s really a lack of imagination on our part to assume something better couldn’t come to replace it, or even Excel.
I think these arguments represent a sad state of affairs. So long as VBA is viewed as the arbitrary (and old) language in which we must operate, we’re not going to progress. There are so many VBA coders who won’t ever touch a lick of C# or C++ because they’re scared of anything C. Some of these coders even do VB.net but won’t touch C# although the semantic differences between VB.net and C# are almost trivial.
So long as we encourage people to code in methods that have not progressed since 1998, I’m not sure there is an objective advantage to learning Visual Basic for Applications. Many people start their programming with VBA… and end there. If we encouraged people to use Option Explicit for instance, they’d be well armed to advance to other languages. If we encouraged folks to write code in a style similar to .net, the transition to it becomes almost seamless. And, given the shortage of developers required, I think it’s a goal worth pursuing.
No longer should we write code just for ourselves. As long as we do that, every accounting or finance office is going to freak out when their VBA expert leaves. Why should they go through this?
At my first job in 2007, the macros being used had not been updating since Office 2000. At first I was told not to touch any of the code because they were afraid what would happen. Once I convinced them there was a better way, I had to go through and replace all the previous code? The way the code had been written previously was almost entirely unreadable. Like I said: we’ve been taught to code for ourselves. And the prevailing thought is, if my code is working fine, then there’s no problem. Why fix what ain’t broke?
Because the entire system is broken. If the person who comes to replace you as the VBA expert can’t figure out what’s going on, then your code is not good code. Consider the state of modern languages: they accommodate team coding and reuse by a community. They use versioning tools like SVN and Git. (To my knowledge, no versioning tools have written to do the same with office files that use VBA even though it desperately needs it.)
In the Excel TV episode in which I argued with Bill Jelen about using VBA, Oz argued that everyone’s programming experience is personal. But since when has that been true? Programming isn’t a pInterest page. I’m not saying there aren’t different styles and preferences worth considering. But no other language platform operates under the idea that programming a serious Excel application for a large financial institution should be as personal as one’s Facebook. We should have standards—modern standards.
Isn’t explaining what you’re code is doing what comments are for? Sure, but comments are supplements to your code. They were never intended—and should no longer be thought of—as the sole explanation of what you’re code is doing.
We’ll go into comments a bit more in the part 3. For now, think about them and where they might fit into everything I’ve presented so far.
As you can see, my beef isn’t just with Hungarian Notation. Hungarian Notation is a coding style that encourages personal tastes because there’s no practical standard. In addition, it encourages unreadable coding styles.
A larger effect of which Hungarian Notation is a part is that Excel developers aren’t taken seriously. Part of the problem is writing code that only really one person—that’s you, the developer—understands. By contrast, a feature of modern languages is coding for others, especially for a community at large. That many coders write in a way that only they can understand is a hindrance both to businesses and to the advancement of the community as a whole.
In part 3 of this exciting serious, we’ll go into the role of comments. I’ll also provide a new way of thinking about coding, which I refer (perhaps incorrectly) as semantic coding. Finally, we’ll end with a rather mundane example.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide