Excel VBA accepting wrong date entry

user_5446919 0
Viewed 111

I'm a total noob in VBA but with the help of the community here I'm putting together a nice spreadsheet for my workplace. So first, I would like to thank you all!

I have an userform that has multiple comboboxes and textboxes that populate a table in another sheet. One of these textboxes is used to insert a date, that said, to prevent a wrong entry, I used the following code:

If IsDate(boxDate.Value) = False Then
    MsgBox "Enter valid date"
    Exit Sub
End If

As the code goes on, the text box populates the cell on this line of code:

.Cells(iRow, 5) = CDate(boxDate.Value)

The thing is, if I enter on the textbox just a single number, like "1", I get the msgbox error and end sub. But if I enter something like "1/121" I don't get the msgbox, the code goes on and I get an error 1004 on the line that has the CDate (posted above).

What am I doing wrong? The IF function recognizes "1/121" as a date, but CDate won't!

vba excel date
1 Answers

CDate does actually recognize "1/121" as a valid date, namely 1/1/121, as does IsDate. From the docs:

In Windows, the range of valid dates is January 1, 100 A.D., through December 31, 9999 A.D.; the ranges vary among operating systems.

And elsewhere:

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100, to 31 December 9999.

The problem is that Excel does not, because day 1 in Excel is 1/1/1900. Hence you get a RTE 1004 if you try to write the date to a cell.

Add another check that the year is >=1900 using Year.

user_92458530
Related