You've see how to force an Excel input box to return numeric values, but what
if you want to capture a date value?
The future is so last year.
Files Needed
You don't need any files for this part of the lesson.
Completed Code
Click here to download
a file which contains the completed code.
Create a new workbook and insert a new module in the VBE.
Returning a Date from an Input Box
You can't use the Type parameter to limit an Excel input box
to allow only date values. As a partial solution to this, you can set the
input box to allow only numbers by passing a value of 1 to the
Type parameter. Create a new subroutine called
ReturnADate and add code which captures the result of an Excel input
box in a Date variable:
Here we've restricted the input box to accept only numbers and captured its result in a
Date variable.
You can run the subroutine and enter a date into the input box in a variety of different formats:
Although it doesn't look much like a number, as long as the value you enter can be evaluated as a date, it will be accepted.
You can use the Locals window to check that your date has been
stored correctly:
The
Locals window will show the value that you have captured.
Entering an Invalid Date
Relying on this technique to capture dates can cause a problem if the user
enters a numeric value which can't be converted into a date:
Entering a number which can't be implicitly converted into a date will cause this technique to fail.
The value in the input box shown above can't be converted into a date and so the
code generates a run-time error:
You'll see this type of error message if the value can't be converted into a date.
Using the Variant Data Type
For dates, it might be best to use the same technique you would use for a VBA
input box; that is to capture the input value as a Variant before testing if it
can be treated as a Date value. In the example below
we've changed the data type of the variable to Variant and
tested if it contains a valid date using the IsDate function:
You can use the
IsDate function to test if the value entered can be converted into a date.
You can try running the subroutine again and try to enter an invalid date to
check that you see a sensible message rather than a run-time error.
To practise capturing dates using an Excel input box:
- Using the same module, create a
subroutine called DoubleDate.
- Declare a Date variable and use an Excel
input box with a Type of 1 to
assign a value to it:
Sub DoubleDate()
Dim StartDate As Date
StartDate = Application.InputBox( _
Prompt:="Enter start date", _
Type:=1)
End Sub
- Run the subroutine and enter a valid date. Make sure
that you don't encounter a run-time error:
This is a safe date format to use.
- Run the subroutine again and enter some text into the input
box:
You should see a validation message like this one after clicking OK on the input box.
- Click OK on the message box
shown above and enter a number greater than 2958465 into the
input box:
This value is a number but it's outside the range of the
Date data type.
- Confirm that you receive a run-time error and click
End on the dialog box that
appears:
This is the error message you should receive.
- Remove the Type argument, change the data type of the variable to Variant
and add an If statement to check that the value
entered is a date:
Sub DoubleDate()
Dim StartDate As Variant
StartDate = Application.InputBox( _
Prompt:="Enter start date")
If IsDate(StartDate) Then
StartDate = CDate(StartDate)
Else
MsgBox "That's not a date!"
Exit Sub
End If
End Sub
- Repeat this process to ask the user to enter an end date:
Sub DoubleDate()
Dim StartDate As Variant
Dim EndDate As Variant
StartDate = Application.InputBox( _
Prompt:="Enter start date")
EndDate = Application.InputBox( _
Prompt:="Enter end date")
If IsDate(StartDate) And IsDate(EndDate) Then
StartDate = CDate(StartDate)
EndDate = CDate(EndDate)
Else
MsgBox "You must enter valid dates!"
Exit Sub
End If
End Sub
- Add a message box to tell the user the difference in days
between the dates they have entered:
Sub DoubleDate()
Dim StartDate As Variant
Dim EndDate As Variant
StartDate = Application.InputBox( _
Prompt:="Enter start date")
EndDate = Application.InputBox( _
Prompt:="Enter end date")
If IsDate(StartDate) And IsDate(EndDate) Then
StartDate = CDate(StartDate)
EndDate = CDate(EndDate)
Else
MsgBox "You must enter valid dates!"
Exit Sub
End If
MsgBox _
"Difference in days = " & _
DateDiff("d", StartDate, EndDate)
End Sub
- Run the subroutine and check that you see a sensible message
when you enter valid dates:
Try entering your birth date followed by the current date to see a horrifyingly large number.
- Save and close the workbook.
