Module 5 - Messages and User Inputs
Lesson 5.4 - The Excel Input Box
Topic 5.4.5 - Capturing Dates

You've see how to force an Excel input box to return numeric values, but what if you want to capture a date value?

Future past

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:

Enter date

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:

Enter date

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:

Captured date

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:

Invalid 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:

Type mismatch

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:

Test for date

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:

  1. Using the same module, create a subroutine called DoubleDate.
  2. 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

  1. Run the subroutine and enter a valid date.  Make sure that you don't encounter a run-time error:
Enter date

This is a safe date format to use.

 
  1. Run the subroutine again and enter some text into the input box:
Invlaid number

You should see a validation message like this one after clicking OK on the input box.

 
  1. Click OK on the message box shown above and enter a number greater than 2958465 into the input box:
Invalid value

This value is a number but it's outside the range of the Date data type.

 
  1. Confirm that you receive a run-time error and click End on the dialog box that appears:
Runtime error

This is the error message you should receive.

 
  1. 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

  1. 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

  1. 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

  1. Run the subroutine and check that you see a sensible message when you enter valid dates:
End result

Try entering your birth date followed by the current date to see a horrifyingly large number.

 
  1. Save and close the workbook.
This page has 0 threads Add post