Convincing arguments for using local ranges in Excel over global ones

I believe that it may well be better to use Localised Range names in Excel over global ones. In Excel 2003 when an range is created using (what I will call) the Name entry box at the top left above the A column, I know that they are global. I understand it is开发者_运维问答 possible although in most cases improbable to have a Range locally on a sheet with the same name and this will cause very confusing behaviour in both Excel and also in VBA?

I have Googled about, without finding may articles about the subject ( I may have to blog one of my own after this). I wanted to know thoughts on what other Professional Excel developers do and why?

Opinions differ about the desirability of using local names.
On the one hand it is desirable to restrict scope as much as possible, so use Local.
On the other hand where you have duplicated local names (same name exists on multiple sheets) the possibility for errors increases rapidly, and if you don't have duplicated local names there is not much point in using them. Also it is then easy to inadvertently create a Global name with the same Name as a local name: which leads to confusing and error-prone (some would say buggy) Excel/VBA behaviour.
The freebie Name Manager addin (written by Jan-Karel Pieterse and myself) makes handling local names much easier by allowing things like easy conversion between local and global, improved visibility of Local Names, filtering, and flagging duplicate Global/Local names.
From a VBA perspective I rarely use them, but from a formula perspective there are occasionally times when local names are useful.

I think the global vs local issue depends on what kind of users you deal with and what kinds of solutions you build. Charles may work with solutions where the users are heavily invested in ranges and formulae; I am knee-deep in VBA code. There's a world of difference in the approaches required here. I wouldn't say Charles is wrong - he's probably right for the solutions he develops. But he's wrong for me.

I work with a lot of solutions that are modular that programmatically generate the same ranges on different sheets. I use names extensively because it helps to limit the use of hard-coded range references in the code and also provides a way of having generic solutions that function across different templates. I rarely create ranges using the standard range-dropdown next formula bar - creating local names there is a right pain in the mule.

The point Charles makes about global/local confusion does not apply to me because it is all programmatic and a mistake of that sort would be a bug and not a user frustration. They don't see these ranges.

In those instances where users are interacting with the names, well, I do wish Excel had signed global names in a different way to avoid user confusion. In my experience if I have defined the ranges, users don't get into trouble. They don't have to worry about local/global issues, because the names are already there.

Let's not get into the issue of range names which are relative versus absolute... that's another can of user confusion worms right there!

Your workbook is a business application and the sheets are modules. Think of it like any software problem.

You want to restrict the scope of your names as much as possible:

1. To minimize accidental and invisible misuse.
2. To avoid worrying about whether a name has been previously used.
3. To minimize the extent of the search for "What the H... changed this?"
4. Because it doesn't cost you anything and globals don't confer any benefit.

Cross-sheet references to local names are extra work and really unlikely to be accidental, so I have to disagree with Charles that duplicated local names increase the likelihood of errors.

There's a reason that when we invented structured programming, we also got rid of Blank Common. The rationale is equally applicable to spreadsheets.

One thing that can help make your spreadsheet models extra-robust: When you are thinking of declaring a global, or you're doing a cross-sheet reference, think about whether it would be better to create a function or a class. It's not always the case, but I've found it's often enough the case that I can reduce complexity by encapsulating "interesting" calculations.

It's a matter of opinion: different users will have different preferences.

I use global names because I want a convenient and stable label that identifies a range throughout the workbook - in formulae on any worksheet and in VBA processes throughout the entire project.

Essentially, I regard a named range as a global variable and an essential tool for stable cross-sheet processes.

Local names are for declaring different ranges on different sheets with the same name, and that's a recipe for confusion and errors. That being said, Joel Goodwin has supplied an answer with a very good reason for using local names: modular code (like a template generator) that does the same thing on different sheets. I've built template generators that do exactly that.

I'd use local names more if better interfaces were available to view them and use them in VBA - JKP's Names Manager gets a lot of use where I work, but it doesn't eliminate the confusion and errors that were designed-in by Microsoft's implementation.