Excel Sort Command And Named Range Scramble Prevention

Here's a macro framework that prevents Excel's Sort command from scrambling the addresses of single-cell named ranges located in the sorted range which is a troubling problem mentioned by Microsoft in KB58245.

PROBLEM STATEMENT

If you are like me you have been torn between taking advantage of the many benefits of working with named (or 'defined') ranges in Excel and the hazards associated with using them. Hazards? Well, here are two simple examples of how things can go wrong with named ranges.

Example 1: You are working on a financial analyis model for one of your customers. One of the cells contains the interest rate. You decided to name the cell you stored the interest rate in 'Interest Rate' to make all formulae using that reference immune to changes your customer might make to the workbook, like adding or deleting of rows or columns and such. It all worked as designed... until your customer decides one day to sort a range of cells that happens to contain the 'Interest Rate' cell, and voila, your workbook solution produces erroneous results.

Example 2: You used named ranges as hyperlink targets for the navigation structure of your workbook to make sure the hyperlinks will point to the intended cells even when you insert or delete rows. One day you decide to sort one of the worksheets containing some of your named ranges... just to realize a couple of days later that some of your navigation links no longer point to the intended cells.

What's the reason for this undesired behavior?

Well, according to Microsoft's support web site:

'Defined names refer to a cell reference and not to the cell contents specifically. When performing a Data Sort, you are only sorting the contents of the cells. Because of this, a defined name may not be referencing the same data after a sort.'

Although Microsoft's support wet site doesn't list Excel 2003 and Excel 2007 as products this problem applies to, as far as I can tell the problem still exists in Excel 2007 (please check it yourself and send me a note if I am mistaken). I have been able to create a fix for this undesireable behavior, at least for single-cell named ranges, and would like to share it here.

BRIEF SUMMARY OF THE PROPOSED SOLUTION

Since Excel's Sort command moves all cell contents according to the requested sort order, including any comments associated with each cell, the opportunity presented itself to use the cell comments to mark which cells in the selected range were single-cell named ranges. Since this needs to be done PRIOR to the completion of the sorting it is necessary to intercept the standard Sort command. Doing this turns the sort command into a custom sort for any range that contains at least one single-cell named range. Consequently, but for those cases only, the standand undo command needs to be replaced with a custom undo procedure which I decided to have invoked by the CTRL + Z keyboard shortcut. For this undo architecture to work the selected range need to be saved PRIOR to the execution of the sort command. To keep things simple I decided to temporarily add a worksheet during the Workbook_Open event and copy the selected range to that worksheet.

DETAILED DESCRIPTION OF THE SOLUTION

I uploaded a demo workbook incorporating the solution I am proposing. The demo workbook contains three VBA modules: ThisWorkbook, CustomSortMacros and CustomWorksheetBehavior. The latter modules contains macros required for the demo to work as intended. In the following I will discussing just the key features of the ThisWorkbook and CustomSortMacros modules.

- Event Handlers in the ThisWorkbook module:

