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

[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?

[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 ..).

[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]

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?

[Killian]

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.

[Bob Phillips]

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.

[Aaron Blood]

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?

[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.

[mvidas]

I have used that plenty before, but the way you said it, I thought you were referring to arguments in the named ranges like =getname(A1)
Using a named range like you just described works good for the one value, but if I have 30 values in A that I want to use a vlookup for, I don't want to have to make 30 named ranges
I'll take a look at that paper later on tonight, though. Thanks for the link

[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! :D (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 ..
Sub testing()
Range("test").Activate
End Sub
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. :D

[Ken Puls]

Zack, quick Q. Why is the naming of the range bad practices in your example. IMHO, this code example is bad code, as it isn't explicitly referenced, which is required to avoid the potential of error. :dunno

I use named ranges from VBA all the time, but usually preface all of my worksheet operations with a
With wsTarget
where wsTarget has been set to a sheet variable. It gives me the ability to use the named ranges to their advantages, without having to worry about a user inserting a line and shifting my target to operate on (with a hard coded range("B1") or whatever)

[Bob Phillips]

I have used that plenty before, but the way you said it, I thought you were referring to arguments in the named ranges like =getname(A1)


I did see such a technique once, but very unwieldy and non-robust as I recall,.

BTW, the argument is implicit, it is defined by the name definition.


Using a named range like you just described works good for the one value, but if I have 30 values in A that I want to use a vlookup for, I don't want to have to make 30 named ranges

No, because it uses as relative reference in the lookup value, it will shift according to wherever it is used. The trick is to define it in the correct cell.

[Aaron Blood]

I wish the poll didn't have the word "programming" in it. Venturing off on why/why not to use range names in VBA. Not really my initial intent.

But do continue, why is it Zack's example is good/no good?

OIC... can't activate cross sheets... yeah OK. Lots of things to trip up the newbies out there. Eventually the training wheels fall off and they pickup on the idea of selecting if they wanna jump to it. Generally it's a bigger step when they realize they probably don't even need to activate or select the range to act upon it.

I do recall a time when I once used defined names in a VBA app in a particularly devious manner. I used VBA to define the same name on every sheet of a workbook differently. I then had code to drop a formula on the active sheet that included the defined range name. Depending on which sheet the formula dropped on, it referred to different cells. Same formula on every sheet, but every sheet the name referred to something different. How you like that one?

[Ken Puls]

Fwiw, in my opinion, it's not a good example. How many times have you fought Excel's object model because you didn't reference something explicitly? It's happened to me quite a bit. (Less now that it used to as I learn.)

To me, the issue in his example seems more like poor programming as it leaves room for errors, rather than the programming issue being with the named range itself.

Try selecting a cell on a sheet when it's not active and you'll get an error. Is that a reason not to use select? Maybe. Is it poor programming to use select at all? Depends on the situation. But if there is a reason to use Select, the issue is not there, it's in the fact that the programmer did not activate the sheet first.

My thoughts, anyway. It's splitting hairs, really, but I don't think that Named ranges can be branded as the programming issue here. They are a tool, and that's it.

EDIT:
Aaron, I see you added something while I was typing! LOL!

[Ken Puls]

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?

To answer this part...

Was the range named "Synergy"? :devil:

A blanket statement like that without the context is hard to agree/disagree with, but if that's all it was, I'd say no. You have to know how to use a named range effectively. They'll cause confusion if not used properly, and novices could be tripped up on them. But then consider this:

Cell A7 holds =Sum(A1:A6)
Higlight row 7 and insert a row. The formula isn't indexed up, but the newbie will think it is.

There simply is no replacement for experience or training. Named ranges, formulas or whateverr are no different in that respect.

[Bob Phillips]

Now this can confuse the heck out of a newbie VBA progger!


But as kpuls says, that is because of poor programming, names are not the issue. I can show you a million examples of poor programming that can confuse experienced programmers, let alone newbies. It is not the function that is at fault here, it is the coding (and that is not a jibe at you, I understand you were trying to make a point :)).


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.

Well there we are just at odds, because I believe in explicitly qualifying, which is why I always do a .Value, use Me as an object reference, I never confine an If clause to one line, etc. etc. But I still haven't heard anything from you yet that shows me why names should be avoided in VBA.

[Zack Barresse]

xld: Exactly. It's just like Conditional Formatting with it's implicit referencing with the active selection.

Ken: Mostly for learning purposes I guess. As that's the majority of what I do, it's a large factor for me. Also as it's not as easy to refer to a named range in VBA (it's basically the same thing) it seems less useful to code. I do the same things in code sometimes, but I call them variables. (Example upon request. ;) )

Now I can definitely use these in worksheet functions. This is great, as stated, because they are generally faster than we can code in VBA (i.e. a UDF). There is a lot we can do with worksheet functions that we don't b/c we think it's too hard or can't be done. Heck, look what Aladin Akyurek does with them.

I guess I think if you are going to name a range, it's almost as easy to do it programmatically with Excel's Object Model. That being said there's about 200 ways to skin these cats, so getting into which is better (or god forbig why) is pretty useless (again, imho).

[Bob Phillips]

A blanket statement like that without the context is hard to agree/disagree with, but if that's all it was, I'd say no.

I think it would be relatively easy to disagree Ken. IMO there are very few such practices that add value (in themselves), more to the point, individulas or corporations who deploy such (IMO good) practices are likely to have the disciplines and structure that give rise to an environment where the IT function adds value.

[Zack Barresse]

Well there we are just at odds, because I believe in explicitly qualifying...

I think I was confusing on my statement. I was explicitly referring to the sample code. I shouldn't have even made the comment, as it's confusing.

Just ignore me altogether.

[Bob Phillips]

xld: Exactly. It's just like Conditional Formatting with it's implicit referencing with the active selection.

Zack, canb you use quotes please, there are so many conversations going on I am not sure which you are responding to here :). Are you agreeing or disagreeing with something I said?

