VBA代码可以在Excel 2013中运行，但无法在2010中运行。

Question

我已经使用Excel 2013编写了以下代码，它可以在Excel 2013上运行。但是，当我在公司的Excel 2010版本上尝试时，当我点击输入框对话框上的取消按钮时，会出现错误，并停止在代码行处：

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

- user3151129

Answer 1

将 EntryDate As Date 更改为 EntryDate As Variant

并且更改该行，

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

为了

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

and after that line

添加这行代码

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

- Siddharth Rout

---

+1 快速回答！我甚至还没来得及阅读问题 :) - Dmitry Pavliv

@SiddharthRout 是的，这就是我想做的，而且我在这里使用Cdate，因为我希望应用程序将日期值插入为日期格式，以便支持过滤。 - user3151129

@MohammadTaha：是的，我明白了，所以我删掉了那个评论 :) - Siddharth Rout

顺便说一句，最后三行的缩写不错 :) - user3151129

@MohammadTaha: 这是我的口头禅 :p HERE - Siddharth Rout