The event handler has two function: i) it displays an alert informing the user of the presence and purpose of the CustomSort macros, and ii) it creates a uniquely-named ("UndoSheet{n}" (where n is used to distinguish from pre-existing worksheets with the same root name) hidden worksheet required by the CustomSort routine to store selected ranges prior to the execution of the sort command and thereby enabling a simple custom undo procedure.

The Workbook_Activate event handler redirects Sort menu command and the CTRL + Z keyboard shortcut by way of the OnAction and OnKey method, respectively, by calling the ActivateCustomSort routine which is declared in the CustomSortMacros VBA module:

Public Sub ActivateCustomSort()
Application.CommandBars("Worksheet Menu Bar").Controls("&Data").Controls("&Sort...").OnAction = "CustomSort"
Application.OnKey "^{z}", "CustomUndo"
End Sub

The Workbook_Deactivate and Workbook_BeforeClose event handlers revert this redirection back to the default by a call to the DeActivateCustomSort routine which is also declared in the CustomSortMacros VBA module:

Public Sub DeActivateCustomSort()
Application.CommandBars("Worksheet Menu Bar").Controls("&Data").Controls("&Sort...").OnAction = ""
Application.OnKey "^{z}", "DefaultCTRL_Z"
End Sub

with DefaultCTRL_Z declared in the CustomSortMacros VBA module:

Sub DefaultCTRL_Z()
On Error Resume Next
Application.Undo
End Sub

The Workbook_BeforeClose event handler deletes the hidden worksheet that was created by the Workbook_Open event handler.

A global parameter, customSortWasInvoked, is used to control the response of the CustomUndo routine. This parameter is set to True when the CustomSort method is invoked on a range containing at least one single-cell named range. The parameter customSortWasInvoked gets reset to False by the standard Workbook_SheetSelectionChange event handler when the user selects a different range.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
customSortWasInvoked =
False
End Sub

- Code in the CustomSortMacros VBA Module:

The CustomSort is routine executed the following steps:

- Step 1: It disables the application event handling to ensure that customSortWasInvoked parameter does not get reset by the Workbook_SheetSelectionChange event handler during the execution

- Step 2: It copies the selected range to the hidden backup worksheet

- Step 3:
- If no named ranges are found in the selected range the routine proceeds to display the Sort dialog.
- If the selected range contains multicell named ranges the user will get alerted that these named ranges might loose their intended meaning when proceeding with the Sort command and be given the option to cancel
- If the selected range contains single-cell named ranges the routine proceeds to append the name of the named ranges to the comment of each of those cells (more about that below), and then proceeds to display the Sort dialog.

- Step 4: It enables the application event handler

Here is the complete code:

Public Sub CustomSort()

If TypeName(Selection) <> "Range" Then Exit Sub

Application.EnableEvents = False

Dim currentSelection As Range
Set currentSelection = Selection

Call AlertToMultiCellNamedRange(MultiCellNamedRangeInSelection)
Call MarkNamedRangesWithRangeNameInComment
customSortWasInvoked =
Application.Dialogs(xlDialogSort).Show
If customSortWasInvoked Then Call UpdateNamedRangeRefersToProperty
RemoveNamedRangeNamesFromCommentsInSelection
currentSelection.
Cells(1, 1).Select
currentSelection.
Select

Application.EnableEvents = True

End Sub

The MarkNamedRangesWithRangeNameInComment routine first makes a copy of the selected range, appends (and thereby leaving pre-existing comments unchanged) the name of the named ranges to the comment of each single-cell named range and finally stores the name and the current address of each single-cell named range in the NamedRanges() array of Type NamedRangeInfo defined as:

Type NamedRangeInfo
rangeName
As String
rangeBeforeSort
As Range
End Type

Private NamedRanges() As NamedRangeInfo

Sub MarkNamedRangesWithRangeNameInComment()
ReDim NamedRanges(0)
If NumberOfNamedRangesInSelection = 0 Then Exit Sub

BackupSelectionForUndoOperation

Dim rName As Name
For Each rName In ActiveWorkbook.Names
If Mid(rName.RefersTo, 2, InStr(rName.RefersTo, "!") - 2) = ActiveSheet.Name Then
Dim iRange As Range
Set iRange = Intersect(Range(rName.Name), Selection)
If Not iRange Is Nothing Then
'Restrict to single-cell named ranges
If iRange.Cells.Count = 1 Then
Call AddToNamedRangeArray(rName.Name)
Dim currentComment As String
Dim c As Comment
Set c = Range(rName.Name).Comment
If Not c Is Nothing Then
currentComment = c.
Text
Else
currentComment =
""
End If
Range(rName.Name).ClearComments
Range(rName.Name).AddComment currentComment & "|" & rName.Name
End If
End If
End If
Next rName
End Sub

Once the Sort command has been completed CustomSort routine ensures that the RefersTo property of every single-cell named-range cell whose value was relocated by the Sort command is reassigned to the post-sort address by invoking the UpdateNamedRangeRefersToProperty routine:

Public Sub UpdateNamedRangeRefersToProperty()
If NumberOfNamedRangesInSelection = 0 Then Exit Sub
Dim rCell As Range
For Each rCell In Selection.Cells
Dim c As Comment
Set c = rCell.Comment
If Not c Is Nothing Then
Dim strComment As String
strComment = c.
Text
Dim nRange As String
nRange = NamedRangeInComment(strComment)
If Len(nRange) > 0 Then
Dim newRefersTo As String
newRefersTo =
"=" & ActiveSheet.Name & "!" & rCell.Address
ActiveWorkbook.
Names(nRange).RefersTo = newRefersTo
rCell.ClearComments
rCell.AddComment (strComment &
"|" & nRange)
End If
End If
Next rCell
End Sub

The CustomUndo routine reroutes to the standard Application.Undo method if it was invoked without prior execution of the CustomSort method (customSortWasInvoked = False). Otherwise it simply copies the pre-sort selection that was copied to the backup worksheet to the current selection and then assigns the pre-sort RefersTo properties to the single-cell named range cells in the selection using the contents of the NamedRanges array:

Dim i As Long
For i = 1 To UBound(NamedRanges)
ActiveWorkbook.
Names(NamedRanges(i).rangeName).RefersTo = "=" & ActiveSheet.Name & "!" & NamedRanges(i).rangeBeforeSort.Address
Next i

CONCLUSION

The solution I am proposing here seems to be effective in reassigning single-cell named ranges affected by Excel's Sort command. Workbooks modified with this solution will maintain the values of single-cell named ranges (and hence the results of all formualae referencing those named ranges) and render hyperlinks to single-cell named ranges impervious to Excel's sort command.

By Rolf Jaeger   Popularity  (6987 Views)