[Ken Puls]

Just ignore me altogether.

xkd, how are we supposed to ignore Zack if you keep asking him to post stuff? :devil:

[Bob Phillips]

xkd, how are we supposed to ignore Zack if you keep asking him to post stuff? :devil:

Can't we just ignore him when he tells us to ignore him :*)

[Zack Barresse]

Are you agreeing or disagreeing with something I said?
Agreeing. Sorry.

Ken: Go to .. hell .. helsinki. ;)

[brettdj]

Actually, this poll is not EXACTLY worded the way I had intended... But, it's still interesting to hear your comments.?
I took the wording from your other thread. I think we could still edit the title if you like, the discussion has been broader than a programming discussion



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? Corporate doublespeak. I feel a mission statement coming on. Stakeholders anyone?

I think Brandtock summed up the pros/cons of range names very well - if a model is being designed for others then range names tend to obfuscate what is going on and the auditability is reduced. From my point of view that?s a bad thing. Other posters have indicated that this is desireable. It depends on what the intenet of the model is, or whether you are focussing on your preferences versus an endusers.

[johnske]

I don't want to join in this debate, I'm simply going with NO here - named ranges are one of the (VERY many) tools provided, as such we shouldn't 'focus' on any one of these tools and "recommend" its use.

If it's use is justified, in the sense "this is the only way to get around this particular problem", then by all means use it.

I mainly use VBA now and generally shy away from spreadsheet solutions where the use of named ranges may perhaps be better justified. Never-the-less I would use named ranges if I ever found a real use/need for them - but up to this point in time I've never needed to use them.

However, an objection to them is as a potential source of corruption for workbooks/spreadsheets. I've recently had to cope with several workbooks (from others asking for help) that contained many rogue range names.

These rogue names are impossible to delete and bloated the file-size of the corrupted books by several mega-bytes, the only way to get rid of them was to copy and paste the entire contents of the workbooks (+ code modules) to a new (uncorrupted) workbook.

John

[Aaron Blood]

I took the wording from your other thread. I think we could still edit the title if you like, the discussion has been broader than a programming discussion


:think: OK, but I never said programming. I said "spreadsheet modelling".

This thread has gone mostly down the path of exploring good/bad ideas related to using defined names in VBA. But, it is indeed pretty entertaining. Probably even a more interesting discussion than my original intent.

Programming best practice? ehhh... I think it's a looser topic with far more gray area. I'm pretty liberal when it comes to coding. (and not too liberal on anything else)

Change the title? I dunno, would any of you have voted differently had the poll been titled, "Range Names: Spreadsheet modelling best practice?

Granted, from the responses, I gather that there were already at least a few who voted that were already thinking in that sense.

[brettdj]

John,

NameMaster is a fantastic tool for deleting the bloated names. http://www.decisionmodels.com/Downloads/Name_Manager_32.zip

If you have a file that has been used for a while as the basis for generating new modules then running Name Manager can be quite an eye opener as to how much junk gets accumulated

As an aside, a sheet copy method, either manually or in VBA will replicate the names in the new book. This is normally useful as a copy and paste won't copy the names. But as per John's note the opposite effect is needed

Cheers

Dave

[Aaron Blood]

However, an objection to them is as a potential source of corruption for workbooks/spreadsheets. I've recently had to cope with several workbooks (from others asking for help) that contained many rogue range names.

These rogue names are impossible to delete and bloated the file-size of the corrupted books by several mega-bytes, the only way to get rid of them was to copy and paste the entire contents of the workbooks (+ code modules) to a new (uncorrupted) workbook.

John

Interesting you should mention that. Actually, there's a trick to killing the demon names. In TOOLS|OPTIONS if you toggle the R1C1 display format it will bring up this wonderful little window asking you to rename the rogues (believe it or not). Once given a suitable name, you can delete them at leisure.

[johnske]

Interesting you should mention that. Actually, there's a trick to killing the demon names. In TOOLS|OPTIONS if you toggle the R1C1 display format it will bring up this wonderful little window asking you to rename the rogues (believe it or not). Once given a suitable name, you can delete them at leisure.

Thanx Aaron :thumb didn't know that one - unfortunately (unless you could put it in a procedure {must look at that}) it wouldn't have helped in this instance cos there were around 8000 of them!!! :rotlaugh: :rofl:

Regards,
John :beerchug:

[brettdj]

I could have sworn I saw the word programming in your thread starter. But now that you mention it I can't see it

As the discussion has been about non-VBA and VBA I've changed the title to be clearer

Cheers

Dvae

[johnske]

BTW: Dave - thanx for the link, just downloaded name manager and I'll have to try it on a copy of one of those books (if I can find one now)

I think I remember looking for this at the time it was needed and all I could find was a 'pay-for' version - which was not an option.

