Using the IsNumeric Function to Avoid CDbl Type Mismatch Error

In the last post I explained about one reason why we get “Type mismatch (Run Time Error 13)” when we use the CDbl function. If we try to convert a value of an empty textbox to double then we get Type mismatch error. To solve that we came up with this below solution.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If frmInput.txtAmount1.Value <> "" Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If frmInput.txtAmount2.Value <> "" Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

frmInput is the name of the form. txtAmount1 and txtAmount2 are the names of “Amount 1” and “Amount 2” textboxes. And the txtTotalAmountPaid is the name of the “Total Amount Paid” textbox. Also the “Total Amount Paid” textbox is updated whenever a change takes place in “Amount 1” or “Amount 2” textboxes. Read this post if you like to see full details about the above solution.

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

So now we won’t get Type mismatch error when a textbox is empty. But still there is a problem. Assume that the user entered a value like this.

As you can see the user has entered a letter instead of a value. This can happen by mistake. Sometimes users might enter a $ symbol intentionally. But then CDbl can not cast that value to double. If you want to disregard the incorrect values entered by the user, then you can use a subroutine like this. Here we have used a VBA function called “IsNumeric” to check whether the user has entered a numeric value. Value will be converted to a double only if it is numeric.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If IsNumeric(frmInput.txtAmount1.Value) = True Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If IsNumeric(frmInput.txtAmount2.Value) = True Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Note that you can use IsNumeric(frmInput.txtAmount1.Value) instead of IsNumeric(frmInput.txtAmount1.Value) = True

So then you can rewrite the above code as follows.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If IsNumeric(frmInput.txtAmount1.Value) Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If IsNumeric(frmInput.txtAmount2.Value) Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Below is a sample result we will get.

Now in this case the user has entered an incorrect value to “Amount 2”. But the program has calculated value ignoring that. But as you can see this is not a good approach. Because if a user enters a non numeric value by mistake then still the program will give a result ignoring it. So the best way to handle this is by giving the warning to the user saying that he/she has entered invalid value. And then the program should not output any result. To do that we can make a change to above subroutine as follows.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If frmInput.txtAmount1.Value <> "" Then          If IsNumeric(frmInput.txtAmount1.Value) = True Then              Amount1 = CDbl(frmInput.txtAmount1.Value)          Else              MsgBox "You have entered incorrect value for Amount 1", , "Warning"              frmInput.txtTotalAmountPaid.Value = ""              Exit Sub          End If      End If      If frmInput.txtAmount2.Value <> "" Then          If IsNumeric(frmInput.txtAmount2.Value) = True Then              Amount2 = CDbl(frmInput.txtAmount2.Value)          Else              MsgBox "You have entered incorrect value for Amount 2", , "Warning"              frmInput.txtTotalAmountPaid.Value = ""              Exit Sub          End If      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Note that the program needs to check whether the value is numeric or not, only if the textbox is not empty. So in addition to the IsNumeric function we need to use frmInput.txtAmount1.Value <> "" as well. Also the following line has been used to clear the result if there is any invalid value in Amount 1 or Amount 2 textboxes.

frmInput.txtTotalAmountPaid.Value = ""

So now the program displays a warning message if there is any invalid input in the textboxes.

Results will be cleared when click ok.

If you like to learn more about the IsNumeric function, the read this post

IsNumeric Function (VBA Function)