Excel-VBA 排序不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7432188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel-VBA Sort doesn't work
提问by chris
I have a problem with a sort function. It doesn't work. If I record a macro while sorting manually, the recorded looks like mine. The Sub looks as follows:
我的排序功能有问题。它不起作用。如果我在手动排序时录制宏，则录制的内容看起来像我的。Sub 如下所示：
Public Sub sortSelectionByDate(ByRef wrksheet As Worksheet, ByVal fromRow As Integer, ByVal toRow As Integer)
'debug
toRow = toRow - 1
wrksheet.Select
wrksheet.Rows(fromRow & ":" & toRow).Select
With Selection
.Sort Key1:=Range("A9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
Thank you in advance....
先感谢您....
Christoph
克里斯托夫
回答by Banjoe
It's most likely not working because you're passing a worksheet that isn't the active worksheet, not activating it, then trying to sort. Here's a link to a description of the problem you're having: Beginning VBA: Select and Activate. Long story short, never ever use .Select or .Selection unless you want your user to be able to run code on the selection of his choice. Even then, still not always a great idea. Here's your sub re-written in one line:
它很可能不起作用，因为您传递的工作表不是活动工作表，没有激活它，然后尝试排序。这是您遇到的问题描述的链接：Beginning VBA: Select and Activate。长话短说，永远不要使用 .Select 或 .Selection ，除非您希望您的用户能够在他选择的选择上运行代码。即便如此，仍然不总是一个好主意。这是您在一行中重写的子：
Public Sub sortSelectionByDate(ByRef wrksheet As Worksheet, ByVal fromRow As Integer, ByVal toRow As Integer)
wrksheet.Rows(fromRow & ":" & toRow - 1).Sort Key1:=wrkSheet.Range("A9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
The important changes are:
重要的变化是：
- No need to use .Select. If you did want to use it you'd need to use wrkSheet.Activate first.
- Change Key1:=Range("A9") to Key1:=wrkSheet.Range("A9"). Always helps to be explicit when working with cells/ranges.
- The With statement isn't needed since you're only running one method.
- 无需使用 .Select。如果您确实想使用它，则需要先使用 wrkSheet.Activate。
- 将 Key1:=Range("A9") 更改为 Key1:=wrkSheet.Range("A9")。在处理单元格/范围时总是有助于明确。
- 不需要 With 语句，因为您只运行一种方法。
