February 17

It’s time to say “Goodbye,” to Hungarian Notation


Most of us code using the Hungarian notation style when writing VBA code. Basically, that’s when you prefix an abbreviated description of a variables’ type or class in front of its name. For example, the ‘d’ below is a prefix for a variable of type double. You get the idea.

   1: Dim dDouble   As Double

   2: Dim sString   As String

   3: Dim cmdButton As CommandButton

I’m not really sure why Hungarian Notation has not yet been replaced—or, at the very least, discouraged for VBA coding. But here’s some speculation: 

(1) it’s a leftover from Visual Basic 6.0;

(2) because what has been put forth to replace it (specifically, Pascal or “CamelBack” Notation) looks like C/C++ and many VBA and Visual Basic 6.0 coders fear that C-languages are too overly complicated for them;

and (3) having a bad style guide is better than no style guide.

Let’s be honest: there’s really no value added with VBA and Hungarian Notation. It makes code ugly-looking and hard to follow (despite what we’ve been told). And, not every coder uses the same style abbreviations. Heck, even the list of abbreviations Microsoft recommends is too large to memorize. Think about this: Does cmbDisplayValues refer to a command button to display values or a combo-box of displayed values? It’s hard to tell—and I’m not the only one who thinks so.

Just take a look at the argument names of Excel’s internal object methods. Notice how virtually none of them use Hungarian Notation. They employee variable names like “Target,” and “Index.” How many times have you confused these names as referring to different types? Probably not that often, if it all. Certain keywords in variables names, like “Count” and “No” almost always refer to integers, so why not use them instead? I argue that you should use a descriptive name that imbues the meaning of the variable—nothing more. A variable called ProjectNPV is likely to be a double if it indeed refers to a net present value calculation for a project. You don’t need to put a ‘d’ in front of it.

Not convinced? Think about this Excel’s internal objects. What is the collection called that holds all the information about every series in the Excel chart? It’s called a SeriesCollection  and not cltSeries; ListObjects not lstObjects; Workbooks not wkbObjects. The point is made: if Microsoft doesn’t use Hungarian Notation anymore, then neither should we.

