Lori said:
That's true but there are several other issues with the defined name method:
And there are counterissues when using alternatives.
- Using a reference to all of the sheet is less efficient since the formula
recalculates whenever any change is made anywhere on the same sheet.
As also happens when using INDIRECT. However, formulas referring to
the entire worksheet don't recalculate when making changes in OTHER
worksheets, but formulas using INDIRECT still do.
There's no way to eliminate problems with row/column insertion/
deletion other than using INDIRECT or INDEX:INDEX.
- Dynamic ranges recalculate at startup, causing the save changes prompt to
appear by default when a workbook is closed (even if nothing has changed).
Same using either INDIRECT or INDEX:INDEX, but there's no way to avoid
this AND protect against row/column insertion/deletion. It's a trade-
off, but if macros were allowed, this could be addressed by something
like this.
Private Sub Workbook_Open()
Me.Saved = True
End Sub
- If you delete all cells on the sheet by right-clicking the top left corner
and clicking delete, the name will return #ref!.
Yes, and that's the ONLY way to affect references to $1:$65536 (Excel
97 to Excel 2003) other than deleting the entire worksheet. If you
have users that make a habit of deleting all cells in worksheets, you
have my sympathy, but in my experience this happens rarely enough that
it doesn't require special handling. But if you must guard against
this, once again event handlers to the rescue.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
If Target.Address(0, 0, xlA1, 0) = "1:65536" Then
MsgBox Prompt:="You may not select the entire worksheet.", _
Title:="No! No! No! Bad user!!"
Target.Cells(1, 1).Select
End If
Application.EnableEvents = True
Application.EnableCancelKey = xlInterrupt
End Sub
A simple way around might be to refer to a hidden sheet where no changes are
made but this causes additional overhead.
This is a good idea. There are usually many things that could and
should be stored in hidden worksheets, and the overhead isn't all that
much as long as you don't to any formatting in that hidden worksheet.
Another option is to use an active sheet name: =row(!$1:$256)
This is more efficient for recalculation and robust to row insertions, etc.
but can cause an error when copied between workbooks.
There are other problems besides copying between workbooks.
You could also try using a relatively defined name: =row(1:256)-row(1:1) but
this causes wraparound problems when you reach the end.
This is also susceptible to selecting all cells and deleting.
In summary all methods have drawbacks and the straightforward method often
works fine provided few changes are planned.
Using a hidden worksheet is the best of these possibilities. The
overhead should be negligible compared to the benefits.
