Pages

CDbl - Type Mismatch Error When Textbox is Empty (How to Solve)

In the earlier post we learnt why we sometimes get “Type mismatch” error when we convert textbox values to double using the CDbl function. This error can occur due to several reasons.

Read this post if you want to know what the reasons are.

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

Converting an empty string is one reason for this “Type mismatch” error. In this post I will explain how to avoid the error when a textbox is empty. So let’s consider the following sample subroutine.

Sub TotalAmountCalculationEx1()

     Dim Value1 As Double
     Dim Value2 As Double

     Value1 = CDbl(frmInput.txtAmount1.Value)
     Value2 = CDbl(frmInput.txtAmount2.Value)

     frmInput.txtTotalAmountPaid.Value = Value1 + Value2

End Sub

frmInput is a form. txtAmount1 and txtAmount2 are textboxes. txtTotalAmountPaid is also a textbox. When this subroutine is executed, sum of values in txtAmount1 and txtAmount2 will be displayed in the txtTotalAmountPaid textbox. But this program won’t work if any of those two textboxes is empty. Then it will give Type mismatch error (Run-time error '13'). To avoid this error which occurs when a textbox is empty we can use the following steps. First we can assign value 0 to variables Value1 and Value2. So these will be the default values of the two variables. Then we can use a If statements to check whether textboxes are empty or not. Value will be converted to a double only if the textbox is not empty.

Sub TotalAmountCalculation()

     Dim Value1 As Double
     Dim Value2 As Double

     Value1 = 0
     Value2 = 0

     If frmInput.txtAmount1.Value <> "" Then
         Value1 = CDbl(frmInput.txtAmount1.Value)
     End If

     If frmInput.txtAmount2.Value <> "" Then
         Value2 = CDbl(frmInput.txtAmount2.Value)
     End If

     frmInput.txtTotalAmountPaid.Value = Value1 + Value2

End Sub

In VBA we use <> to check the Inequality. So the following line checks whether the value of txtAmount1 is unequal to "" or not.

If frmInput.txtAmount1.Value <> "" Then

In VBA "" represents an empty string.

If frmInput.txtAmount1.Value <> "" is true then it will execute the below line

Value1 = CDbl(frmInput.txtAmount1.Value)

This is the same for txtAmount2 as well.