Category Archives: Advanced Excel

A VBA Coding Manitesto (Part 2)

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.

 

 

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.

clip_image002

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).

clip_image004

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.

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.

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.