April 23

A VBA Coding Manifesto (Part 1)


A little while ago, I made a blog post on why I think we no longer need Hungarian Notation. Truthfully, it’s not the first time I’ve advocated for doing something against the grain. My blog on the principles for app/game development with Excel argues you should keep most of your procedures in the sheet object (versus a module), and you should use active objects (like ActiveSheet, ActiveCell etc) sparingly. At the MVP Summit, Roger Govier told me I should challenge everything. So I am.
This morning, the esteemed Excel expert, Dick Kusleika of Daily Dose of Excel, wrote a response post to my rant against Hungarian Notation in The Great Hungarian Debate. I started writing my response in the comments. But I’m terrible at brevity. So I’m going to post my response here. My response started small, but it ultimately became a manifesto. So I’m breaking it up into parts. This, of course, was my intention all along. However, I was still working on drafting the rest of series. Dick’s response has forced me to get it all out there, which I’m actually very thankful for.

Not that long ago, in the same galaxy as this one…

Let’s travel back in time to January 30th, 2007—the date Office 2007 was released. I remember I was working as a junior auditor in 2007 for a federal auditing agency. Near the end of the year, we upgraded our Office suite from 2003 to 2007.
For those who remember, Office 2003 was driven by menus and toolbars (above). Office 2007 brought a dramatic change replacing menus and toolbars with ribbons (Office 2013 shown below).
I vividly remember the response in my office to the upgrades. Nobody liked the change. Nothing was where they had remembered it. At the time, I was the Excel expert in the office (and likely Microsoft’s only defender of the new ribbons). Nobody really bought my arguments; that everything was easier to find, that we could really showcase our internal add-ins on a new ribbon (at the time, people in the office had trouble finding our internal toolbar). I had been there for only less than a year—so I was easily dismissed as a young pisher. The veteran auditors didn’t want to learn anything new. Once developers on the internet began releasing those ribbon add-ins that recreated the layout of Office 2003, many in our regional office flocked to them.
In 2007, if I had disparaged the new ribbon, it would have been in likeminded company. Could the same be said today in 2014? Would anybody be so bold as to defend that old menu system? Is there anyone reading this right now who will take up the mantle to breathlessly defend the layout of the old Office? Anyone? Now’s your chance.
Hopefully you see where I’m going with all of this. Visual Basic for Applications is a dialect of Visual Basic 6.0, which was discontinued by Microsoft in 1998. That’s more than a decade ago. Most modern languages have all but abandoned Hungarian Notation,
And while I greatly respect the opinions of the Excel experts and MVPSswho paved the way for my success—and whose work I owe a great debt of gratitude—the fact remains no argument presented so far has objectively addressed my points. If I may be so bold, I’ll sum up the typical arguments against the motion:

1. I can read my code so what’s the big deal?
2. I’ve been doing this for 25 years, if I changed now, I won’t be able to understand my code.
3. I’m stubborn, why should I change now?

If these arguments sound familiar, it’s because they’re fundamentally the same as the ones I heard from those veteran auditors. Nobody should fault those who prefer familiarity over change. It’s worth noting detractors of 2007’s ribbon were helpful to the Office product team. Therefore, I’m not arguing that everyone who is against change is fundamentally on the wrong side of things. My point is to demonstrate that problems persist with Hungarian Notation no amount of familiarity can overcome. And they same could have been said about 2007’s new system.
So let’s consider the idea of familiarity. Does familiarity address confusion over abbreviations between programmers? Again, a textbox in my code, might be txbInput. Yours might be txtInput. In my previous post, I presented code which showed such confusion. So I ask you: Which of these conventions is right? Moreover: What is the point of a coding convention if there isn’t ubiquity?
Near the end of his article, Dick argues

Another advantage of data type prefixing is being able to use reserved words. For my experiments, if I want to use a reserve word I’m going to tack on an underscore. When I want to code Dim lEnd As Long, I will instead use Dim End_ As Long.

