Range Names : Good or Bad programming practice?

[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! (That's why VBA took off and the Excel4Macros have been dying since 95.)

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

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

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


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

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

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

I use named ranges from VBA all the time, but usually preface all of my worksheet operations with a
[vba]With wsTarget[/vba]
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"?

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?

[Bob Phillips]

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

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