Consulting

View Poll Results: Should the use of Range Names be recommended as good modelling pratice?

Voters
51. You may not vote on this poll
  • No

    13 25.49%
  • Yes

    38 74.51%
Page 1 of 7 1 2 3 ... LastLast
Results 1 to 20 of 122

Thread: Range Names : Good or Bad programming practice?

  1. #1
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

    Range Names : Good or Bad modelling practice?

    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
    Last edited by brettdj; 05-05-2005 at 05:05 PM. Reason: misleading title

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.

  3. #3
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    I like to use it too. (learned from Jakes code)
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  4. #4
    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.

  5. #5
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    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,
    Brandtrock




  6. #6
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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?

    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  7. #7
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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 vreasons


    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Contributor Glaswegian's Avatar
    Joined
    Sep 2004
    Location
    Glasgow, Scotland
    Posts
    196
    Location
    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?
    Iain - XL2010 on Windows 7

  10. #10
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Quote Originally Posted by xld
    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?

    Quote Originally Posted by xld
    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

    Quote Originally Posted by xld
    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

    Quote Originally Posted by xld
    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.

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by brettdj
    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.

    Quote Originally Posted by brettdj
    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.

    Quote Originally Posted by brettdj
    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.

    Quote Originally Posted by brettdj
    No. Its counter intuitive and a dangerous default
    Just have to agree to disagree on that.

    You and me are going to have fun .
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by xld
    Quote Originally Posted by brettdj
    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 ..).

  13. #13
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by TonyJollans
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    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".

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  16. #16
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by firefytr
    Programming with Named Ranges is a bad idea, too many issues.
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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.
    K :-)

  19. #19
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Killian
    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!)?

    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  20. #20
    VBAX Contributor Aaron Blood's Avatar
    Joined
    Sep 2004
    Location
    Palm Beach, Florida, USA
    Posts
    130
    Location
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •