Range Names : Good or Bad programming practice?

[Bob Phillips]

That's not entirely correct - you can name a array of constants.

Then that is not a constant, it is an array, which is a set of values/objects.

[brettdj]

Tony,

I don't know if you saw my earlier example about a user mistakenly copying a formula referring to a one cell named range and erronesly absolute referencing this cell. I've seen this happen more than once.

And if I can beat my drum one more time, range names add complexity for the end user.

The clever solution I referred to was a dynamic range chart. As you would have gathered by my posts to date, I'm not a big fan of imposing "clever me" techniques on others. In the business evaluation work that I do, good modelling is about good structure (same years in the same columns in different sheets), error checking (graphs, formulas checks, conditional formatting), full explanation (detailed notes rather than hidden comments), consistent cell formats for different intents (ie input area, formulas etc) and reduced complexity (avoiding linked files, macros, long winded IF formulas etc) as the model is not mine alone. Name ranges fall into the complexity pile.

Having dumped all that out there, if the model is for my use alone, I'll play to my hearts content. If I do use any code or links and the model has to go to someone else I ensure that they have a functioning copy without these attributes.

For me this debate reduces to who will be using the model. If its not just me then range names are to be used only as a last resort. I think that the poll results so far support this position, my guess is that a "good pratice" would be looking at a pass mark of at least 90% if not higher.

Cheers

Dave

[TonyJollans]

Then that is not a constant, it is an array, which is a set of values/objects.

If it's not variable, it's constant in my book

[TonyJollans]

Dave,

Yes, range names add complexity. So do the alternatives. So does every other feature. The question, surely, should be do they add unnecessary complexity. Sometimes they do, sometimes they don't. Use them when they don't.

I agree comletely that the target audience is a big factor. Who is it for? What are they to do with it? Who will have to amend it? etc. Again I would say, sometimes they're right, sometimes not.

I'm not sure that there's an answer to the simple question of whether they're good practice or not, as it has been posed; the debate is interesting but seems to miss the point somewhere. Is it good practice to use indicators when you're driving a car? Well, yes if you want to inform other drivers of your intentions and you actually indicate correctly but no, if you give the wrong indication or indicate at the wrong time. Using indicators adds complexity to driving (so you probably don't do it if you're in a field) but it reduces the chances of an accident out there on the road so you do it then even though it's extra effort.

[brettdj]

I reckon that name ranges are more like a sun roof than indicators, pretty, not integral to the operation and a potentially dangerous distraction

[Zorvek]

Dave,

This is really more a question of preference and desire than whether or not it is good practice. As with most subjective methodologies, it really boils down to how willing one is to accept and use the methodology and how much it is accepted in the community. As with the Hungarian notation that was the rage a decade ago, these kinds of things will ebb and flow. There is really no tangible benefit or cost, just what we want to make of it. You, for example, find naming individual cells "add no value but increase the chance of errors." I, on the other hand, find that it makes the formulas more readable and decreases the chance of errors. We are both correct given our respective views and habits, and the audiences we serve and how we serve them.

It is, in the end, only a level of indirection above direct cell references - something a seasoned software engineer welcomes but a novice finds confusing. Meaningful name or explicit name. We used to look at our variables as single or double bytes at address $A182. Now we just give them names. Today we often don't even care about the variable?s type (Variant.)

In the beginning I felt the same way about COBOL relative to BAL. But now the issue of whether to use and how much to use assembly or machine code has been rendered mute for a number of reasons and we readily, as a community, accept higher-level languages as the standard. Now the debate is over VB versus C++ and that is even being muddled of late with VB.NET and C#.

Can you imagine a worksheet environment where you can't use direct cell references in a formula at all? Where you HAVE to name everything? When I look at history and how we have progressed from BAL to COBOL to C to VB/C# I think I can see it.

Kevin

[Cyberdude]

Gee, Kevin, BAL, COBOL?? I'll bet you're a guy who has used PL/1 too. (I did.) Those were the days (sigh).

[Bob Phillips]

If it's not variable, it's constant in my book

Methinks you confuse nouns with adjectives .

[Bob Phillips]

Today we often don't even care about the variable?s type (Variant.)

We do, or we d*!#n well should. If we care about performance, code logic, or maintainability, we need to type our variables.

