Range Names : Good or Bad programming practice?

[brettdj]

see http://www.vbaexpress.com/forum/showthread.php?p=25053

my call

- too often people attach data to the top or bottom of a range name and it doesn't get picked up.
- People seem to like creating range names and then use OFFSET to reference cells from that range. Not pretty from an auditing viewpoint. An Index/Match combination is better


Cheers

Dave

[Jacob Hilderbrand]

I think that they are useful and should be used when they are convenient.

I use then with Validation and also when I have to work with a specific range that may be made up of a lot of non-contiguous cells.

For example if I want to clear a range of cells like A1, B5, C7, D3, etc. I would likely just make a Named Range, so that I could refer to that in my code.

Also if I use the same range in multiple lines of code or in multiple Subs I use a Named Range so that if I need to change the range I would only need to do it once.

[Paleo]

I like to use it too. (learned from Jakes code)

[Cyberdude]

I do a lot of charting, and defined ranges are completely nifty for use in specifying the Series formulas. If I didn't use them, I would have to spend a lot of time each month updating the Series formulas.
I do a lot of linking between individual cells in the 15 workbooks in my financial system. It sure helps to not have to care if I move some of these cells around for various reasons. The other workbooks don't know about the moves because they link to the defined names.

[Brandtrock]

Defined names, whether used for a formula or a range are both good and bad:

Good because:
1. Help readability of formulas
2. Keep inexperienced users from meddling in a formula
3. Keep inexperienced users from "ruining" a formula by adding rows or columns
4. Allow for easy changes to be made (i.e. only the definition needs to be changed if a portion of code in a sheet/model has to be changed later).

Bad because:
1. Auditing them is a real PITA.

Given that I have listed 4 good to 1 bad, you might get the impression that I think there is more good than harm done by using defined names. This isn't quite how I see it though. Careful consideration to the end user should take precedence over the good thing/ bad thing evaluation.

If the worksheet is only going to be used by novice or intermediate user (per your scale Aaron), then using them as a safe guard is probably okay.

The more complex the worksheet//model gets, the less likely that they are a good thing due to the complexity added to auditing them or debugging an error in a formula that uses them.

This can be somewhat mitigated by incorporating a map sheet into the worksheet (hidden or very hidden), but then it seems that you are saving time on one hand while adding work on the other.

If I'm "on the stand" and being given only good or bad as my choices, I'd have to vote bad since defined names are one of the things in Excel that can be learned easily but used inefficiently. Passing on a sheet or model with numerous defined names will eventually cause more difficulty than it avoided even if it is documented.

There may be exceptions to this (like using defined names for chart series or expanding pivot table ranges) but as a general rule I avoid using them in complex models and enjoy using them in simpler worksheets.

Just my 2 cents worth. Perhaps others who answer will change my views on this, but this is my gut feeling.

Regards,

[Ken Puls]

I think it depends on the situation, as all things do, but I am a huge fan of named ranges.

One of my biggest pet peeves with VBA programming in Excel is that if you refer to, say Range("E1"), and then insert a row before it... VBA is pointing to the new Range("E1"), which used to be "D1". When you name a range, that doesn't happen since the named range is dynamic. For that sole reason, I think that they form a HUGE part of good programming practice.

Can they get out of hand? Sure, but what can't?

[brettdj]

Another pet hate

You range name A1
In C1 you put = A1+B1 then you copy down
.... The reference to A1 is automatically hardcoded to the range name. Nasty.

Cheers

Dave

[Bob Phillips]

This is just a copy of my answer to Aaron in the first thread on this, but I am a huge fan of range names, so I am adding to the Yes vote, for the following reasons


Range names are not good, they are excellent, absolutely no doubt about it in my mind. They
a) make the spreadsheet more maintainable in that if a range changes, you only have to make that change in one place
b) they make the formulas easier to read. For instance

=IF(ISNA(myValue),"",myValue)

is immeasurably better than

=IF(ISNA(VLOOKUP(A1,$H$1:$M$100,2,FALSE)),"",VLOOKUP(A1,$H$1:$M$100,2,FALSE))

and that is a very trivial example of the power of range names
c) they can be used to protect formulas from change by unwitting users
d ) with worksheet names you can have the same name applying to different ranes on individual sheets
e) some solutions can only be achieved with range names.

The example given by brettdj is bad programming, not a fault of range names. If the range is dynamic, create a dynamic range name.

Also, range names can be used to setup constants in your worksheets. For example, I have a range name of VAT, with a RefersTo value of 17.5%, which means I can use formulae such as

=A1*VAT

much more descriptive, maintainable, and flexible.