In the forthcoming weeks, I’ll be posting more on this subject. It’s time we changed the way we develop with Excel. Out with the old, in with the new. Let’s make 2014 the year of good code.

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • Absolutely agree Jordan. I think pre-prending a variable with some code to signify its data type is dumb in the extreme. Want to now what a a variable type is, select it, F2 takes you to that variable and you can see it spelled out, Shift-F2 takes you back where you were. There was a LinkedIn discussion on this recently, and the purists came yup with the usual clap-trap about pure Hungarian versus hybrid, completely missing the point that it’s unnecessary, it is ugly, it is obfuscating, and just so 70s. One guy even said he didn’t have time to mess about with ‘tricks’ like F2/Shift-F2, he was too busy maintaining hundreds of workbooks – it’s easy to see why.

    • It is so 70s. I don’t understand how there are still holdouts defending its use given that virtually all modern languages have abandoned it. In terms of coding in .net, Microsoft has actively argued against its use. I learned to code in QuickBasic while in secondary school–but then I grew up. Time for VBA developers to do the same.

    • I tried the F2 trick but it just opened the object browser. Instead what worked for me was SHIFT+F2 take you to the variable declaration, CTRL+SHIFT+F2 takes you make to the line of code. Personally I use hungarian about half the time, especially for strings and range objects. I find it makes it easier to decipher my code later on. I don’t use hungarian when programming in visual studio however because you can however your mouse above the variable name and immediately see the variable type, but the VBA VBE lacks such features.

  • Of course I agree with nearly everything you say, especially the sentiment. But I’m not going to change. I know that makes me a pig-headed Luddite, but I don’t care. Here are my counterpoints:
    I don’t do anything just because MS does it, so I reject that part of the argument.
    I can use keywords as variables, e.g. sType, bPrivate
    I can have two userform controls with the “same” name, e.g. txtSearch, lbxSearch
    I don’t find it ugly or hard to follow.
    I don’t expect, nor want, to convince anyone else to do it. I’ll be perfectly happy if I’m the last man on Earth to change. But there is *some* value added, however small. I accept that some think the cost exceeds the value.
    Having said that, when I code in ruby, python, or javascript, I don’t use use it, so maybe there is hope for me. I’ve been using it in VBA for a long time and I haven’t heard a compelling reason to stop. You’ve made some arguments, they just don’t compel me. I look forward to your future posts on the subject.

    • I mean, I won’t pretend to offer solid, inarguable points because I don’t think those exist. It’s a matter of creating a set of guidelines that are also flexible enough to ignore when the need arises. Old habits die hard, of course; in a pinch, I still sometimes use tmpObject for temporary objects or even vResponse for variants. My goal in this series is to argue for what I see as a more up-to-date development style. I know not everyone will agree. At the MVP Summit I posited we should stop stuffing modules with every procedure known to man but instead place sheet specific procedures inside their associated Sheet objects. Nobody agreed with that. (In fact, I wouldn’t be surprised if Bob didn’t agree with that–I maybe the only one with this position.)
      But that’s OK. I know I have all the work ahead of, and hopefully I’ll rise to meet the occasion.
      Anyway, I’ll address your points above because I expect others to make the same arguments. First, I’m not really saying we should or shouldn’t do something just to follow Microsoft’s lead. My point is two-fold, although I realize I didn’t argue it in full above:
      (1) Microsoft, for their own classes, has surely demonstrated they no longer see the value in it; and
      (2) by using Hungarian Notation, we’re essentially employing two different notations in our code.
      Is it good coding practice to refer to a SeriesCollection for one object and cltSeries for another? I don’t think it is. And if I must choose between the two, I’ll go for consistency and readability.
      Which brings me to point two. You could have variables SearchTextbox and SearchLabel instead, and there’d be no loss of fidelity. Just to drive this point home, I’m not really sure what the “lbx” is you used in your example above. It’s not listed on that big list of abbreviations I linked to above. I’m just guessing it stands for a label (maybe an ActiveX label…is that why there’s an ‘x’ in it?). In presenting the “lbx,” you’ve demonstrated my point that there is no common understanding as to what these prefixes communicate.
      Which brings me to the final point. You have to consider other folks who might look at your code. More important, you also have to consider someone else: you… six months from now. When I talk about code readability, I mean gleaning what’s going on most easily when you haven’t looked at your code in a long time. From my own experience, Hungarian Notation does not adequately encode the required information to make understanding your code in the future an easier process.

    • Ha, blnFound… well, I guess we shouldn’t trust all of Microsoft’s code.
      I’m letting some of the proverbial cat out of the proverbial bag by answer your question, but the simple answer is that I don’t deal with scope. If you place your procedures and perform most of the work in the sheet object, there’s very little need for publicly declared variables. All my variables are assumed to be private to the sheet’s in which they are used. I use modules only for public functions, APIs, and on the (very!) rare occasion that it makes more sense to store a procedure there. I never use more than a single module in a spreadsheet application, and neither should anyone else. In fact, modules should be used rarely, in my opinion. (Here comes the mob screaming off with my head!)
      Without a doubt there are some variables that should be accessed by other sheets. So how do I expose them across the workbook? I store them in Excel on a sheet (often called Calc, Constants, Information… something like that). And I use a named range to access them. I’ll go into more of why that’s advantageous in a later article, but for short description, see Principle 1 of this post: http://optionexplicitvba.com/2013/09/16/development-principles-for-excel-games-and-applications/

      • I don’t think my apps would be as maintainable if every thing was in 1 module and only used sheet objects as code storage.
        Do you still store code in sheet object when creating addins?

        • You know, I hadn’t thought about that. Last time I made an add-in was in 2008. I think then I had in fact stored information on one of the sheets, but even looking I wouldn’t encourage that practice… yet. Let me think about that one.

  • Very interesting topic of discussion. I’m not really familiar with other programming languages but it would be interesting to learn about techniques and best practices that other languages use.
    One argument for the Hungarian prefix is the ability to group all the variable types in the auto complete menu. For example, if I prefix all my string variables with “str”, then when I type “str” and hit Ctrl+Space Bar I get a list of all my string variables to choose from. It’s definitely faster than scrolling back up to the top of the procedure to find the variable name I’m looking for.
    It would be interesting to know how other languages handle this, and if there is a better way to do it. Might be a good topic for one of your future posts.
    To add to your list of speculation on why… I’m thinking that VBA programmers might be more organized than coders from other languages. I’m partly joking here, but I’m guessing most VBA programmers come from a finance/accounting background (myself included), and their desks tend to look a lot different than that of a game developer’s. Again, just speculation. Even though the prefix seems like extra work, it’s a way to keep things really organized. For some this might be excessive, and others will be driven nuts by not having the organization. Just ask my wife… 🙂
    We probably also get stuck in our ways a bit, and change isn’t always easy unless there are 18 million good reasons to do it. 🙂
    Anyways, I’m always open to learning something new and finding more efficient ways to complete the same task. So thanks for making me think!

  • I agree with Dick but with a twist. I never understood how I could be productive while typing 3 chars of a variable name (eg strName) just to get to the first letter of the real variable. But I could see the value of having some type indication – so I went with a suffix rather than a prefix (eg. Name_s). That made intellisence much more effecitve for me.
    Over time (it has taken me years) I have realized that many variables are sufficiently typed without the suffix (count, index, i, j, name) so for those obvious variables I don’t use the suffix.
    I do add a different suffix for local variables, module variables and global variables and having that as part of the name is critical for me (eg. doc_key_s, DocKey_ms, DocKey_gs)
    There is always a balance between productivity and self-documenting code and with suffixes I found the right balance for me.
    As to stuffing all subs and functions in a module, that makes no sense to me – grouping under common mod names works – I do write sheet specific subs/funcs in the sheet object, but I have many that are used outside of the sheets, so mods and classes is where they go. It really depends on how much code is required to meet the client’s needs.

  • “Want to now what a variable type is, select it, F2 takes you to that variable and you can see it spelled out, Shift-F2 takes you back where you were”
    See, all I have to do is look at the first letter. The fact that some coders use “b” and others use “bln” to signify a boolean is no great shakes. We’re all capable of recognising this type of small difference without pausing for breath (and if you’re really stuck then select it, F2 takes you to that variable and you can see it spelled out, Shift-F2 takes you back where you were).
    Yes, I freely admit that having been coding using various languages for 25 years I am a dinosaur, but there’s lots of stuff that MS does that I see no reason to emulate just because that’s the way they do it. And “ugly” vs “pretty” code doesn’t convince me – perhaps your aesthetic appreciation of code is that much more advanced than mine. On balance I think I’d lose more than I’d gain by changing but guess you pays your money and takes your choice. For the moment at least I’ll be staying with variable prefixes. I guess you can just thank your lucky stars you’re not maintaining my code. 🙂

  • Well, the “b” vs “bln” is really a tame example. The problems begin when cmd and cmb are used to refer to the same control, sometimes in the same code. For booleans in other languages, you might write something like “isBusy” instead of “bBusy.” IsBusy = True is definitely more semantic. If you’ve been doing something a certain way for 25 years though, you’re unlikely to see any improvement if you changed now (you’d probably see a detriment). You’re already used to reading something a certain way. That said, I still don’t see a reason why we continue to teach newcomers to use these old coding styles.

  • I’m a hardcore user of Hungarian Notation in VBA. The main reason I still use is because of Ctrl+SpaceBar shortcut. When developing in C# or VB.Net, there is no need to do this because the autocomplete features in code are much better.
    I even put mstr if it is module level, o g if public. Also, I use ‘a’ when it is an array.
    I define constants like gcstrProgramName.
    I agree that a code like [Profit] – [Taxes] is much more readable then my way writing Range(“Profit”).Value2 – Range(“Taxes”).Value2, but the only thing I say is the more I use VBA, the more purist I get.

  • I tend to advocate Jordan’s “lets look at the bigger the picture” view on this. Certainly got me thinking, today I was using a collection to store unique employees and then looked them up in a worksheet to sum up totals – i ended up using colEmployee and sEmployee (collection and string).
    After reading this I thought why didn’t I just use EmplCollection and EmplName or something like that. Personally I believe I use the type prefix out of sheer convenience – but when I look at the bigger picture this makes me lazier, less creative/descriptive with my variable names which in the grander scheme longer more descriptive names possibly could make it a little easier for the next guy looking at the same piece of code.
    I can see why most people won’t bother changing their ways but for me personally its food for thought and I do see value add in the principle.

  • I agree with you Jordan. I’m no longer a heavy user of VBA (especially with the advent of web apps!), working mostly with JS and .NET these days but the few times a month when I do go back to it I don’t even think about using Hungarian Notation because it’s not really used in any other languages and does look quite juvenile on the rare occasion I do see it used these days.
    That said, I don’t think this is the fault of the developer. The fault is with the IDE. In Visual Studio you don’t need to think about making your variable or function names meaningfully encoded with a type because intellisense will give you a useful precis of context as you type. I realise there hasn’t been an update to the VBA IDE in, what, well over a decade, but maybe that’s the step forward that would allow the industry to mothball the convention once and for all? =]

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

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.