Next thing I'll hear is that it doesn't matter about declaring variables, as VBA will declare them at runtime!

[TonyJollans]

Kevin,

For the most part I agree with you and I like the part about maybe one day having to name everything - it does kind of continue the trend towards ever higher levels of abstraction (which perhaps only us old programmers can see ) but, like xld, I have to disagree about not caring about data typing - the trend is towards stronger typing; do you want to go back to S0C7 abends and the like?

Continuing your historical perspective, ever since I can remember there have been standards imposed on developers. Some of these were for good reasons and are still considered good practice. Oftentimes, though, installations restricted various language features for the simple (and stated) reason that they were too difficult to understand. Part of the reason for this was that demands for developers far exceeded the availability of skills and many developers barely understood what they were doing; commercial reality was the driving force.

I have never seen any comprehensive standards for Excel development, which in itself is interesting, but if there were any, I wonder whether the the same view would prevail? The prevalent corporate view seems to be that any idiot can do Excel (and certainly many idiots do!) but should there be standards, and are the commercial realities the same? And if so, what would be allowed? Or is there some fundamental difference between, say, Cobol and Excel?

[Aaron Blood]

(Small note: The deleted posts in this thread are a howl!!)

Oh maaaan! Did I miss some good ones?

[brettdj]

Dave,
This is really more a question of preference and desire than whether or not it is good practice. As with most subjective methodologies, it really boils down to how willing one is to accept and use the methodology and how much it is accepted in the community. As with the Hungarian notation that was the rage a decade ago, these kinds of things will ebb and flow. There is really no tangible benefit or cost, just what we want to make of it. You, for example, find naming individual cells "add no value but increase the chance of errors." I, on the other hand, find that it makes the formulas more readable and decreases the chance of errors. We are both correct given our respective views and habits, and the audiences we serve and how we serve them.
Kevin

I agree - its all about the audience.

When our group builds models we try to make the logic as transparent as possible. So rather than referring to cell A182 on sheet 'Inputs' I would copy a link to that row accross to each major section where it was used. So a user would see say four lines in Excel say, Saleable Product, Product Price, Foreign Exchange and CPI which are used to derive revenue rather than one long formula linking to several sheets. And there would be notes as to where the data was sourced, what date is was current from and an explanation of what the calc was doing. I think this is as "readable" as it gets as the major drivers are can be viewed together without delving into the formula and audit bar.

My perspective on this has been shaped by the work I now do. I used to build complex models for my use alone .... but when you are submitting or reviewing a billion dollar investment you want to be understanding how the inputs drive the valuation, not dicking about trying to follow the model architecture.

Named ranges are certainly not a big problem compared to poor layout, lack of comments, poor fill right procedures (I kid you not), complex algorithims, macros and linked files which lead to almost all the mistakes that we come accross on a project review. But in our modelling guidelines we don't recommend that people use names - the readability comes from good layout and notes as per my example above.

Oh maaaan! Did I miss some good ones?

Yep

Cheers

Dave

[XL-Dennis]

Hi all ,

The only answer is: Yes

What most people seems to miss is the need for system documentation of all kind of applications, which should always include a list of used range names and their contents.

The key to have a good start for maintance and further development is:
- System documentation.
- Well commented code.
- Overall / Detailed schemas.

Here I could provide the board with a nice VBA-solution to document range names but instead I suggest that some of the more frequent members write a KB-article covering it.

However, another close related issues is when to and how to apply range names. This may be subject to another poll / discussion.


Kind regards,
Dennis

[TonyJollans]

Dennis,

How nice to see you

And a couple of excellent points:

• No solution is complete without documentation, which should of course include details of any named formulae and their uses.
• The naming of ranges and the application of those names are different. This has been hinted at a couple of times in the discussion but not stated as clearly as that.

[Bob Phillips]

it does kind of continue the trend towards ever higher levels of abstraction