There is a very good paper on range names at http://www.xldynamic.com/source/xld.Names.html

[brettdj]

a) make the spreadsheet more maintainable in that if a range changes, you only have to make that change in one place
b) they make the formulas easier to read. For instance
=IF(ISNA(myValue),"",myValue)
is immeasurably better than
=IF(ISNA(VLOOKUP(A1,$H$1:$M$100,2,FALSE)),"",VLOOKUP(A1,$H$1:$M$100,2,FALSE ))

If a cell changes you only make that change in one place

"Immeasurably better" is over the top. For some people they make it easier to read - others have no idea how to edit them and prefer a VLOOKUP. Range Names are not just a tool for the user, they are there for anyone who uses your workbook.

On a more pedantic point why would you bother range naming that a one-off VLOOKUP?

c) they can be used to protect formulas from change by unwitting users

Which for the same reason may protect formulas for er .. witting users

The example given by brettdj is bad programming, not a fault of range names. If the range is dynamic, create a dynamic range name.

No. Its counter intuitive and a dangerous default

Also, range names can be used to setup constants in your worksheets. For example, I have a range name of VAT, with a RefersTo value of 17.5%, which means I can use formulae such as
=A1*VAT
much more descriptive, maintainable, and flexible.

more descriptive yes - but only if the spreadsheet user is too lazy to write a text line explaining his/her logic flow.
more maintainable and flexible no.

[Bob Phillips]

If a cell changes you only make that change in one place

"Immeasurably better" is over the top. For some people they make it easier to read - others have no idea how to edit them and prefer a VLOOKUP. Range Names are not just a tool for the user, they are there for anyone who uses your workbook.

If they don't know what they are doing they should not mess with it. If they do, the consequences are on their shoulders.

On a more pedantic point why would you bother range naming that a one-off VLOOKUP?

I might agree with you about that being pedantic, if only I understood what you meant by the tail-end of that sentence.

Which for the same reason may protect formulas for er .. witting users

I was amazed to find that witting is actually in the dictionary , but I get your drift. But by definition, a witting user would know about that.

No. Its counter intuitive and a dangerous default

Just have to agree to disagree on that.

You and me are going to have fun .

[Zack Barresse]

On a more pedantic point why would you bother range naming that a one-off VLOOKUP?

I might agree with you about that being pedantic, if only I understood what you meant by the tail-end of that sentence.

And that is for another conversation, and quite off point.


Aaron: Excellent question! With so many great responses, it's hard to write up anything worth while. Here is my 2 cents ...

I mostly agree with Brandtock.

Named Ranges go best with worksheet functions.

Programming with Named Ranges is a bad idea, too many issues.