Regards,
John

[Aaron Blood]

Thanx Aaron :thumb didn't know that one - unfortunately (unless you could put it in a procedure {must look at that}) it wouldn't have helped in this instance cos there were around 8000 of them!!! :rotlaugh: :rofl:

Regards,
John :beerchug:

Actually, yes I think it could be automated.


8000 named ranges.... You hear that!

Go ahead, keep telling the CPA grads how great they are. They'll use em.
I know, isolated case right? Happens everywhere!

[Kieran]

Ok I added my two cents worth to the Yes faction.
Basically I figure that things are generally simpler with named ranges.
Improved readability of code, of worksheet formulas etc.

Where ZI use named ranges badly in the past, it has been my error, not the use of named ranges that was bad practice.

[shades]

I use FastExcel (paid version) which includes Name Manager (from decisionmodels.com) to reduce file size. It automates even the unused named ranges.

One problem I find is that if there are data pulls from databases, it will add named ranges to the Excel file. I think most people assume that once you have deleted the names, they are gone. They are - until you refresh the data, and the named ranges come back.

[matthewspatrick]

Overall, named ranges are a Good Thing, but when misused they can be more trouble than they are worth.

Something I am doing more and more often is using dynamic named ranges. That defeats the "what if they add/subtract data" problem. Of course, having too many dynmaic ranges can hurt performance. I also recently ran into a surprising problem with dynamic named ranges:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21414425.html

Patrick

[patrickab]

I use range names extensively for my own work or for spreadsheets that are used as a fixed template for others to use. The reason is that when re-looking at them after some period of time it doesn't require much thought to understand how I had constructed it. So it's more for speed at a later date.

When creating spreadsheets that others might modify I tend to completely avoid range names as others may well be totally unaware of their existence and it tends to add confusion rather than enlightenment.

So perhaps not surprisingly my answer is yes and no, but in this context 'No', as ranges names are not too helpful to others and can be more confusing than helpful.

[Bob Phillips]

I also recently ran into a surprising problem with dynamic named ranges:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21414425.html


Patrick,

It is not so surprising, just think about what a dynamic range name. By virtue of the fact that you are using OFFSET, you are returning an array of values not a range.

The INDIRECT function accepts ranges or strings that evaluate to a range, such as A2:A100, but not an array such as {1,2,3}.

QED - the two do not go together.

[Zorvek]

Named ranges?been using them since they were invented. Only use direct cell references when no other option is available such as when reading other workbooks that are outside my control. Here are some thoughts:

Readability: As coders we refrain from using variables like i, j, and k and use more descriptive names for a reason, range names have the same benefit.

Flexibility: Range names adjust just like direct cell references in worksheet formula. But in VBA, that direct cell reference sticks like a fly to flypaper whereas range names just do the right thing no matter where they are defined. With named ranges and you can move them all over the place without impacting the formulas or the VBA code.

Auditability: Yes, there is a level of indirection one has to wade through...but we're smart are we not? Write an auditing function. I have one that marks all cells belonging to a named range with a red grid pattern. I save the previous grid and pattern settings in each cell's ID property so they can be restored with an undo mark routine. This function is managed from a non-modal dialog that has a list of all names in a drop down so I can quickly see which cells belong to a named range - it's actually easier to audit this way than just looking at direct cell references. There is also the paste list option from the Paste Name dialog. Still not happy? Use a routine to convert names to direct cell references:

Public Sub ConvertNamesToCellReferences()

Dim Name As Name
Dim Sheet As Worksheet

For Each Name In ThisWorkbook.Names
For Each Sheet In ThisWorkbook.Sheets
Sheet.Cells.Replace What:=Name.Name, Replacement:=Mid(Name.RefersToLocal, 2), LookAt:=xlPart
Next Sheet
Next Name

End Sub

Productivity: Ease of coding in VBA is the one single most important reason for using named ranges. If it wasn't for VBA I probably wouldn't be as adamant. One amazing thing about names is you can leverage their existence to auto-generate sheet initialization code and quickly take advantage of the sheet's class properties. One development aid I built looks at a sheet and produces a list of names as public variables along with code to initialize them. Result looks something like:

Option Explicit

' ##### Start of Generated Code #####

Public Caption As Range
Public CommandBar As Range
Public CommandBars As Range
Public Divider As Range
Public FaceID As Range
Public Level As Range
Public PositionMacro As Range
Public ShortcutKey As Range

Public Sub Initialize()

On Error Resume Next

Set Caption = Me.Range("Menus_Caption")
Set CommandBar = Me.Range("Menus_CommandBar")
Set CommandBars = Me.Range("Menus_CommandBars")
Set Divider = Me.Range("Menus_Divider")
Set FaceID = Me.Range("Menus_FaceID")
Set Level = Me.Range("Menus_Level")
Set PositionMacro = Me.Range("Menus_PositionMacro")
Set ShortcutKey = Me.Range("Menus_ShortcutKey")

End Sub

' ##### End of Generated Code #####

This instantly gives me access to all named ranges from within VBA as simple variable references, both from inside and outside the sheet module. I also have development aids that, with a hot key, create named ranges using headers over the current selection. Bottom line, I can take a sheet of data and with a few selections and keystrokes, have all my ranges named and sheet initialization code generated so I?m ready to start coding.

