我已经能够创建一个代码，以便我可以从 Excel 中的电子表格中订购任何年份范围内的日期。
Private Sub UserForm_Initialize()
With Me
.StartUpPosition = 0
'.Width = Application.Width * 0.46
'.Height = Application.Height * 0.57
.Left = Application.Left + (Application.Width * 0.7) \ 2
.Top = Application.Top + (Application.Height * 0.3) \ 2
End With
End Sub
Private Sub CommandButton1_Click()
initialize ("lth") 'Call rutine "initialize" to order listbox from down to up
End Sub
Private Sub CommandButton2_Click()
initialize ("htl") 'Call rutine "initialize" to order listbox from up to down
End Sub
Sub initialize(ByVal ordertype As String)
Dim x As Integer
Dim i As Integer
Dim fechas As Date
Dim datofecha As String
Dim arrayyear() As Date
Dim diasmesbisiesto(1 To 12) As Integer
Dim diasmesnobisiesto(1 To 12) As Integer
Dim lastrow As Integer
Dim mayoryear As Integer
Dim menoryear As Integer
Dim f As Integer
Dim c As Date
lastrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
ReDim arrayyear(lastrow)
For u = 1 To lastrow
arrayyear(u) = Format(Sheet1.Cells(u, 1).Value, "dd/mm/yyyy")
Next u
'-------------------------
'Obtener mayor valor fecha
'-------------------------
Dim vm As Integer
Dim maxindex As Integer
maxindex = 1
For vm = 2 To UBound(arrayyear) 'suponemos indice desde 1 a n
If arrayyear(vm) > arrayyear(maxindex) Then
maxindex = vm
End If
Next
mayor = arrayyear(maxindex)
'-------------------------
'-------------------------
'Obtener menor valor fecha
'-------------------------
Dim vn As Integer
Dim minindex As Integer
minindex = 1
For vn = 2 To UBound(arrayyear) 'suponemos indice desde 1 a n
If arrayyear(minindex) < arrayyear(vn) Then
Else
minindex = vn
End If
Next
menor = arrayyear(minindex)
'-------------------------
menoryear = Year(menor) ' Asign the lowest year in menoryear
mayoryear = Year(mayor) ' Asign the highest year in mayoryear
ListBox1.Clear
'Carga vector diasmesbisiesto
'---------------------------
diasmesbisiesto(1) = 31
diasmesbisiesto(2) = 29
diasmesbisiesto(3) = 31
diasmesbisiesto(4) = 30
diasmesbisiesto(5) = 31
diasmesbisiesto(6) = 30
diasmesbisiesto(7) = 31
diasmesbisiesto(8) = 31
diasmesbisiesto(9) = 30
diasmesbisiesto(10) = 31
diasmesbisiesto(11) = 30
diasmesbisiesto(12) = 31
'---------------------------
'Carga vector diasmesnobisiesto
'---------------------------
diasmesnobisiesto(1) = 31
diasmesnobisiesto(2) = 28
diasmesnobisiesto(3) = 31
diasmesnobisiesto(4) = 30
diasmesnobisiesto(5) = 31
diasmesnobisiesto(6) = 30
diasmesnobisiesto(7) = 31
diasmesnobisiesto(8) = 31
diasmesnobisiesto(9) = 30
diasmesnobisiesto(10) = 31
diasmesnobisiesto(11) = 30
diasmesnobisiesto(12) = 31
'---------------------------
f = 0 'Variable para ubicar la ubicacion de la fila en donde se guardaran los datos en el listbox
cuenta = 0 ' Variable para contar la cantidad de elementos que se cargaron en el listbox
If ordertype = "lth" Then
GoTo 1 ' Ordering lowest to highest
End If
If ordertype = "htl" Then
GoTo 2 ' Ordering highest to lowest
End If
'---------------------------
' Ordering lowest to highest
'---------------------------
1:
For anio = menoryear To mayoryear
For mes = 1 To 12
If (anio Mod 4 = 0 And anio Mod 100 <> 0 Or anio Mod 400 = 0) Then
' Años bisiestos
diasmes = diasmesbisiesto(mes)
Else
' Años no bisiestos
diasmes = diasmesnobisiesto(mes)
End If
datofecha = Format(DateSerial(anio, mes, 1), "dd/mm/yyyy")
fechas = datofecha
'Carga del listbox
'------------------------------------------------------------------------------------------------------------
For x = 0 To diasmes - 1
For i = 1 To lastrow
c = fechas
If Format(Sheet1.Cells(i, 1), "dd/mm/yyyy") = c + x Then
Me.ListBox1.AddItem
Me.ListBox1.List(f, 0) = Format(Sheet1.Cells(i, 1), "dd/m/yyyy")
cuenta = cuenta + 1
For b = 1 To 1
Me.ListBox1.List(f, b) = Sheet1.Cells(i, b + 1)
Next b
f = f + 1
End If
Next i
Next x
'------------------------------------------------------------------------------------------------------------
Next mes
Next anio
Label2.Caption = "Number of Elements: " & cuenta
Label3.Caption = "Order from Lowest to Highest "
Exit Sub
'---------------------------
' Ordering highest to lowest
'---------------------------
2:
For anio = mayoryear To menoryear Step -1
For mes = 12 To 1 Step -1
If (anio Mod 4 = 0 And anio Mod 100 <> 0 Or anio Mod 400 = 0) Then
' Años bisiestos
diasmes = diasmesbisiesto(mes)
Else
' Años no bisiestos
diasmes = diasmesnobisiesto(mes)
End If
datofecha = Format(DateSerial(anio, mes, diasmes), "dd/mm/yyyy")
fechas = datofecha
'Carga del listbox
'------------------------------------------------------------------------------------------------------------
For x = 0 To diasmes - 1
For i = 1 To lastrow
c = fechas
If Format(Sheet1.Cells(i, 1), "dd/mm/yyyy") = c - x Then
Me.ListBox1.AddItem
Me.ListBox1.List(f, 0) = Format(Sheet1.Cells(i, 1), "dd/m/yyyy")
cuenta = cuenta + 1
For b = 1 To 1
Me.ListBox1.List(f, b) = Sheet1.Cells(i, b + 1)
Next b
f = f + 1
End If
Next i
Next x
'------------------------------------------------------------------------------------------------------------
Next mes
Next anio
Label2.Caption = "Number of Elements: " & cuenta
Label3.Caption = "Order from Highest to Lowest "
Exit Sub
End Sub