Sleeper: Range Names: Good or Bad?

[Aaron Blood]

Curious to hear your thoughts on range names.

1. Do you think defined range names in spreadsheets are good or bad?

2. Would you recommend defined range names as a modelling best practice?


Forgive the cross posting to: MrExcel, OzGrid, Vbax
(some of you just don't respond on the other boards and I don't want to miss the opinions of certain folks I respect on each)

[brettdj]

This may be better off as a poll, http://www.vbaexpress.com/forum/showthread.php?t=3083

1) Bad

- 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

2) No

follows (1)

Cheers

Dave

[Paleo]

1) Depends on the issue to be solved
2) Same as one

[Bob Phillips]

Aaron,

Surprised to hear you asking this, I thought you would already have had your opinion :) . As an aside, love your watermark solution.

Anyways ....

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

[Aaron Blood]

Aaron,

Surprised to hear you asking this, I thought you would already have had your opinion :) . As an aside, love your watermark solution.



Actually, yes I do have a very strong opinion on the subject. But, in this case, my opinion is not important. I want to hear the thoughts of others.

[Zack Barresse]

I voted No in the Poll by Dave. My two opinions? ...

Named Ranges ..
.. can go well in certain formulas, when/if used correctly.
.. do not belong on the VBA field.


Comments can be added if desired. :)