Excel-VBA Solutions: Using Val Function to Avoid CDbl Type Mismatch Error

In one of our earlier posts we learnt why we sometimes get “Type mismatch” error (Run-time error '13') when we use CDbl function to convert textbox values to double. So there were lots of reasons. One reason was due to no value in the textbox. In other words, if you try to convert the value of a textbox to a double using the CDbl function and if the textbox is empty then you will get a “Type mismatch” error.

Read this post if you want to learn more about CDbl Type mismatch error.

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

In this post I will show you how you can use the Val function instead of CDbl to avoid this problem. So you will no longer get a “Type mismatch” error when the textbox is empty. So let’s consider the following example.

Sub TotalPriceCalculation()

     Dim Price As Double
     Dim Tax As Double

     Price = CDbl(txtPrice.Value)
     Tax = CDbl(txtTax.Value)

     txtTotalPrice.Value = Price + Tax

End Sub

txtPrice, txtTax and txtTotalPrice are text boxes. txtTotalPrice textbox displays the total value of txtPrice and txtTax values. Note that we are entering the tax amount to the simple calculator. Not the tax percentage. However we will get a “Type mismatch” error if txtPrice or txtTax is empty. To avoid that issue we can modify the above subroutine using Val function as follows.

Sub TotalPriceCalculation()

     Dim Price As Double
     Dim Tax As Double

     Price = Val(txtPrice.Value)
     Tax = Val(txtTax.Value)

     txtTotalPrice.Value = Price + Tax

End Sub

Now if you call the subroutine when the textbox is empty, it won’t give any error. It will show the result in the txtTotalPrice textbox.

However remember that the program will still give a result even if the user enters incorrect values like this.

So this solution may not be suitable for some situations.

Pages

Using Val Function to Avoid CDbl Type Mismatch Error