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.
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.
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.
Make Your Excel Spreadsheet into a Bitmap Image With This Excel Magic Trick
Excel Data Visualization: Presidential Approval Ratings with Slicers & Power Query – Chart Tricks
Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54
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