And that's about all I have to say about that. If you want further, I'll provide (don't want to add too much text here, I can only read so fast ..).

[Glaswegian]

I'm with xld on this. I tend to use them selectively, really depends on the scope etc of the project/workbook. In the past I've created a workbook to update an old version that contained a great deal of data, but only in selected cells or texboxes. By naming specific ranges, it was much easier to put data in the right place, even if rows had been inserted (as was often the case).

Maybe brettdj has had some bad experiences in the past?

[TonyJollans]

OK, my turn.

Range Names are very useful and generally good and I would recommend appropriate use of them.

I stress appropriate because they are just one tool in Excel's toolbox. If I try and screw in a screw with a hammer it won?t go very well ? that does not mean there is something wrong with the hammer and it does not mean I shouldn?t recommend the hammer for knocking in a nail. One should use the tool appropriate for the job in hand. The fact that some people don't know when or how to use named ranges is not an argument against them any more than the fact that some people don't know how to use Excel itself is an argument against Excel. Untrained users of almost anything are a liability.

[Bob Phillips]

Range Names are very useful and generally good and I would recommend appropriate use of them.

I stress appropriate because they are just one tool in Excel's toolbox. If I try and screw in a screw with a hammer it won?t go very well ? that does not mean there is something wrong with the hammer and it does not mean I shouldn?t recommend the hammer for knocking in a nail.

Cliched metaphors aside, can you give an example of an inappropriate use of a name.

[shades]

95% of the Excel work I do is for my own projects. I normally start developing the workbook with straight ranges. I troubleshoot everything with specific ranges to make sure it works, play with it some more and get the bugs ironed out, then I move to named ranges, especially dynamic named ranges.

On those occasions when I help someone else setup a workbook, I follow the same procedures, but walk them through the steps of using named ranges. Then I help them troubleshoot, and finally let them troubleshoot on their own.

Of course, I am not an Excel developer, I just get asked to help many others in the company "solve a little problem".

[mvidas]

I'm sure I'm part of the minority, but I generally avoid named ranges. In fact I'd say I don't use them unless I feel I have to! I will on occasion use them for SUMPRODUCT and VLOOKUP formulae, but that is really only if I am going to be giving that workbook to someone else, and they'd prefer to read "ColumnA" instead of "$A$2:$A$65536". And even then it really depends on how much I like the person I'm giving it to

Then again, I'd say I use VBA much much more than I have to, as I'd rather see

=getname(A1)

over

=VLOOKUP(A1,'[BC data.xls]4420 processed'!$A$1:$D$30,4,FALSE)

or even

=VLOOKUP(A1,'BC data.xls'!LookupTable,4,FALSE).

I still only have to change references in one place, and to me a UDF is easier to read on the worksheet and easier to trace via code. Especially with dynamic ranges--to people like us they're easy, but I don't really find it fun showing a relative novice how dynamic ranges work using built-in functions. But vba/non-vba is a different discussion altogether.

In summary
To me: range names = no no
To most everyone else: range names = probably
I understand the question is "Should the use of Range Names be recommended as good programming practice?" but you'll rarely, if ever, see me recommend naming ranges.
Matt

[Bob Phillips]

Then again, I'd say I use VBA much much more than I have to, as I'd rather see
=getname(A1)
over
=VLOOKUP(A1,'[BC data.xls]4420 processed'!$A$1:$D$30,4,FALSE)

You can use names to do just that, you don't need VBA. One of my points earlier.

[mvidas]

Then again, I'd say I use VBA much much more than I have to, as I'd rather see
=getname(A1)
over
=VLOOKUP(A1,'[BC data.xls]4420 processed'!$A$1:$D$30,4,FALSE)

You can use names to do just that, you don't need VBA. One of my points earlier.

I apologize if this is getting too off topic, I'll gladly start a new thread for this if anyone prefers, but how is that possible? I'll be the first to admit I don't know everything there is to know about named ranges, but I can't imagine how something like that could be done.

[Bob Phillips]

I apologize if this is getting too off topic, I'll gladly start a new thread for this if anyone prefers, but how is that possible? I'll be the first to admit I don't know everything there is to know about named ranges, but I can't imagine how something like that could be done.

Select B1, and then go to Insert>Name>Define... and in the Workbook Names box enter 'getname' (without the quotes), and in the RefersTo box enter something like
=VLOOKUP(A1,'sheet name'!$A$1:$D$40,4,FALSE)
and ok

in B1, enter =getname and it will look up the value] from A1. In B2 it will lookup the value from A2.

Works a treat.

Take a look at the paper I metioned, http://www.xldynamic.com/source/xld.Names.html, you might find it enlightening.

[Zack Barresse]

I apologize if this is getting too off topic, I'll gladly start a new thread for this if anyone prefers, but how is that possible? I'll be the first to admit I don't know everything there is to know about named ranges, but I can't imagine how something like that could be done.

You can in the same way you use old Excel4Macros to use things like the GET.CELL or GET.DOCUMENT properties and that, for reading things like formats in cells with formulas and such (w/o VBA). If I had to do this, I'd use VBA, b/c it's just so much simpler, faster to write and much easier to maintain, and the biggie - it's easier to show somebody how to do it! (That's why VBA took off and the Excel4Macros have been dying since 95.)

That being said, I wouldn't even think about using something like that, I'd use the native functions - far more efficient.

xld, do this to test with me (re: VBA & named ranges)

Open a new workbook (w/ at least 2 sheets)
Select Sheet1
Select any contiguous number of cells
Name it "test"
(or press Ctrl + F3 and manually define)
Select Sheet2
Press Alt + F11 | Insert | Module
Paste this code in the module ..
[vba]Sub testing()
Range("test").Activate
End Sub[/vba]
Press F5 to run and watch your error pop up
Press Alt + Q
Select Sheet1
Press Alt + F11
Press F5 - no error.


Now this can confuse the heck out of a newbie VBA progger! Think about this: In the worksheet environment, named ranges are good across sheets (aka tabs, so cross-tab), but not in VBA. Just like everything else in VBA (as you have pointed out, to your credit) it needs an explicit reference, uless you luck out and have that sheet as the activesheet. This would not be my idea of "efficient coding" or "best practices" in regards to coding etiquette. Much in the same sense that I would not assume the ".Value" should follow a range object reference.

As far as making a dynamic (worksheet{s}) list/ranges with Named Ranges, yup. Have done it and will continue to do so. Why? Because I can't match the speed with VBA.