Here again we have the problem that Hungarian Notation encourages: both lEnd and End_ are not good variable names (no offense). What does End_ do? As I argued in my original post, we need to focus on descriptive variable names. Hungarian Notation encourages the use of inelegant descriptors.
Hopefully, you now see what the resistance to go beyond Hungarian Notation means in practice: namely, we write code that only we can understand. But if the code works, how one choose’s to write it amounts to a hill of beans, right?
I don’t think so. If you want to keep doing what you’ve done in the past, well, that’s up to you. But hopefully we can agree it’s time to move beyond 1998. Even if we can’t bring ourselves to break old habits, we should encourage others not to do what we do. Yes, that may be hypocritical, but that’s what I meant by the “good bye.” It may be too late for us to change, but we should the lay the groundwork. We need to transition from an old model to a new one. I’m not the only one who feels this way. Rob Collie’s Modern Excel movement makes similar claims about moving toward modern development. Even as he and I might readily disagree on what that future ought to look like, the point is made: just because we’ve done something the same way for a long time does not mean it’s better.
Even as people complained back in 2007, there was an acceptance change was coming. Those ribbon toolbars, which mimicked the old layout, were pyrrhic victories at best. Eventually we had to accept the ribbon changes whether we liked them or not. This simple inevitability, I believe, is why few if any will still defend the old menu system today. Objectively, the ribbon toolbar was (and still is) better for a variety of reasons. But what most helped us see the light is that we had no choice but to accept it!
VBA isn’t going anywhere, right? At least, that’s the perception from the inside. But from the outside vantage point, VBA has already been left behind. So my views on why we should code differently are more than just preference. It’s a struggle for relevance.
And, in the next exciting chapter you’ll find out why.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)


You may also like

April 23, 2014

A VBA Coding Manifesto (Part 1)

May 3, 2014

A VBA Coding Manitesto (Part 2)

December 22, 2013

Excel Holiday Card
  • There is nothing wrong with Hungarian notation. I used it for more than 10 years and then I stop.. Yeah when I moved to .NET world (C#,VB) they look at you and think you are a stranger if you use it and I didn’t want to be. Of course, it took me almost 2 years to not mixing the name between the languages but since 2012 I do not use Hungarian just because I don’t want to confuse myself. It’s simple and it’s plain
    But as I said, there is nothing wrong with Hungarian notation I think it’s a matter of time in 10 years when young boys come to our VBA world with some experience with other languages, they will just say “Why?”

  • I suspect many are not purists one way or the other. I recently posted :
    Dim r As Long
    Dim c As Long
    r = rng.Rows.Count
    c = rng.Columns.Count
    Probably horrible naming standards but clear enough for a small snippet (I should functionize)
    Do you recommend well defined variables names when looping through the elements of a collection?
    For each ws in .Worksheets
    Next ws
    For each pt in .PivotTables
    Next pt

    • I go into more detail on loops in my Part 2 (which I’m still proofreading). The fact is, I’m guilty of using For i instead of For RecordIterator.
      You’re right about there not being purists. My goal with these thoughts is to present an alternate paradigm, not hard and fast rules. There probably exist a few examples where Hungarian Notation may prove more readable. I’m open to possibilities other than my own. But if we put Hungarian Notation up against other conventions, the other conventions are preferable for many reasons.

  • Well, yesterday I decided to try to break my VBA Hungary Notation rules because of your manifesto. I’m designing a quite big multiuser system that uses an Access backend and Excel frontend. I’ll share my experience when I finish the project.

  • Whatever my views about Hungarian notation, and prefixing more generally, the statement you make in …
    If I may be so bold, I’ll sum up the typical arguments against the motion:
    1. I can read my code so what’s the big deal?
    2. I’ve been doing this for 25 years, if I changed now, I won’t be able to understand my code.
    3. I’m stubborn, why should I change now? …
    is very lazy debating. I have heard very clear arguments for Hungarian that are far more reasoned than those statements, indeed you even quote one such from Dick later in your piece that is far more reasoned (you may not like it, but it is).

    • Yes, I agree those statements aren’t well reasoned, that’s why I chose to address them. But it’s not a straw man: all three are a version of an argument presented in the comments of the original article. A simple review of those comments – and comments made by Dick – and even in a comment made above – show that these three arguments commonly manifest in this discussion. Are you suggesting these arguments are inconsistent with those you saw in the LinkedIn debate?
      And yes, there are far more reasoned arguments. At no point have I attempted to suggest the three arguments presented are the only points in this debate worth addressing.. At no point have I suggested that Dick’s arguments weren’t well reasoned. If I have done these things, your welcome to show me where. There are indeed many well reasoned arguments arguments against the motion from people whom I greatly respect. I’ve even acknowledged difficulties in my own position in the comments section of the original article.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!