VBA 代码可以在 Excel 2013 中运行，但不能在 2010 中运行

Question

我使用 Excel 2013 编写的以下代码适用于 Excel 2013，但是当我在我公司的 2010 版 Excel 上尝试它时，当我单击输入框对话框上的“取消”按钮并停止在代码行时，它会产生错误:

EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))

说:类型不匹配，编号为:运行时错误'13':

为什么？

    Sub InsertNewEntry()
'
' InsertNewEntry Macro
' To Insert New Entry for exchange rates
'

Dim LastRow As Integer, EntryDate As Date
Dim EURtoUSD As Double, JODtoUSD As Double, ILStoUSD As Double

' determine the number of the last row entered
LastRow = ThisWorkbook.Worksheets("Exchange Rates Template").Cells(Rows.Count, 2).End(xlUp).Row

'determine if last date is last day of the year
   If Cells(LastRow, 2) = #12/31/2014# Then
    MsgBox "You are not allowed to insert a date above " & "31/12/ " & Cells(4, 1).Value
    Exit Sub
   Else
    Cells(LastRow, 4).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow, 8).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow, 12).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Cells(LastRow + 1, 2).Select
    EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))
      If EntryDate <> "" Then
       Cells(LastRow + 1, 2) = EntryDate
       Cells(LastRow + 1, 3) = "EUR to USD"
       Cells(LastRow + 1, 6) = EntryDate
       Cells(LastRow + 1, 7) = "JOD to USD"
       Cells(LastRow + 1, 10) = EntryDate
       Cells(LastRow + 1, 11) = "ILS to USD"
      Else
       Cells(LastRow + 1, 2).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Cells(LastRow + 1, 6).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Cells(LastRow + 1, 10).Select
       Selection.ListObject.ListRows(LastRow - 3).Delete
       Exit Sub

      End If
   End If

End Sub

Answer 1

将EntryDate As Date更改为EntryDate As Variant

并更改行，

EntryDate = CDate(InputBox("Insert Date", "Insert Date", vbOKCancel))` to

至

EntryDate = InputBox("Insert Date", "Insert Date", vbOKCancel)

以及该行之后

添加此行

If EntryDate = False Then Exit Sub

在这行之后，你可以写

EntryDate  = Cdate(EntryDate)

原因很简单，因为 Cdate(False) 会给你 00:00:00 而 Cdate(True) 会给你1899年12月29日

评论跟进

这就是你正在尝试的吗？

Dim EntryDate As Variant

EntryDate = InputBox("Insert Date", "Insert Date", vbOKCancel)

If EntryDate = False Then Exit Sub

If EntryDate <> "" Then
    EntryDate = CDate(EntryDate)
    Cells(LastRow + 1, 2).Value = EntryDate
    Cells(LastRow + 1, 3).Value = "EUR to USD"
    Cells(LastRow + 1, 6).Value = EntryDate
    Cells(LastRow + 1, 7).Value = "JOD to USD"
    Cells(LastRow + 1, 10).Value = EntryDate
    Cells(LastRow + 1, 11).Value = "ILS to USD"
Else
    Cells(LastRow + 1, 2).ListObject.ListRows(LastRow - 3).Delete
    Cells(LastRow + 1, 6).ListObject.ListRows(LastRow - 3).Delete
    Cells(LastRow + 1, 10).ListObject.ListRows(LastRow - 3).Delete
    Exit Sub
End If