如何在vba中对数组中的日期进行排序? [英] How can I sort dates in an array in vba?

问题描述

我是编程新手，刚刚开始学习VBA for excel.我有一个关于数组排序的查询.如何对包含日期的数组进行排序?例如，如果我有一个包含日期的数组("23-jul-13"，"11-jan-10"，"1-may-09"，"3-feb-04")，该如何对该数组进行排序.我已经在互联网上搜索了所有答案，但只能找到用于对数字进行排序的代码.我已经为此花了2天的时间，但似乎无法理解.

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.

谢谢

我有下面的代码，该代码从选定的列中获取日期，但是每次运行它时都会出现错误.我已经尝试了两天了，这是怎么回事.我没有提起这段代码，因为它不必要地增加了混乱. 子GetUniqueAndCount可以正常工作，但是它是sort子，这是问题所在，因为它不接受作为参数传递给它的数组.

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.

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

推荐答案

您的Sort(arr() As Date)正常工作.问题是这条线

Your Sort(arr() As Date) works fine. The problem is with this line

Sort (ThisArray)

将其更改为

Sort ThisArray

还因为您将Dates存储在ThisArray中，所以希望您已将其声明为Date?

Also since you are storing Dates in ThisArray, I hope you have declared it as Date?

示例

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

输出

13/12/2012 
13/12/2013 
13/12/2014 
13/12/2015 
13/12/2016 

这篇关于如何在vba中对数组中的日期进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！