Paste Formula Problems: (This one is for my EE buddy Dave.) When you have a list/column of data, never name just the first cell. Always name the entire column excluding only the header. This way the formula:

C1=ColumnA+ColumnB

will copy/paste and Excel will do the right thing. With all due respect, the notion that this method is "counter intuitive and a dangerous default" is just plain wrong. Naming only part of a collection of data elements is counter intuitive and a dangerous default. There are many things in this world, and in Excel, that are counter intuitive...until one changes one's perspective. Dave, please noodle this a little before responding ? look at it from the perspective of a software engineer. If I ever need to name the first cell because I feel the need to use OFFSET instead of INDEX then I name it in addition to the whole column.

Need more? Dynamic named ranges. Love this range name formula for creating a menu from a variable list of elements (note the use of a full column range name to begin with):

=OFFSET(INDEX(Lists_Countries,1,1),0,0,COUNTA(Lists_Countries),1)

Kevin

[brettdj]

:)

Kevin,

As I posted in that EE thread I use range names when they offer a clever solution or an approach that is otherwise impossible, ie

- conditional formatting with a range on a different sheet
- dynamic range names for graphs
- XLM functions

But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errors - such as the hardcoding example I mentioned earlier

The "counter intuitive" quip was not from my perspective, it was from the average Excel user. In this case, the modeller was calculating discount factors for an NPV, and he managed to hardcode all the factors to the first year by copying through a range name. Rather justifiably, he thought that Excel contributed to his mistake.

I think this debate boils down to who will be using the range named model. If its for general release in formulae then for mine they are a firm no-no as they add complexity for the average user and increase the chance of errors. If its in VBA then its less of an issue as the VBA has probably already confounded the user enough.

Cheers

Dave

[Bob Phillips]

But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errors - such as the hardcoding example I mentioned earlier


That was naming a constant value, not a cell, and de facto a constant can only be a singleton. This is akin to assigning constant values to variables in code, actually a good practice. What sort of error can arise from that? Not a programming logic error, or spreadsheet logic error (i.e. human error), but an application error, beacuse if you mean human error, then that is nothing to do with names and can arise in any function, and thus can only be avoided by not doing anything.

BTW, it is a common misconception to call them range names, presumably because they are most often applied to ranges, but they are actually workbook or worksheet names. They can, as in my VAT example, have nothing to do with ranges.

[Anne Troy]

LOL, John! That would have been fun, huh?

Aaron said:
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.

And I recall something... stupid of me to forget... is it 2003? Or planned for a newer version? Where they are really souping up (or already did) the named ranges feature? Admittedly, I don't use 2003 enough to develop in (as if anything *I* do could be called development--put some red text in there!), so I haven't named many ranges...

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

[brettdj]

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

Aren't they just :)

[mgh_mgharish]

According to me, it is a good practice for an experienced user and bad practice for a new user.

A user will not understand where the range is and how the code is working unless he knows the range or he himself is the writer of the code.

SO IT IS A BAD PRACTICE:dunno

[Bob Phillips]

I use range names when they offer a clever solution

That is the kind of solution that is most likely to confuse the newbies that you express so much concern for!

[TonyJollans]

I use range names when they offer a clever solution ... I like 'clever' solutions but that is for my own amusement and I would not recommend them as best practice.



... or an approach that is otherwise impossible
Now that's a good point. Range names do offer solutions to otherwise intractable problems.



But when people use them to name single cells, such as XLD's earlier VAT example I think they add no value but increase the chance of errorsLeaving aside that it is not a single cell at all, I have to disagree completely here. Having, say, VAT as a constant does add value and REDUCES the chance of errors.



... and de facto a constant can only be a singleton ... That's not entirely correct - you can name a array of constants.


Finally, one point which I don't think has been mentioned so far. Range names can be helpful to users - they can go to a named range very easily without having to scroll aimlessly looking for information or work out what address to use and go to that address - and when they do so, they can see at a glance whether that named range contains what they expect.

[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:whip

[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 :think: .

[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 :yes) 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 :hi:,

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 :devil:) 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 :devil:) 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 :clap:).

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

[rberke]

I usually don't bother with named ranges in simple spreadsheets which will be thrown away in a day or two.



Almost all of my other spreadsheets rely heavily on named ranges. Especially useful for my a spreadsheets with dozens of columns. As far as I am concerned defined names greatly simply almost every spreadsheet task. For instance, I don?t like the way the columns are arranged, I?ll just cut column Q and paste it in front of column C, and everything including vba keeps working without any problems. (see my comment a little later about vlookup)





For instance, many of my worksheets have a header area followed by a detail area followed by a summary row then a trailer area. The detail area might have 20 columns in a database format. I would define

Hdr=1:14

all = 15:50

sumrow=51:51

trailer= 52:60

targetSales=A:a

TodaysSales=b:b

YesterdaysSales=c:c



I'll then make references to =Sum(all dailysales). Or perhaps =?the bottom line is? & range(?sumrow target?)

For the most part it works great.



Its also amazing how easy it is to sort thing using range names:



Edit goto all (I key alt g "all" <enter>)

