1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
| Option Explicit
Sub FilterTable()
Dim Dates
Dim BeginDate As Date
Dim EndDate As Date
Dim tmp As Date
Dates = getPeriodDates()
If IsArray(Dates) Then
BeginDate = Dates(0)
EndDate = Dates(1)
If BeginDate > EndDate Then tmp = BeginDate: BeginDate = EndDate: EndDate = tmp
Range("tableau1").AutoFilter field:=2, Criteria1:=">=" & CLng(BeginDate), Criteria2:="<=" & CLng(EndDate)
Else
MsgBox "Dates non valides"
End If
End Sub
Function getPeriodDates()
Dim Ok As Boolean
Dim RetValue(0 To 1)
Dim strDate1 As String
Dim strDate2 As String
strDate1 = InputBox("Veuillez saisir la date de début au format jj/mm/yyyy")
strDate2 = InputBox("Veuillez saisir la date de fin au format jj/mm/yyyy")
RetValue(0) = StringToDate(strDate1)
RetValue(1) = StringToDate(strDate2)
If RetValue(0) > 0 And RetValue(1) > 0 Then
getPeriodDates = RetValue
Else
getPeriodDates = 0
End If
End Function
Function StringToDate(Value As String) As Date
Dim Ok As Boolean
Dim YearValue As Long
Dim MonthValue As Long
Dim DayValue As Long
Ok = True
If Not Value Like "##/##/####" And Not Value Like "##-##-####" Then
Ok = False
Else
YearValue = Right(Value, 4) * 1
MonthValue = Mid(Value, 4, 2) * 1
DayValue = Left(Value, 2) * 1
If YearValue < 1900 Then
Ok = False
Else
Select Case MonthValue
Case 1, 3, 5, 7, 8, 10, 12
If DayValue > 31 Then Ok = False
Case 4, 6, 9, 11
If DayValue > 30 Then Ok = False
Case 2
If YearValue Mod 400 = 0 Or (YearValue Mod 100 <> 0 And YearValue Mod 4 = 0) Then
If DayValue > 29 Then Ok = False
Else
If DayValue > 28 Then Ok = False
End If
Case Else
Ok = False
End Select
End If
End If
If Ok Then
StringToDate = DateSerial(YearValue, MonthValue, DayValue)
Else
StringToDate = 0
End If
End Function |
Partager