Nothing Like A Name

(stackoverflow rep: 6622, Project Euler 82/252 complete)

I was suddenly plunged head-first back into the world of Excel/VBA this week. After a year or so of mostly Ruby coding, I was struck by how, well, wordy VBA can be by comparison. There’s a definite lower level of expressiveness, too, although that can be mitigated to some extent with extra (mostly mental) effort. While I was rapidly back up to speed and having a good time refactoring to make room for some new functions, I couldn’t help feeling that I’d moved on and was practising what had become a legacy skill. That has happened before: I left COBOL behind (without a second glance, it must be said) in 1990 and have not written a line in it since.

Anyway, I was thinking about named ranges in excel, and how they aren’t. Named ranges, I mean. Well they are, but they’re not. A name can refer to a range, but it’s really referring to a formula that references a range.


And by formula, I mean anything (pretty much) that can be input into a cell after an equals sign. The result need not be a range. Want to define a named constant, Faraday’s Constant (96485.3399) say? Just define a name with “=96485.3399” as the “Refers to” value.

In older Excels, you’d get this:

Named constant - in a Name

Named constant - in a Name

I’m a huge fan of using names to define OFFSET range for charting:

SwimDateCount is also a formula...

SwimDateCount, in turn, is =COUNT(SwimStats!$A$2:$A$500)

The excellent Professional Excel Development (I have the older edition; I’m sure the new one will be just as good when I finally start working with 2007/2010 in anger) covers this, I’m sure. I’d check, but it’s been borrowed by a colleague. Because it’s that good.

The only slightly less excellent Excel Hacks definitely does cover it. (Also borrowed, but I managed to find it). Hack #52 in my copy, if you’re interested.


3 Responses to Nothing Like A Name

  1. Jon Peltier says:

    John Walkenbach’s been calling them Named Formulas for a long time (see A Range Name Is Really A Named Formula on the Spreadsheet Page blog).

    If you use names frequently, you should use the excellent and free Name Manager by Jan Karel Pieterse. It is what the Excel define names dialog should be.

    I didn’t think Excel Hacks was slightly less excellent than PED. I thought it was way less excellent than almost any other Excel book I have ever seen. Some of the “hacks” were okay, if reused, but a few were dangerous (particularly dealing with topics like macro security).

  2. Pete Loggie says:

    The guy above took quite a lot of words out of my mouth. Excel Hacks contain a couple of interesting tips. But Bullen, Bovey and Green’s masterpiece is the best computing book I’ve ever bought.

    Walkenbach calls everything a Named Formula to emphasise the most powerful use of names. If you’d had Bullen et al to hand you’d have seen that they call everything a Defined Name, and then specify the three types of names: Named Constants, Named Ranges and Named Formulas. This is arguably more accurate than calling everything either a Named Formula or a Named Range. If you look at the Excel screenshots, nowhere does it claim that you’re dealing with Named Ranges or Named Formulas. It just says ‘Name’.

  3. I wrote a series of articles on named ranges a couple of years ago, see:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: