IsNumeric Function (VBA Function)

Today’s post is about another very useful vba function called “IsNumeric” function. This function evaluates whether the argument is a number or not. So if all the characters are numeric then IsNumeric function will return True. If any character of the argument is not numeric then the function will return false. However the function will still return true if you use dot(.) as a decimal point or comma (,) as a thousand separator.

IsNumeric is a built-in VBA function. However it is not available in worksheets. You can only use it in the VBA programs. (In VBA editor)

The syntax of the function is as follows. There is one parameter for this function.

IsNumeric(chars)

Parameter is required.

IsNumeric function returns
True - If the argument can be evaluated as number
False - If the argument can not be evaluated as number.

Keep in mind that IsNumeric("4,56") will return true. But IsNumeric("4/56") or IsNumeric("4^56") will return false.

Now let’s look at some examples.

Example 1

Sub IsNumericFunctionExample1() Dim S As String S = "156" Debug.Print IsNumeric(S) End Sub

Example 2

Sub IsNumericFunctionExamples2() Dim S As Variant S = "45.2" Debug.Print IsNumeric(S) End Sub

Example 3

Sub IsNumericFunctionExample3() Dim S As String S = "www" Debug.Print IsNumeric(S) End Sub

Example 4

Sub IsNumericFunctionExample4() Dim S As String S = "ww33w" Debug.Print IsNumeric(S) End Sub

Example 5

Sub IsNumericFunctionExample5() Dim S As String S = "4,565" Debug.Print IsNumeric(S) End Sub

Example 6

Sub IsNumericFunctionExample6() Dim S As String S = "4/56" Debug.Print IsNumeric(S) End Sub

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)

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.

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

How to Show Immediate Window in VBA Environment

Immediate window is a very important element in the VBA Environment. You can use it to see the result of the debugging statements. Also you can directly write commands to the immediate window as well.

Following are two main benefits of immediate window.

• Find the result of the functions.
• Find Current values of the variables.

If your VBA environment looks like this then your immediate window is not visible.

To make it visible, you can follow these steps.

First click on the “View” menu. Then click on the “Immediate Window”.

Immediate Window will appear below the code Window.

Also you can use the following keyboard shortcut to show the immediate window.

Ctrl + G