Tony, Glad to see someone agrees with me, I started to doubt myself (not) ). Just a small point, but not sure you are using abstraction correctly here, abstraction is the process of picking out (abstracting) common features of objects and procedures. I would go further and say that it is isolating those features into a separate object (a class - now there's a subject to get us going ) so as to make the rest of the application oblivious to those features, just know how to interact with that object via a strongly defined interface. What is being discussed here is feature of a product, not a development methodology, a methodolgy would equally apply to another product that does not have this feature.

I have never seen any comprehensive standards for Excel development, which in itself is interesting, but if there were any, I wonder whether the the same view would prevail?

I have. There was once such a beast produced by Price Waterhouse (IIRC) which was very good. Haven't checked it out in a while, but you can see it at http://www.eusprig.org/smbp.pdf. There is a good site with a few such links http://www.sysmod.com/sslinks.htm

[Bob Phillips]

Here I could provide the board with a nice VBA-solution to document range names but instead I suggest that some of the more frequent members write a KB-article covering it.

No need, Jan Karel Pieterse has already done it with his NameManager utility http://www.decisionmodels.com/downloads.htm

[brettdj]

Hi Dennis,

Great to hear from you

Yes, documentation is a necessity for a good model, it isn't an optional extra.

Take care

Dave

[TonyJollans]

Just a small point, but not sure you are using abstraction correctly here, abstraction is the process of picking out (abstracting) common features of objects and procedures. I would go further and say that it is isolating those features into a separate object (a class - now there's a subject to get us going ) so as to make the rest of the application oblivious to those features, just know how to interact with that object via a strongly defined interface.

Goodness, I'm not sure I want to digress to a discussion of English! Abstraction, surely, means making abstract, or conceptualizing. Using a name instead of a specific reference to a location (or value or formula) is doing that isn't it? The user doesn't need to know what the name refers to, merely that it returns the desired result - analagous to Class in many respects.

What is being discussed here is feature of a product, not a development methodology, a methodolgy would equally apply to another product that does not have this feature.

'tis true! What the discussion started out as was a feature of a product, but it has widened to include the development methodology behind the feature. There are two issues I suppose - the methodology and the product's implementation of it and I think that many of the negative comments have been about the product feature rather than the concept. Now, if that's so, how should Excel Names be improved?

[Bob Phillips]

Using a name instead of a specific reference to a location (or value or formula) is doing that isn't it?

Not to me it isn't, it is a difference between concept and implementation.

'tis true! What the discussion started out as was a feature of a product, but it has widened to include the development methodology behind the feature.

That is true, but I was simply referring to the use of the word abstraction here when I talked of features versus methodologies, not the overall discussion.

There are two issues I suppose - the methodology and the product's implementation of it and I think that many of the negative comments have been about the product feature rather than the concept. Now, if that's so, how should Excel Names be improved?

We are getting confused, the product does not implement a methodology, it implements a set of features and functions. The methodology relates to, or should relate to, spreadsheet modelling, not the product, or a feature of the product. It is perfectly possible, and legitimate if so decided, to have riules that say you do not use names in an Excel spreadsheet, but that is not methodology, it is procedure, and local procedure at that. The methodology would not concern itself with a specific feature.

I have no idea as to how to improve names as I have yet to hear of anything wrong with them. The two examples I have seen were poor programming/development in the first case, and a lack of understanding of another Excel function in the other. I keep hearing that names are bad, but no evidence of why names are bad, just plenty of general points about spreadsheet modelling (read that paper, I think you will get much from it ).

Of course, it is possible to give examples of problems, such as a worksheet is slow when it has 200 worksheets, 3,000 names, etc., etc. But does that make names bad?

And of course, every feature might benefit from further functionality, but IMO Excel have it about right in that most functions are relatively simple, but by mixing and matching, and if necessary by use of VBA, can be extended almost indefinitely. Just look at what we have done with SUMPRODUCT.

[CBrine]

Here's my view on named ranges. I believe that using them within a worksheet formula tends to lend some readablility to the formulas.
=Vlookup(A2,TotalsSheet!A1:B200,2,false)
is more difficult to understand then,
=Vlookup(A2,WageRate,2,false).
So just like a well named variable in VBA, you have a better understanding based on the name. Just by viewing the formula you know you are looking up WageRate.

The other issue is with Office 2003, have you ever tried to do a mail merge without a named range??? It's darn near impossible now.

As far as using named ranges in code, I wouldn't recommend it, not because you shouldn't , but just because in code you have so many better tools to work with then in worksheet formula's. Give me a dynamic range object any day over a named range.

Cal