Range Names : Good or Bad programming practice?

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

[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 DA220C230 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 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,

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

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:

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

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

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