Excel Geeking: How I (Now) Name Range Variables

This one is gonna ramble a bit since there is (in my head) a lot of exposition to get through. It will probably also be a relatively useless one to Excel programmers, since everybody has their own preferences for variable naming conventions. But it’s my blog so I get to write about what I want to write about. So there.

Plus, I’ve been programming Excel for about seven years and this one just occurred to me within the last month. I never claimed to be quick.

When naming variables, I tend to follow what you might call “Hungarian Notation Lite”. You can read all about Hungarian Notation at the all-important-and-doubtlessly-true-because-it’s-on-the-internet cache of information known as Wikipedia. If you have read the most excellent book by Bullen, Bovey, and Green called Professional Excel Development, you’ll find their examples all use Hungarian Notation. They even have a section in one of the early chapters dedicated to proper naming conventions for all aspects of your Excel programming.

The type of Hungarian Notation that they use is what the Wikipedia article refers to as “system” notation, where the prefix of the variable name is represents the physical data type of the variable. What does that mean? It means that if you have a string variable called Message, then the actual variable name is prefaced with nomenclature that represents the fact that Message is a string variable. In this case, Message becomes strMessage, with the “str” representing the fact that Message is a string variable. Why use this kind of naming convention? Because when you’re reading through code you can quickly determine the Message is a string variable without having to stop and go back to the declarations to make sure you’re remembering it properly.

I use this method, but I use what I consider to be a “lite” version of it. If I have a string variable, I don’t type “str” in front of the variable, I just type “s”. In my world, strMessage becomes sMessage. Why do I do this? Part of it is laziness I suppose, I find it a pain to type three letters when I find one will do. Another reason is that I find it more readable. For some reason my eyes can take in an “s” and translate that to “string” better than “str”. My eyes stumble over “str” (or “lng” or “bool” or “rng”, etc.) and slows up the rest of my reading of the code. Cause, you know, I’ve curled up with a good multi-page printout of VBA code before and read it to relax. (Haven’t you?)

Okay, so to get down to the purpose of why I started this long-winded blog post, it’s about the range variables. Which I start with “r” instead of “rng”. (Get over it.)

I, like a ton of other Excel programmers, iterate through ranges in my code on a fairly regular basis. If you do anything that’s table-driven, odds are you’re using a For/Next loop to cycle through the ranges and find the values you’re looking for. To do this, I use the Range collection. How that works is that you set a specific range variable to something, then use another range variable to cycle through the first one. It looks something like this:

Dim rng As Range
Dim rCell As Range

For Each rCell in rng
Do something here...
Next rCell

Essentially what I’ve done is set up a whole range of cells to be the first variable. This creates all these cells as a collection housed within that variable. Then I can use the second variable to loop through the whole collection and evaluate each member of that collection. I look at the first cell, see if it meets whatever criteria I have to take further action, then move on to the next cell using the Next rCell line.

Here’s where I’ve been slow.

I’ve always used the following to name my variables:

Dim rng As Range
Dim rCell As Range

For Each rCell in rng
Do something here...
Next rCell

Where it gets muddy is when you have to iterate through multiple ranges in nested For/Next loops. It ends up looking something like this:

Dim rng1 As Range
Dim rCell1 As Range
Dim rng2 As Range
Dim rCell2 As Range

For Each rCell1 in rng1
For Each rCell2 in rng2
Do something here...
Next rCell2
Next rCell1

Yeah. No. That’s flippin ugly.

Yet this is how I did this until about a month ago when it dawned on me: why not name my range variables in the same way other collection are named? For example, to iterate through all the worksheets in a workbook you would use something that looks like:

For Each Worksheet in Workbook.Worksheets.

Brilliant Holmes! Now, if I’m looping through a range, my variables will look something like this:

Dim rSettings As Range
Dim rSetting As Range

For Each rSetting in rSettings

Much cleaner, much easier to read, and you can quickly determine which set of ranges belong to each other.

Only took me seven years to figure it out. Like I said, I’m kinda slow sometimes.