Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Type Mismatch Error (Run Time Error 13) When Converting to Double

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

Contact Form

Name

Email *

Message *