vba 使用VBA检查日期是否在两个日期之间

Using VBA to check if a date is between two dates

提问by Christopher Warrington

So, I have looked through many of the questions on this site to answer this question, and I believe that my code is correct, but it still doesn't verify correctly. I cannot figure out why though.

所以，我浏览了这个网站上的许多问题来回答这个问题，我相信我的代码是正确的，但它仍然没有正确验证。我不明白为什么。

What I am trying to do:

我正在尝试做的事情：

I am in Excel with a User Form. I have a date input box and a calendar picker. When one is changed, the other updates. I am trying to verify that the date entered into the input box is a valid date and within a two years time span, and if it is then update the calendar picker.

我在 Excel 中使用用户表单。我有一个日期输入框和一个日历选择器。当一个改变时，另一个更新。我试图验证输入框中输入的日期是否为有效日期并且在两年的时间跨度内，如果是，则更新日历选择器。

Errors:

错误：

1. If a wrong date is entered into the input box then the program errors out as it cannot update the calendar picker with an invalid date. (This is an old error and thus why I do the validation check)
2. If any valid date is entered, it does not verify correctly in the "If" statement.

1. 如果在输入框中输入了错误的日期，则程序会出错，因为它无法使用无效日期更新日历选择器。（这是一个旧错误，因此我进行验证检查的原因）
2. 如果输入任何有效日期，则不会在“If”语句中正确验证。

For instance:

例如：

If I enter 2/18/2016, it should see that it is a valid date, is later than the oldDate and more recent than the lateDate. Then update the calendar picker with the value of the input box. But, with this code it always resets the value of the input box and gives the message that it didn't verify.

如果我输入 2/18/2016，它应该会看到它是一个有效日期，晚于 oldDate 并且比lateDate 更近。然后使用输入框的值更新日历选择器。但是，使用此代码，它始终会重置输入框的值并给出未验证的消息。

Code:

代码：

Private Sub weekInput_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim tempDate
    Dim oldDate
    Dim lateDate

    tempDate = weekInput.Value
    oldDate = Date - 365
    lateDate = Date + 365

    'Doing this to try and error check if an invalid date is given. Doesn't work for some reason.
    If IsDate(tempDate) And tempDate > oldDate And tempDate < lateDate Then
        'Find date of that week's Monday
        weekPicker.Value = weekInput.Value
    Else
        weekInput.Value = weekPicker.Value
        MsgBox "Didn't verify"
    End If
End Sub

采纳答案by Christopher Warrington

Thank you to @RonRosenfeld for his answer in the comments on the question. Here is the corrected code which works. Added code that also shows an error message if the date doesn't validate by changing text color on the User Form from blending into the background to being red.

感谢@RonRosenfeld 在问题评论中的回答。这是有效的更正代码。添加了代码，如果日期未通过将用户表单上的文本颜色从混合到背景中更改为红色来验证，该代码也会显示错误消息。

Changes Explained:

更改说明：

The first changes made was to declare what type of variable tempDate, oldDate, and lateDatewere. Before, not having them declared made them Variant. This insures that they are recorded as a date type and thus can be compared as I was trying to do.

所做的第一个变化就是声明的变量的类型tempDate，oldDate和lateDate人。以前，没有声明它们会使它们变体。这确保它们被记录为日期类型，因此可以像我试图做的那样进行比较。

So:

所以：

Dim tempDate
Dim oldDate
Dim lateDate

Becomes:

变成：

Dim tempDate As Date
Dim oldDate As Date
Dim lateDate As Date

The second change is moving the IsDate()to its own Ifstatement. This is due to the fact that if the inputted text was not a date, it would cause a fatal error before even reaching the other Ifstatement as it was trying to save a non-date to a variable of date type. So tempDate = weekInput.Valuebecame:

第二个变化是将 移动IsDate()到它自己的If语句中。这是因为如果输入的文本不是日期，它甚至会在到达另一个If语句之前导致致命错误，因为它试图将非日期保存到日期类型的变量中。于是tempDate = weekInput.Value变成了：

If IsDate(weekInput.Value) Then
    tempDate = weekInput.Value
End If

The rest remained the same. Except I deleted the MsgBoxand made a nicer, less obtrusive error message by changing the color of an error message from blending into the background to being red by adding these lines of code:

其余的保持不变。除了我删除了MsgBox并通过添加以下代码行将错误消息的颜色从混合到背景中更改为红色，并制作了一个更好，更不突兀的错误消息：

dateError.ForeColor = &H80000004
dateError.ForeColor = &HFF&

Answer:

回答：

Here is the full code:

这是完整的代码：

Private Sub weekInput_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim tempDate As Date
    Dim oldDate As Date
    Dim lateDate As Date

    If IsDate(weekInput.Value) Then
        tempDate = weekInput.Value
    End If

    oldDate = Date - 365
    lateDate = Date + 365

    If tempDate >= oldDate And tempDate <= lateDate Then
        weekPicker.Value = weekInput.Value
        dateError.ForeColor = &H80000004
    Else
        weekInput.Value = weekPicker.Value
        dateError.ForeColor = &HFF&
    End If
End Sub