Have you get Type Mismatch Error (Run time error 13) when trying to convert a textbox value to a double. (using CDbl function). If so I will explain the reason for that and how to overcome the error.
I will explain this using a sample project. Let’s consider this simple data entry application. Below is an image of the user interface of the program.
Using this application users can select upto two payment methods and then can enter the amounts separately. Whenever “Amount 1“ or “Amount 2“ is updated then “Total Amount Paid” should be updated automatically and instantly. So to do this we can create a subroutine in a separate module and then can use the change events of the “Amount 1” and “Amount 2” textboxes to trigger that subroutine.
Following are the codes of change events of two textboxes.
|
Private Sub txtAmount1_Change() Call TotalAmountCalculation End Sub Private Sub txtAmount2_Change() Call TotalAmountCalculation End Sub |
And we can put the below code in a separate module. This is a simple subroutine which calculates the total amount.
|
Sub TotalAmountCalculation() Dim Amount1 As Double Dim Amount2 As Double Amount1 = CDbl(frmInput.txtAmount1.Value) Amount2 = CDbl(frmInput.txtAmount2.Value) frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub |
As you can see, the CDbl function is used to convert the values to double. But if we try to enter a value to one of the textboxes, we will get Type Mismatch Error. (Run Time Error 13).
Why does it happen? So the reason is this. We tried to enter a value to “Amount 1” textbox. But at that time “Amount 2” textbox is empty. So when the TotalAmountCalculation subroutine is triggered, the program tries to convert “Amount 2” textbox value also to a double.
| Amount2 = CDbl(frmInput.txtAmount2.Value) |
But as the “Amount 2” textbox is empty, we get an error. Because CDbl can’t convert an empty string to a double. CDbl can convert only numeric values. For an example following will give Type mismatch error.
|
Sub ConvertToDouble() Dim ConvertedValue As Double ConvertedValue = CDbl("122RE") Debug.Print ConvertedValue End Sub |
But below will print 122 in the immediate window.
|
Sub ConvertToDouble() Dim ConvertedValue As Double ConvertedValue = CDbl("122") Debug.Print ConvertedValue End Sub |
Because 122 is a numeric value, but 122RE is not.
So how do we solve the problem with the textboxes. Here are two possible solutions.
1. Convert to double only if the textbox is not empty.
2. Use Val function instead of CDbl