data sort daily sales. ( I key alt d s "dailysales" <enter>



I don't ever touch the mouse for lots of these quick tasks.



Cell formulas become much easier like =if(sales>2*target,"great",if(sales>target,"good","bad))



These formalas copy down to the whole range very nicely.



I also have developed routines that allow copy chunks of 5 spreadsheets onto one spreadsheet with automatic resizing before print. That was a truly cool application because the copies automatically update and resize before printing Couldn't have done any of it without defined ranges.

I even use the defined field inside vba a ton. For instance the following seems very straightforward to me and allows me to reference spreadsheet cells very easily.



For r = 1 to 50

ActiveWorkbook.Names.Add Name:="curr", RefersToR1C1:=rows(i)

If range(?curr today?) < range(?curr target?) then ?.



Next r



If there are two or three detail areas, things works just as well.

AllSales=15:50

AllPayments=65:80

TodaysPayments=a:a.



Notice, I don?t worry about the fact that there are two different column names for a:a (TodaysPayments and TargetSales). It just all works itself out automatically. Sum (Allpayments TodaysPayment) gets one set of cells and sum(allsales targetsales) gets a different set.





But (Very rarely) it defines ranges do cause troubles:



Problem 1: I have to be careful when I insert a row at the bottom or top of a range. I sometimes solve that by inserting an placeholder row which is included in the range and is always empty. I make it two points high and all black shaded, then forget about it.



Problem 2: I have to avoid using Max and Min functions. If cell d20 contains =if(target < max(Today, Yesterday)>target,?Good?, ?Bad?) then it will be evaluated incorrectly like =If(a20< max(b1:b65000, c1:c65000), ?good?, ?bad?)



This surprising behavior only hits me with min and max functions, but I?m sure it could occur elsewhere.



But everything in excel has to be used carfully. For instance, a lot of people are in love with vlookup. But if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false).



I always use the match and index functions to avoid the vlookup problem. I would Name lookupkey=A:A lookupanswer=B:b and then use

=Index(lookupanswer,match(a2,lookupkey,false). Now I can enter a column anywhere I want and things work just fine.



Sorry to be so long winded, but you can see I very enthusiastic about named ranges.



In fact, I must say I was shocked to find so many people that don?t like them.

[rberke]

I forgot my other favorite vba trick:

Salesix=range("sales").column
targetix=range("target").colum
for r = 1 to 50
if cells(r,salesix) < cells(r,targetix) then....
next r

[Ken Puls]

...if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false)

Unless you use a "Match" function to return the column index. :)

[Aaron Blood]

But everything in excel has to be used carfully. For instance, a lot of people are in love with vlookup. But if you insert a column between A and B, you break formulas that say = Vlookup(A2,A1:B200,2,false).

I always use the match and index functions to avoid the vlookup problem. I would Name lookupkey=A:A lookupanswer=B:b and then use

=Index(lookupanswer,match(a2,lookupkey,false). Now I can enter a column anywhere I want and things work just fine.




It's the index in the VLOOKUP that prompts me to avoid using it in general. The regular ole LOOKUP function seems to be better suited to maintaining its lookup column point of reference. In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

[Ken Puls]

It's the index in the VLOOKUP that prompts me to avoid using it in general. The regular ole LOOKUP function seems to be better suited to maintaining its lookup column point of reference. In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

Maybe we should be starting a new thread on Lookup vs Vlookup? (Vlookup is my preferred method ;))

:rotlaugh:

[Ken Puls]

Hey everyone!

The timliness of this is quite amusing, I think. Just got Ozgrid's May Newsletter (http://www.ozgrid.com/News/named-ranges.htm) which, lo and behold, features Dave's opinions on Named Ranges! :giggle

[Aaron Blood]

Hey everyone!

The timliness of this is quite amusing, I think. Just got Ozgrid's May Newsletter (http://www.ozgrid.com/News/named-ranges.htm) which, lo and behold, features Dave's opinions on Named Ranges! :giggle


Dave,

To quote your newsletter (with regard to range names),
"Using them frequently is a very good habit to form."

I would say, perhaps, in the context as you present them. People in general say, "Yes, use them. They're great; and here's why..."


I think it's safe to say the largest crowd out there using spreadsheets are in the accounting/finance fields. In practice, when people in this field develop models, typically the models are not forever self-contained. In fact, most often sheets or modules of a model are copied/reused and moved from app to app. The pitfalls associated with this type of activity on an ongoing basis as it relates to prolific use of ranged names is seldomly if ever discussed when suggesting that people in general should use them habitually.

I would like to begin to see at least some precautionary statements associated with using named ranges in spreadsheet formulas rather than just telling people, "Hey, these things are great and you should use them a lot!"

I seem to be in the minority of excel developers (with some financial modelling background) who look further down the train track and notices that in the accounting/finance field range names have a tendency to lead to derailment. Unfortunately, at least in my case, it seems like I'm having to deal with the train wrecks after the fact on a more regularly occuring basis. Perhaps some of the wreckage could be avoided if newbie developers were given a headsup on some of the dangers instead of just the typical, "Here they are; go use em!" message.

Generally speaking... I say yeah, range names have their uses. I typically have 10-15 or so in my models used for just formula reference. Typically another 10 or so for VBA refs. I pre-qualify all my VBA only range names as "VBA_MyRangeName" so I know which ones relate to macros at a glance. When I work with new analysts on modelling fundamentals, I'm careful to point out that range names should be treated with extra caution. My experience has been that the newbie developers are unaware of the pitfalls.

[Ken Puls]

I think it's safe to say the largest crowd out there using spreadsheets are in the accounting/finance fields. In practice, when people in this field develop models, typically the models are not forever self-contained. In fact, most often sheets or modules of a model are copied/reused and moved from app to app. The pitfalls associated with this type of activity on an ongoing basis as it relates to prolific use of ranged names is seldomly if ever discussed when suggesting that people in general should use them habitually.

Those are some great points, Aaron. I would have to agree with you on all of them. :yes

[Cyberdude]

With all the conversation going on, I started getting a little confused about what's being called a "named range". I think the term range is what's bothering me. I thought you were talking about using what I call a "defined name", which is not restricted to a range. In fact all defined names are formulas, which may or may not include a range.
There is also the effect of using a column header as a name for everything beneath it. Is that perhaps what this is all about?? :bug:

[rberke]

Aaron, LOOKUP function requires that the vector or array be sorted. And, you can't tell it to require an exact match. The exact match is critical in most of my lookups, so I stick to the MATCH function.

Sometimes I'll use VLOOKUP for temporary spreadsheets, but never for something that is long lasting. It is too likely that I'll come back a month later, insert a new column, and not notice that I have broken the vlookup functions.

[Zack Barresse]

There is also the effect of using a column header as a name for everything beneath it. Is that perhaps what this is all about??
That is a label.



.. insert a new column ..
Inserting and deleting, imho, is bad practice altogether. The best solutions are those where we don't have to do this 'last resort' unless absolutely necessary. Imho, it should only be used during spreadsheet creation/development and no further. But this is conversation for another thread.

This is an interesting thread, isn't it. :)

[rberke]

Cyberdude, Perhaps I am using terminology wrong, but I have never seen a dictionary definition that pins these things down.

When I refer to Defined Ranges or Named Ranges, I really mean a particular type of Defined Name. It seems clearer than saying "a defined name that points to a range".

I have also seen people refer to dynamic vs static ranges. Most of this discussion revolves around static ranges. If someone wants to make the range bigger, they insert a row at the bottom, and the static range gets bigger.

Dynamic ranges are slightly different, if someone wants to make the range bigger, they simply key data into the next empty cell beyond the range. Like magic, the range gets big enough to include the newly keyed cell. No need to use the "insert row" function.

[rberke]

True, the column heading is a label, but it is wrapped up in Excel's "natural language" functionality. To a certain extent it is more flexibile than and eliminates the need for defined names. For instance these column headings:
north ............... South
Monday Tuesday monday Tuesday
1.........1..............3..........2
2.........1..............1..........1

Can be referenced as =sum(north monday) and would equal 3. This happens without defining any names! Also works with Row headings and combinations of both!


But, I have had some troubles when I tried natural language, so I stopped trying long ago. Even if it worked, it seems like magic to me, so I would probably never trust it.

What I have done instead is write a vbamacro that takes row 1 of my worksheet and generates the defined name for entire columns. The macro names the ENTIRE column, not just the detail portion. So it would say target=A:A instead of RealTarget=a15:a50. When I want to refer to the real target, I would say Range("AllSales Target").
Some people might dislike this approach, but it works for me. In particular, adding an extra row to the detail area, doesn't force excel to change the definition of all my defined column names.

[rberke]

That is a label.



Inserting and deleting, imho, is bad practice altogether.
firefytr: When I first started using excel I would have agreed. It seemed that every time I inserted, deleted, or moved a column things stopped working. Then I discovered named ranges, and stopped using vlookup.

Now, I love rearranging my spreadsheet to suit my needs. I even have a "worksheet A" that automates the rearrangment process, so I can look at the columns one way when analyzing past sales, and another way when I am anayzing product costs. I'll admit this macro originally rearranged the header area more than I would like. Then I figured out a great way to put the header information in another worksheet B and paste a linked picture at the top of my worksheet a. So rearranging the columns of worksheet A does not affect the header information.

But, I still agree that shared worksheet used by serveral different users, should not be rearranged too often unless you are willing to make a macro to return things to a default sequence.

[Bob Phillips]

With all the conversation going on, I started getting a little confused about what's being called a "named range". I think the term range is what's bothering me.

You are right, they are just called Names in help. They apply to a workbook or a worksheet. They do not apply to ranges, just are most often use to ReferTo a range.


In fact all defined names are formulas,

No, names can refer to a constant value.

[Aaron Blood]

Aaron, LOOKUP function requires that the vector or array be sorted. And, you can't tell it to require an exact match. The exact match is critical in most of my lookups, so I stick to the MATCH function.

Sometimes I'll use VLOOKUP for temporary spreadsheets, but never for something that is long lasting. It is too likely that I'll come back a month later, insert a new column, and not notice that I have broken the vlookup functions.

Really? Wow... hold on... Lemme go write that down.

OK then fine, index/match in place of a VLOOKUP, data sorted, exact match... blah blah blah.

More meant as a warning against hardcoding the col offset in a vlookup. I did once notice another interesting technique with the VLOOKUP though. Lemme see if I can remember it... involved using an anchored count or something like the following...

=VLOOKUP(G1,A1:E10,COLUMNS(A1:D1),FALSE)

I guess something along these lines would probably be safe, no?

Edit: HAH! look at that it coverted my range ref to a smiley... I'd change it, but I kind of like how the formula is laughing.

[rberke]

*1* It's the index in the VLOOKUP that prompts me to avoid using it in general

??

*2* In fact, I would think LOOKUP would be the preferred formula for those who like to name columns.

??

*3* =VLOOKUP(G1,A1:E10,COLUMNS(A:B),FALSE )





*1* I agree with completely:friends: .

*2* You have just retracted (albeit sarcastically) so we also agree on that.

*3* Been there! Done that! Doesn?t work! Column B can only be cut and pasted in the 5 column range. Go ahead -- just try pasting column B to the left of column A. Write it down!:creator:

[Ken Puls]

Okay, so if I can tie the vlookup and name thing together...

The attached workbook contains a table (named rngTblData), and the header row is also named (rngTblHead). There are two data cells (put in your year and a validation list helps you look up a value.)

The vlookup uses the match function to get the column index:
=VLOOKUP(B11,rngTblData,MATCH(B12,rngTblHead,FALSE),FALSE)

Insert a new column. It will still work, as the range is dynamic!

Seems to me that both vlookup and the named ranges work just great together in this instance. :devil:

[Aaron Blood]

*3* Been there! Done that! Doesn?t work! Column B can only be cut and pasted in the 5 column range. Go ahead -- just try pasting column B to the left of column A. Write it down!:creator:

True... true...
Now I can get back to hating range names... :rotlaugh:

[rberke]

Seems to me that both vlookup and the named ranges work just great together in this instance. :devil:

named ranges work fine with almost every excel function, including vlookup. (I discussed problems with max and min earlier).

But, I modified your example a little to show the problem with vlookup. Open the attached, and select profits column. Cut and paste it to the left of year column. Your formula breaks, mine continues to work. Mine uses index/match:
=INDEX(INDIRECT(B12),MATCH(B11,year,FALSE))

Of course, when I see how your application fits together, I believe it is one of those cases where vlookup is the best way to go. Aaron, you can now gloat!

Attachment is in following post on page 6.

[rberke]

Here is attachment

[Bob Phillips]

named ranges work fine with almost every excel function, including vlookup. (I discussed problems with max and min earlier).

I looked back at your discussionm on MAX and Min, and admit IU don't really understand your point. Can you explain it again?

On the VLOOKUP point you make, you could use dynamic indexes

= VLOOKUP(M1,A1:B200,COLUMN(B1)-COLUMN(A1)+1,FALSE)

then no need to use the (clumsier) MATCH...INDEX alternative.

[sandam]

I have an objection to make. There is no poll option for "I have no idea what a named range is and who its cousin VLookUp is" however I have put myself on the subscription for MrExcels free e-book so that I can teach myself a little more. :)

