XL97: Problem Using Sort Method with More Than One Key

ID: Q229107

The information in this article applies to:

Microsoft Excel 97 for Windows

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.

Use named arguments.

STATUS

This bug was corrected in Microsoft Excel 2000.

MORE INFORMATION

Steps to Reproduce Problem

Start a new workbook in Microsoft Excel 97.

Type the following data in cells A1:B6:


Letter       Number
A            8
B            5
A            6
B            1
B            10

Press the ALT+F11 key combination to start the Visual Basic Editor.

Click Module on the Insert menu to insert a module into the project.

Type the following code in the new module:


Sub Test()
   Range("A1:B6").Sort Range("A1"), , Range("B1"), , , , , xlYes
End Sub

Press the ALT+Q key combination to return to the workbook in Excel.

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

Additional query words: XL97 Automation OLE

Keywords : kbdta kbdtacode xlvbainfo kbExcel KbVBA kbGrpDSO
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug