Excel Visual Basic for Applications online training

This page has 0 threads | Add post

7,359 reviews

Self-paced training

Finding our tutorials useful? Please consider helping us to share more!

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?

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Returning a Date from an Input Box

Entering an Invalid Date

Using the Variant Data Type

Head office

London

Manchester