[johnske]

I have an objection to make. There is no poll option for "I have no idea what a named range is and who its cousin VLookUp is" however I have put myself on the subscription for MrExcels free e-book so that I can teach myself a little more. :)


:rofl: :rotlaugh: :devil:

[brettdj]

I think it's safe to say the largest crowd out there using spreadsheets are in the accounting/finance fields. In practice, when people in this field develop models, typically the models are not forever self-contained. In fact, most often sheets or modules of a model are copied/reused and moved from app to app. The pitfalls associated with this type of activity on an ongoing basis as it relates to prolific use of ranged names is seldomly if ever discussed when suggesting that people in general should use them habitually.

I would like to begin to see at least some precautionary statements associated with using named ranges in spreadsheet formulas rather than just telling people, "Hey, these things are great and you should use them a lot!"

I seem to be in the minority of excel developers (with some financial modelling background) who look further down the train track and notices that in the accounting/finance field range names have a tendency to lead to derailment. Unfortunately, at least in my case, it seems like I'm having to deal with the train wrecks after the fact on a more regularly occuring basis. Perhaps some of the wreckage could be avoided if newbie developers were given a headsup on some of the dangers instead of just the typical, "Here they are; go use em!" message.

Generally speaking... I say yeah, range names have their uses. I typically have 10-15 or so in my models used for just formula reference. Typically another 10 or so for VBA refs. I pre-qualify all my VBA only range names as "VBA_MyRangeName" so I know which ones relate to macros at a glance. When I work with new analysts on modelling fundamentals, I'm careful to point out that range names should be treated with extra caution. My experience has been that the newbie developers are unaware of the pitfalls.

