XL97: Problem Using Sort Method with More Than One Key (229107)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q229107

SYMPTOMS

When you attempt to programmatically sort a range using more than one key, all but the first sort key is ignored. This problem occurs when you call the Sort method using positional arguments, which is common with automation. The problem does not occur if you used named arguments.

CAUSE

According to the Excel type library and the Excel VBA Help file, the Sort method has the following syntax: 
Sort ([Key1], [Order1], [Key2], [Type], [Order2], [Key3], [Order3],
      [Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])
The documentation is incorrect: the Key2 and Type arguments are reversed.

The syntax of the Sort method should read as follows: 
Sort ([Key1], [Order1], [Type], [Key2], [Order2], [Key3], [Order3],
      [Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])

RESOLUTION

To correct this problem, you can:
  • Reverse the order of the Type and Key2 arguments in your code if you are using positional arguments. -or-

  • Use named arguments.

STATUS

This bug was corrected in Microsoft Excel 2000.

MORE INFORMATION

Steps to Reproduce Problem

  1. Start a new workbook in Microsoft Excel 97.
  2. Type the following data in cells A1:B6:
    Letter       Number
A            8
B            5
A            6
B            1
B            10
  3. Press the ALT+F11 key combination to start the Visual Basic Editor.
  4. Click Module on the Insert menu to insert a module into the project.
  5. Type the following code in the new module:
    Sub Test()
   Range("A1:B6").Sort Range("A1"), , Range("B1"), , , , , xlYes
End Sub
  6. Press the ALT+Q key combination to return to the workbook in Excel.
  7. Point to Macro on the Tools menu and then click Macros. Select the macro "Test" in the list and click Run.

    Result: The letters in column A are sorted correctly in ascending order but the numbers in column B are not.
To correct this problem, you can modify the macro so that it uses named arguments: 
Range("A1:B6").Sort Key1:=Range("A1"), Key2:=Range("B1"), Header:=xlYes
or you can change the order of the arguments: 
Range("A1:B6").Sort Range("A1"), , , Range("B1"), , , , xlYes
The data will then sort correctly: 
Letter       Number
A            6
A            8
B            1
B            5
B            10
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbdtacode kbfix KB229107
©2004 Microsoft Corporation. All rights reserved.