Excel-VBA Solutions

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.

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.

Popular Posts

Contact Form

Name

Email *

Message *