Range Names : Good or Bad programming practice?

[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

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/Downlo...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 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!!!

Regards,
John

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

Regards,
John

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/Appl..._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/Appl..._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:

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

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:

[VBA]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 #####[/VBA]

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

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

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