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