使用VBA检查日期是否在两个日期之间 [英] Using VBA to check if a date is between two dates

问题描述

因此，我已经浏览了本网站上的许多问题以回答此问题，我相信我的代码是正确的，但仍然无法正确验证。我不能弄清楚为什么。

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.

我要做什么：

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

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.

错误：

1. 如果在输入框中输入了错误的日期，则程序
   会出错因为它无法使用无效的
   日期更新日历选择器。 （这是一个旧错误，因此我为什么要进行验证检查）


2. 如果输入了任何有效日期，则它在 If语句中无法正确验证。

例如：

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

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.

代码：

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

推荐答案

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

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.

更改说明：

第一个更改是声明什么类型的变量 tempDate ， oldDate 和 lateDate 是。以前，没有声明它们使它们成为变体。这样可以确保将它们记录为日期类型，从而可以像我尝试的那样进行比较。

The first changes made was to declare what type of variable tempDate, oldDate, and lateDate were. 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.

所以：

Dim tempDate
Dim oldDate
Dim lateDate

成为：

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

第二个更改是将 IsDate（）移至其自己的 If 语句。这是由于以下事实：如果输入的文本不是日期，则将导致致命错误，甚至在到达另一个 If 语句之前，由于它试图保存一个非-date为日期类型的变量。因此 tempDate = weekInput.Value 变为：

The second change is moving the IsDate() to its own If statement. 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 If statement as it was trying to save a non-date to a variable of date type. So tempDate = weekInput.Value became:

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

其余相同。除了我删除了 MsgBox 并通过添加以下几行代码来将错误消息的颜色从混合到背景更改为红色之外，从而使错误消息更美观，引人注意：

The rest remained the same. Except I deleted the MsgBox and 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:

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

答案：

这里是完整代码：

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

这篇关于使用VBA检查日期是否在两个日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！