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.
Nobody thinks of us as developers.
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.”
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.
Coding for ourselves no longer
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.
Comments won’t save you
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.
Conclusion
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.
[polldaddy poll=8018765]
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
Very much agree with all of this. Nice job. I’m looking forward to part 3. The common vba usage of Hungarian makes it pretty much unreadable and drives people to focus on the wrong things …eg Thinking about types before function. I don’t do that much in vba nowadays, but I believe it certainly has a place for a long time in the future. Enabling shareability and versioning would go a long way to change the perception of vba as an old mans game and improve quality through peer pressure, but we need to careful that modernisation doesn’t introduce too many barriers to entry. Too many things to learn before being able to achieve a visible result will kill it. Its value proposition is its immediacy.
“but we need to careful that modernisation doesn’t introduce too many barriers to entry.” I couldn’t agree more. I think you highlight a very important caveat to my argument.
The reputation among IT professionals of Excel as a valid platform for building robust business applications is indeed often very low , and so because of the examples that you have cited. Yet, Excel stays because it is an invaluable tool to individuals. I would be very interested in a list of clear best practices to mitigate those risks, and which would have the IT professionals say: “when an Excel-based app is developed this way, it is okay to be deployed as a business solution”.
Good point!. What we need is a really basic testing framework – that alone would solve a lot of problems. Unfortunately Microsoft haven’t done anything really on that side.
A testing framework you say? I couldn’t agree more. http://www.rubberduck-vba.com/
Jordan,
A couple of specific points:
1) Aside from possibly demonstrating your manliness, there’s no utilitarian reason to choose C#.Net over VB.Net. The underlying framework is identical and you get the same power, libraries, sweet IDE, etc., with either. Plus with VB.Net you get the With/End With construct, which I really miss in C#. So, if you don’t mind the jeers of people who think C# “is just better,” feel free to use VB.
2) As others have mentioned, putting everything in sheet modules ignores addin development, which I understand you don’t do. I don’t know if you also eschew classes and userforms, but this sheet-module only tenet is clunky and limiting.
3) Sure, some people didn’t like the Ribbon at first. People have trouble with change sometimes. This isn’t limited to VBA/Excel users, and it doesn’t prove anything about our general willingness to enjoy new things. Look, for example, at how much we appreciate your very innovative rollover techniques.
4) Hungarian notation? Really, who cares. I can see its good points and bad, and use it accordingly. To hold it up as a cause, or symptom, of some larger problem is uncompelling at best.
More generally:
I don’t know if people consider me a developer, but they do have me do projects that harness VBA, Excel and other tools because they know I can put together something that will save a lot of time, eliminate a lot of mistakes, and do it pretty quickly. Many people, including some IT folks waiting for the bigger pieces of an enterprise system to coalesce, have expressed appreciation for these capabilities. They understand, with some help from one, what a good VBA/Excel programmer can do for them. And I like the personal scale and relatively fast turnaround of the work I get to do.
So far I don’t see how your “manifesto” is going to improve my situation as a programmer, or how it’s going fix the worldwide shortage of developers. Your main points boil down to: people were wrong to whine about the ribbon; people should stop using Hungarian Notation; and they should limit themselves to sheet modules. So far it’s more scolding than constructive.
I spend a lot of time answering questions on Stack Overflow, where I’m impressed both by the number of people teaching themselves VBA and the number of really good VBA coders answering questions. Some are young, some also know how to code in C, and some no doubt only know VBA, but there’s a lot of them and they’re not talking about Hungrarian Notation.
Speaking of Git integration tools, I’ve been working on one. It’s just a COM library at the moment, but I’m very close to releasing the beta of the VBE integrated version. http://www.rubberduck-vba.com/
The Rubberduck project aims, among other things, at helping VBA devs standardize how they code, through code inspections and code formatting/indenting and refactoring tools. It encourages explicitness everywhere: from Option Explicit to explicit ByRef and ByVal parameters, and explicit Public members; it encourages declaring variables closer to their usage, and provides tools to change the code that way. The “rename” refactoring leaves little excuse for Hungarian Notation to remain, and an inspection even encourages outright renaming 2-letter identifiers and others without a vowel, or numbered variables. We’re warning about implicit assignments to default members, and implicit references to the active sheet/workbook in Excel VBA. We encourage proper encapsulation, provide tools to make public fields private without breaking the code, and strive to promote best practices in general. If every VBA dev used Rubberduck, the VBA tag on Stack Overflow would certainly see much fewer dupes for “runtime error 1004 trying to get a range”, and people inheriting a VBA project wouldn’t cringe at the poor IDE tooling as much.
The website url is changing to http://rubberduckvba.com; in a few weeks the domain with the dash (linked by Christopher above) is no longer going to redirect to the new domain.