Amen brother, Amen.

[charvin]

Hello , good discussion,

i love name, but I'm looking to retreive the cells address of a chart with VBA. Imagine the data source in DA220:DC230 and the chart is in A1 how could we manage to find this address with VBA (like we do by hand with the toolbar chart).
Any idees??
help will be welcome. :))

[ALe]

Is it true that calculation of formulas working on named ranges is faster?

[Bob Phillips]

Is it true that calculation of formulas working on named ranges is faster?

No significant difference that I can measure.

[Jan Karel Pieterse]

Hi Zack,

Being more or less considered the RangeName wizz, I cannot refrain from posting to this thread!!!



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. ;)

Of course the example you give is confusing because it (IMO) violates good VBA programming practice: qualifying the object you're talking to.

Using Range("test") is simply begging for trouble, whether you'd be using a range name or a cell address (so Range("A1") is equally bad), UNLESS you're deliberate about wanting to address the active worksheet (in which case you'll need a check to make sure a worksheet IS active!).

[Jan Karel Pieterse]

Thanx Aaron :thumb didn't know that one - unfortunately (unless you could put it in a procedure {must look at that}) it wouldn't have helped in this instance cos there were around 8000 of them!!!

I have a tool that fixes workbooks like that automatically.

[Jan Karel Pieterse]

Hi Kevin,


Public Caption As Range
Public CommandBar As Range
Public CommandBars As Range
Public Divider As Range
Public FaceID As Range
Public Level As Range
Public PositionMacro As Range
Public ShortcutKey As Range



Off topic:

I'd advise to stop using variable names that are identical to Objects, properties or methods. It'll get you into trouble.

[Jan Karel Pieterse]

Hi,

Sorry about the duplicate post, my Internet connection is on and off today.

My 2 cts now.

Range names are a great tool. If put to use with prudence. Aaron has phrased it well: too little people are aware of the pitfalls.

I use range names extensively when it comes to communicating with spreadsheets through VBA. On worksheets, I use them sparsely, mainly when I need dynamic ranges and when I refer to single cells that contain key parameters for an entire model.

I've written an article on my site about Range names: http://www.jkp-ads.com/Articles/ExcelNames.htm, which addresses the pitfalls too little I guess (Aaron did motivate me to add some about the problems).

I guess Charles Williams and my Name Manager is a must have utility for anyone using defined names more than once a year :-).

