vba 如何在vba中对数组中的日期进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20721489/
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
How can I sort dates in an array in vba?
提问by user3125707
Hi I am new to programming and just started learning VBA for excel. I have a query regarding sorting of arrays. How do I sort an array containing dates? For example if I have an array containing the dates ("23-jul-13","11-jan-10","1-may-09","3-feb-04") how do I sort this array. I have searched all over the internet for answers but could only find code for sorting numbers. I have been racking my brains on this for 2 days but can't seem to get it.
嗨，我是编程新手，刚开始学习 VBA for excel。我有一个关于数组排序的查询。如何对包含日期的数组进行排序？例如，如果我有一个包含日期的数组（“23-jul-13”、“11-jan-10”、“1-may-09”、“3-feb-04”），我该如何对这个数组进行排序。我在互联网上搜索了答案，但只能找到对数字进行排序的代码。我已经为此绞尽脑汁 2 天了，但似乎无法理解。
Thanks
谢谢
I have the code below which takes dates from a selected column but I am getting an error whenever I run it. I have been trying to figure out what's wrong with it for 2 days now. I didn't mention this code earlier as I though it would unnnecessarily add to the confusion. The sub GetUniqueAndCount works fine but it's the sort sub which is the problem as it doesn't accept the array passed to it as an argument.
我有下面的代码，它从选定的列中获取日期，但是每当我运行它时都会出现错误。我一直试图弄清楚它有什么问题了 2 天了。我之前没有提到这段代码，因为我认为它会不必要地增加混乱。sub GetUniqueAndCount 工作正常，但问题在于 sort sub，因为它不接受作为参数传递给它的数组。
Sub GetUniqueAndCount()
Dim d As Object, c As Range, k, tmp As String
Set d = CreateObject("scripting.dictionary")
'I will select the column of dates
For Each c In Selection
tmp = Trim(c.Value)
If Len(tmp) > 0 Then
If Year(DateValue(Format(tmp, "dd-mmm-yy"))) = 2013 Then
d(tmp) = d(tmp) + 1
End If
End If
Next c
i = 0
ReDim ThisArray(UBound(d.keys)) As Date
For Each k In d.keys
ThisArray(i) = DateValue(Format(k, "dd-mmm-yy"))
i = i + 1
Next k
Sort (ThisArray)
End Sub
Sub Sort(arr() As Date)
Dim Temp As Date
Dim i As Long
Dim j As Long
For j = 2 To UBound(arr)
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10 arr(i + 1) = Temp
Next j
End Sub
回答by Siddharth Rout
Your Sort(arr() As Date)works fine. The problem is with this line
你的Sort(arr() As Date)工作正常。问题出在这一行
Sort (ThisArray)
Change it to
将其更改为
Sort ThisArray
Also since you are storing Datesin ThisArray, I hope you have declared it as Date?
此外，由于你是存储Dates在ThisArray，我希望你已经宣布它作为Date？
Example
例子
Sub Sample()
Dim ThisArray(1 To 5) As Date
ThisArray(1) = #12/13/2013#
ThisArray(2) = #12/13/2012#
ThisArray(3) = #12/13/2015#
ThisArray(4) = #12/13/2014#
ThisArray(5) = #12/13/2016#
SortAr ThisArray
For i = 1 To 5
Debug.Print ThisArray(i)
Next i
End Sub
Sub SortAr(arr() As Date)
Dim Temp As Date
Dim i As Long, j As Long
For j = 2 To UBound(arr)
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10: arr(i + 1) = Temp
Next j
End Sub
OUTPUT
输出
13/12/2012
13/12/2013
13/12/2014
13/12/2015
13/12/2016
回答by Nikko
This might help. fell free to ask a follow up question.
这可能会有所帮助。随意问一个后续问题。
Sub Sort()
Dim x As Long, y As Long, z As Long
For x = Application.WorksheetFunction.Min(Columns("M")) To Application.WorksheetFunction.Max(Columns("M"))
For y = 1 To Worksheets("Users Info").Cells(Rows.Count, 13).End(xlUp).Row
If Worksheets("Users Info").Cells(y, 13).Value = i Then
z = z + 1
Worksheets("Users Info").Cells(z, 14).Value = i
End If
Next y
Next x
End Sub
