Printable View
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
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.
I like to use it too. (learned from Jakes code)
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.
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,
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?
:)
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
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
is immeasurably better thanCode:=IF(ISNA(myValue),"",myValue)
and that is a very trivial example of the power of range namesCode:=IF(ISNA(VLOOKUP(A1,$H$1:$M$100,2,FALSE)),"",VLOOKUP(A1,$H$1:$M$100,2,FALSE))
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
much more descriptive, maintainable, and flexible.Code:=A1*VAT
There is a very good paper on range names at http://www.xldynamic.com/source/xld.Names.html
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?
If a cell changes you only make that change in one placeQuote:
Originally Posted by xld
"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?
Which for the same reason may protect formulas for er .. witting usersQuote:
Originally Posted by xld
No. Its counter intuitive and a dangerous defaultQuote:
Originally Posted by xld
more descriptive yes - but only if the spreadsheet user is too lazy to write a text line explaining his/her logic flow.Quote:
Originally Posted by xld
more maintainable and flexible no.
If they don't know what they are doing they should not mess with it. If they do, the consequences are on their shoulders.Quote:
Originally Posted by brettdj
I might agree with you about that being pedantic, if only I understood what you meant by the tail-end of that sentence.Quote:
Originally Posted by brettdj
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.Quote:
Originally Posted by brettdj
Just have to agree to disagree on that.Quote:
Originally Posted by brettdj
You and me are going to have fun :).
And that is for another conversation, and quite off point.Quote:
Originally Posted by xld
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 ..).
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.
Cliched metaphors aside, can you give an example of an inappropriate use of a name.Quote:
Originally Posted by TonyJollans
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".
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
overCode:=getname(A1)
or evenCode:=VLOOKUP(A1,'[BC data.xls]4420 processed'!$A$1:$D$30,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.Code:=VLOOKUP(A1,'BC data.xls'!LookupTable,4,FALSE).
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
Sometimes, if names are used in the workbook you have to manage them in code, there is no practical choice. Rather than just throw out unsubstantiated statements, would you care to offer to us what these issues might be?Quote:
Originally Posted by firefytr
Interesting discussion... my view is pretty much in line with firefytr & TonyJollans' posts - I don't really see good or bad but rather try to make an informed decision on the best approach to a solution based on the specifics of the situation. That's good programming practice and if a named range is the right tool for the job (and it can be) then use one.
I'm going to vote 'Yes'. There may, however, be disadvantages which you have to consider in the context of their use. For example, user "interaction" may be a factor... unfortunately, saying people shouldn't mess with things they don't understand doesn't actually stop them from doing it., so caution is advised at the planning stage.
Absolutely right, but in a product like Excel that is so open to user 'enhancement' (for want of a worse word), we have to set some ground-rules when developing a spreadsheet that is to be used for any serious commercial purpose, and make it clear that if those ground-rules are ignored, they are either responsible, or they pay us big bucks to come and fix it (T&M!). How many decisions are being made based upon spreadsheets maintained by people who have no real idea of what they are doing (scary!)?Quote:
Originally Posted by Killian
We can make it as bullet-proof as possible, good error handling, protecting the code, hiding stuff that users don't need to see etc. My point is that names can help in this objective, if indeed it is an objective, and to rubbish that aspect as was done earlier shows an unenlightened view. And because it may take some effort on the part opf the developer to use them does not make them bad.
By posing the question, I assume that Aaron thinks they are bad, I would be interested in knowing why, or why not.
Actually, this poll is not EXACTLY worded the way I had intended... But, it's still interesting to hear your comments.
In my original post, I was thinking more along the lines of whether you think using defined range names in general spreadsheet modelling should be considered a best practice.
A lot of people suggest in their books and at universities that newbie excel modellers should be coached to use defined range names in developing spreadsheet solutions/models. Many (MANY) would suggest the concept of using defined range names in formulas is a "best-practice" technique.
I'm pretty sure it's listed as a recommended practice in the PwC doc (someone correct me if I'm wrong). Recently, I've also come across a university study that concludes the practice will "add value to companies".
What do you all think about that?