Not sure if you all are aware, but there is a beta of version 4 of the Name Manager available, which enables you to rename names (includes renaming in almost any object on your workbook and in your VBA code!). I have also added a feature which will show you all locations where a name is in use. I think if one makes extensive use of this tool (especially the last new option I mentioned can be most enlightening), a lot of the problems with defined names may be avoided or at least they become much more obvious.

[Jan Karel Pieterse]

Oh, I forgot to add a link to the beta:

www.jkp-ads.com/officemarketplacenm-en.asp

[Zorvek]

Jan,

I?ve been using variable names identical to object names for years and have yet to get into trouble. For the most part it's a myth that it does. Objects exposed in VB/VBA are almost always members of other objects and hence have to be qualified. If the name DOES conflict with an object name defined in the global name space then VB/VBA chooses the local name over the global one without error. Can it ever get confusing? Sure. But, at the same time, so can liberal use of objects defined in the global name space that imply ownership ? that?s where people get into the most trouble. Here are some rather obtuse examples illustrating just how predictable variable references really are. Both routines produce predictable results without fail:


Public Sub Test1()
Dim ThisWorkbook As Variant
ThisWorkbook = 23
MsgBox ThisWorkbook
MsgBox Application.ThisWorkbook.Name
End Sub

Public Sub Test2()
Dim Application As Variant
Application = 23
MsgBox Application
MsgBox ThisWorkbook.Application.Name
End Sub


Kevin

[Jan Karel Pieterse]

Kevin,

Although I guess you're right, I still disagree. I find using object/method/property names as variable names very confusing.

But hey, if you're happy with it, ignore me! I know I would :-)

[brettdj]

Just when this thread appeared to be dormant ......

Great to see you here Jan Karel, I've incoporated a copy of your Name Manager into a corporate addin that we use for auditing and debugging spreadsheets

While I am still firmly in the "no" camp I do want to pose a name range problem that has been bugging me. I posted this question some time ago at Experts Exchange but had no luck identifying a workaround

"I've built a workbook template (http://www.vbaexpress.com/forum/l) with indentical input sheets and I'm using hyperlinks to local range names for major sections (Revenue in A100, Capex in A364 and so on)

Capex ='Alt 2. 10|U|T|M - Mid Mine Plan'!$A$364
etc

All is going well until I rename a sheet, and voila, the hyperlink (http://www.vbaexpress.com/forum/l) is now invalid. Has anyone seen this before, and more importantly is there anyway around it? Global range names wth hyperlinks work fine with a sheet name change, Excel (http://www.vbaexpress.com/forum/l) just remaps like it normally does

Its an internal work example to be published on our intranet so code is out of the question"

Cheers

Dave

[Jan Karel Pieterse]

Hi dave,

Could you perhaps post a set of steps to repro the problem?

[mdmackillop]

In this question I discovered by accident that there is an "automatic" range name created using the column heading (Excel 2003). I can't replicate this in a new spreadsheet. Is there a setting to do this?
http://www.vbaexpress.com/forum/showthread.php?t=8417&goto=newpost (http://www.vbaexpress.com/forum/showthread.php?t=8417&goto=newpost)

[brettdj]

Sorry for the reply lag

Attached is a small example, there is a local name 'Daves Sheet'!Test at A1, a hyperlink linked to this range name in A4.

If the sheet name is changed from 'Daves Sheet' the hyperlink does not update the local range name change

Cheers

Dave

[Jan Karel Pieterse]

Yup, indeed the rename isn't working. It does work as expected with a globally defined name, but that doesn't solve your problem I guess.

I guess your workaround will need to be to either not use local named ranges in hyperlinks, or to rebuild the hyperlinks. Sorry, not much help I guess.

[macleanb]

There should be an inbuilt limit on the numbers of named ranges a user can create of 3. If he can fax Microsoft with incontrevertible proof that he's a seasoned and capable spreadsheet developer then he gets given one of those very long keys which he can use to unlock the number of names he can define.

[brettdj]

Yep. It's an annoying glitch which I will notify MSFT of. Now should I call it a Range Name bug or a Hyperlink bug :)