Excel Visual Basic for Applications online training

This page has 0 threads | Add post

7,326 reviews

Self-paced training

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

Module 5 - Messages and User Inputs

Lesson 5.3 - The VBA Input Box

Topic 5.3.5 - Type Mismatch Errors

Using a VBA input box to capture numbers and dates works well until somebody enters a value of the wrong type:

If your code is expecting the user to enter a number, what happens when they don't?

Files Needed

You can click here to download the file needed for this part of the lesson.

Completed Code

You can click here to download the completed code.

Returning the Wrong Data Type

Extract and open the workbook linked to in the Files Needed section above. In Module1, find the subroutine called TypeMismatchErrors. In the previous part of the lesson you saw that you can use the VBA input box to capture non-string values such as dates and numbers. The TypeMismatchErrors subroutine attempts to capture a Date value from an input box:

You can attempt to return the value of an input box to a variable of any data type.

The problem is that there is no control over what values your users type into the input box:

Rebellious users may have ideas of their own...

This leaves your code prone to causing type mismatch errors:

This is not the sort of message that inspires confidence in your users.

Clicking Debug on the dialog box shown above indicates the line that has caused the error:

Unsurprisingly, it's the line which attempts to store a value in the Date variable.

You can also cause a run-time error by cancelling the input box. Clicking Cancel causes the input box to return an empty string, which can't be stored in either a Date or an Integer variable.

Returning All Results as Strings

A simple way to avoid this sort of problem is to initially store all values returned from input boxes as strings. Find the subroutine called StoringStrings in Module1:

Storing the result of each input box as a String eliminates the risk of a type mismatch.

Once you've safely captured the value in a string variable, you can use a function to determine if it could be converted into another data type. Add an If statement to the StoringStrings subroutine to test if the DOBString variable contains a valid date:

Use the IsDate function to test if the variable contains a value that can be converted into a date. If the value of the variable can't be converted into a date we inform the user and then exit the procedure.

If the user enters an invalid date, instead of a standard run-time error message they'll see a more user-friendly version:

You could provide some friendly instructions to help the user to enter a valid value the next time they run the procedure.

You can test if a value is a number in a similar way, using the IsNumeric function. Add another If statement to the StoringStrings subroutine to check if the HeightCmString variable contains a value that can be converted into a number:

If the value of the variable can't be converted into a number, the user will see a message and then the procedure will end.

Explicitly Converting the Data Type

Once you've established that a value can be treated as another type, you may wish to store it in a variable of the correct type. This can be useful if you want to perform calculations on a number or a date later in the procedure. The example below shows how you could explicitly convert a string into a date using the CDate function and store the result in a separate variable:

It's quite a lot of effort to ensure that your users can't enter invalid data into a VBA input box!

Using explicit type conversion functions makes code easier to read, but you don't technically need to use them. You could omit the CDate function in the above example and rely on implicit data type conversion:

'Convert the string to a date

DoB = DoBString

You can take the same approach to explicitly convert a string into a number:

You should also make sure that the number is within the range of the data type you're using. If the user entered a value of greater than 32,767 (the upper limit of the Integer data type) they would see another run-time error message.

To practise avoiding type mismatch errors:

Using the same module, create a subroutine called HowManyDays.
Declare a string variable to hold a user's date of birth and use an input box to populate it:

Sub HowManyDays()

Dim DoBString As String

DoBString = InputBox("Enter date of birth")

End Sub

Add code to check that the value entered can be treated as a date and, if not, exit from the procedure:

Sub HowManyDays()

Dim DoBString As String

DoBString = InputBox("Enter date of birth")

If Not IsDate(DoBString) Then

MsgBox "That's not a date!", vbCritical

Exit Sub

End If

End Sub

Declare a new variable which can hold a Date value. Convert the value of the string variable into a date and store it in the new variable:

Sub HowManyDays()

Dim DoBString As String

Dim DoB As Date

DoBString = InputBox("Enter date of birth")

If Not IsDate(DoBString) Then

MsgBox "That's not a date!", vbCritical

Exit Sub

End If

DoB = CDate(DoBString)

End Sub

Declare a new variable which can hold a Long value. Calculate the user's age in days and store the result in the new variable then display it on a message box:

Sub HowManyDays()

Dim DoBString As String

Dim DoB As Date

Dim DaysOld As Long

DoBString = InputBox("Enter date of birth")

If Not IsDate(DoBString) Then

MsgBox "That's not a date!", vbCritical

Exit Sub

End If

DoB = CDate(DoBString)

DaysOld = DateDiff("d", DoB, Date)

MsgBox _

"You are " & DaysOld & " days old", _

vbInformation

End Sub

Run the subroutine and check that you see an appropriate result when you type in a valid date:

Suddenly, one's age in years doesn't seem quite so bad.

Run the subroutine again and check that you see your warning message when you type in a value which isn't a date:

Although it's quite simple, it's better than seeing a run-time error message.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Returning the Wrong Data Type

Returning All Results as Strings

Explicitly Converting the Data Type

Head office

London

Manchester