Microsoft KB Archive/229107

= XL97: Problem Using Sort Method with More Than One Key =

Article ID: 229107

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



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.



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 </li> Press the ALT+Q key combination to return to the workbook in Excel.</li> 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.</li></ol>

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: kbbug kbdtacode kbfix KB229107

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.