如何在Excel VBA中使用英国日期样式的“日期”和“自动过滤器”？

问：

我编写了Excel VBA，用于将数据从一个文件复制并粘贴到另一个文件。

我创建了输入框，要求用户输入开始日期和结束日期，然后使用“自动过滤”功能并复制和粘贴。但是，我使用的英国日期样式（dd/mm/yyyy）不起作用。我不得不输入美国日期样式（mm/dd/yyyy）。

有什么方法可以在我的 VBA 中允许英国日期样式？

Sub MyFilter()
    
'define startdate & enddate & create msgbox
    Dim W1Startdate As Date, W1Enddate As Date
    Columns("A:A").Select
  
    Selection.NumberFormat = "dd/mm/yyyy"
    
    Range("A2").Select
    
    W1Startdate = Application.InputBox("Enter the Start Date in MM/DD/YYYY format")
    W1Enddate = Application.InputBox("Enter the End Date in MM/DD/YYYY format")
    
   
     
' base on startdate & end date to filter Column A
   ActiveSheet.Range("A1:E35").AutoFilter field:=1, Criteria1:=">=" & W1Startdate, Criteria2:="<=" & W1Enddate
   
' select & copy filtered column A information
   With ActiveSheet.AutoFilter.Range
    .Offset(1, 0).Resize(.Rows.Count - 1, 1).Copy
   
   End With

' Paste filter column A to target file ("list2.xlsx") *change target file name when needed
    Windows("list2.xlsx").Activate
    Range("b" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Windows("MASTER.xlsm").Activate
    
 ' select & copy filtered column B information
    With ActiveSheet.AutoFilter.Range
    .Offset(1, 1).Resize(.Rows.Count - 1, 1).Copy
    
    End With
   
 ' Paste filter column B to target file ("list2.xlsx") *change target file name when needed
    Windows("list2.xlsx").Activate
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Windows("MASTER.xlsm").Activate
   
 ' select & copy filtered column 3 information
    With ActiveSheet.AutoFilter.Range
    .Offset(1, 2).Resize(.Rows.Count - 1, 1).Copy
    
    End With
   
 ' Paste filter column C to target file ("list2.xlsx") *change target file name when needed
    Windows("list2.xlsx").Activate
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Windows("MASTER.xlsm").Activate
   
 ' select & copy filtered column 3 information
    With ActiveSheet.AutoFilter.Range
    .Offset(1, 4).Resize(.Rows.Count - 1, 1).Copy
    
    End With
   
 ' Paste filter column E to target file ("list2.xlsx") *change target file name when needed
    Windows("list2.xlsx").Activate
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Windows("MASTER.xlsm").Activate
   
   
   
End Sub

我希望我可以输入英国日期格式来获得结果。