Tanjima Hossain
If you are looking for some of the easiest ways to use the VBA IsNumeric function, then you are in the right place. We generally use this function in VBA to test whether an expression is a number or not and depending on the expression it will return TRUE if the expression is a number otherwise FALSE.
Download Workbook
VBA IsNumeric Function: Syntax & Arguments
⦿ Syntax
IsNumeric (Expression)
⦿ Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| Expression | Required | It is a variant that is to be checked if it is a number or not. |
⦿ Return Value
| Input | Return Value |
|---|---|
| Number | TRUE |
| Not a Number; String | FALSE |
⦿ Version
The ISNUMERIC function was introduced in the Excel 2000 version and is available for all versions after that.
9 Examples of Using VBA IsNumeric Function
In this article, we will try to demonstrate the uses of the VBA IsNumeric with some random examples along with some examples including the following table.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
1. Checking Out VBA IsNumeric with Some Random Values
Here, we will test out some random strings with the VBA ISNUMERIC, if the values are numeric or not.
Step-01:
➤ Go to Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.
After that, a Module will be created.
Step-02:
➤ Write the following code
Sub checkvalue1()
Dim x As Variant
x = InputBox("Give any Value")
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store the input value. Then ISNUMERIC will return TRUE if the input value is numeric otherwise it will return FALSE. We will find the output within a message box (MsgBox).
➤ Press F5.
Then you will get the following input box and if you write the value 100 and press OK,
you will get a message box that says “True”.
For writing the string Cat and pressing OK in the input box,
We are getting a message box saying “False”.
Read More: How to Use VBA Randomize Function in Excel (5 Examples)
2. Using VBA IsNumeric with IF-THEN-ELSE Statement
In this section, we will use the ISNUMERIC function with the IF-THEN-ELSE statement in a VBA code to define the numeric and non-numeric values.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue2()
Dim x As Variant
x = InputBox("Give any Value")
If IsNumeric(x) = True Then
MsgBox "The Given Value is numeric"
Else
MsgBox "The Given Value is not numeric"
End If
End SubHere, we have declared x as a Variant and it will store the input value. When ISNUMERIC will return TRUE, IF will return a message saying “The Given Value is numeric” and if ISNUMERIC returns FALSE, then IF returns a message explaining “The Given Value is not numeric”.
➤ Press F5.
Then you will get the following input box and if you write the value 200 and press OK,
you will get a message box that says “The Given Value is numeric”.
For writing the string Cat and pressing OK in the input box,
We are getting a message box saying “The Given Value is not numeric”.
Read More: VBA If – Then – Else Statement in Excel (4 Examples)
3. Creating an Opposite Result with the IsNumeric Function
Here, we will create a VBA code that will give us the reverse result of the ISNUMERIC function, which means for numeric values we will get FALSE, and for non-numeric values, it will return TRUE.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue3()
Dim x As Variant
x = InputBox("Give any Value")
If IsNumeric(x) = True Then
MsgBox ("FALSE")
Else
MsgBox ("TRUE")
End If
End SubHere, we have declared x as a Variant and it will store the input value. When ISNUMERIC will return TRUE, IF will return a message saying “FALSE” and if ISNUMERIC returns FALSE, then IF returns a message explaining “TRUE”.
➤ Press F5.
Then you will get the following input box and if you write the value 25 and press OK,
you will get a message box that says “FALSE”.
For writing the string Alaska and pressing OK in the input box,
We are getting a message box saying “TRUE”.
Related Content: VBA Format Function in Excel (8 Uses with Examples)
4. Checking Out If Blanks is Numeric or Not
You can check easily with a VBA code if blanks are numeric or not.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue4()
Dim x As Variant
x = " "
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store the Blank. Then ISNUMERIC will return TRUE if the Blank is numeric otherwise it will return FALSE.
➤ Press F5.
Afterward, you will get a message box saying “False” which means blanks are not numeric.
Read More: Excel Formula to Generate Random Number (5 examples)
5. Checking Out If Dates are Numeric or Not
In this section, we will use a random date and check if the date is numeric or not.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue5()
Dim x As Variant
x = "02/02/2022 "
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store a date. Then ISNUMERIC will return TRUE if the date is numeric otherwise it will return FALSE.
➤ Press F5.
Afterward, you will get a message box saying “False” which means dates are not numeric.
We can also try with the DATESERIAL function to create dates and check out if it is numeric or not.
➤ Type the following code
Sub checkvalue5a()
Dim x As Variant
x = DateSerial(2022, 2, 2)
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store a date created by the DATESERIAL function. Then ISNUMERIC will return TRUE if the date is numeric otherwise it will return FALSE.
➤ Press F5.
In return, you will get a message box saying “False” also this time.
Read More: VBA Date Function (12 Uses of Macros with Examples)
Similar Readings:
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
- Use the VBA Environ Function (4 Examples)
- How to Use VBA And Function in Excel (4 Examples)
- Use VBA Case Statement (13 Examples)
- How to Use Log Function in Excel VBA (5 Suitable Examples)
6. Checking Out If Time is Numeric or Not
In this section, we will check out if times are numeric or not by using the ISNUMERIC function.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue6()
Dim x As Variant
x = "09:30:00 AM"
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store a time. Then ISNUMERIC will return TRUE if the time is numeric otherwise it will return FALSE.
➤ Press F5.
After that, you will get a message box saying “False” which means times are not numeric.
You can also try with the TIMESERIAL function to create dates and check out if it is numeric or not.
➤ Type the following code
Sub checkvalue6a()
Dim x As Variant
x = TimeSerial(9, 30, 0)
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store a time created by the TIMESERIAL function. Then ISNUMERIC will return TRUE if the time is numeric otherwise it will return FALSE.
➤ Press F5.
Then, you will get a message box saying “False” also this time.
Again, we can try out referencing to a time value in a cell of a sheet.
➤ Type the following code
Sub checkvalue6b()
Dim x As Variant
x = range("B2").value
MsgBox IsNumeric(x)
End SubHere, we have declared x as a Variant and it will store a time that is in the B2 cell. Then ISNUMERIC will return TRUE if the time is numeric otherwise it will return FALSE.
➤ Press F5.
Finally, you will get a message box saying “True” this time.
Read More: How to Use VBA TimeSerial in Excel (3 Examples)
7. Using VBA IsNumeric for a Range of Values
Here, we will check if the values of the Marks/Grades column are numeric or non-numeric and have the results in the Check column.
Steps:
➤ Follow Step-01 of Section 1.
➤ Write down the following code
Sub checkvalue7()
Dim cell As Range
For Each cell In Range("D5:D11")
cell.Offset(0, 1) = IsNumeric(cell)
Next cell
End SubWe have declared the cell as Range and used a FOR loop for the cells of the range “D5:D11” and for these cells, the ISNUMERIC will return TRUE if the value is numeric, otherwise it will return FALSE and cell.Offset(0, 1) will return the output values in one column later to the input column.
➤ Press F5.
After that, we will have TRUE for the numeric values or Marks and FALSE for non-numeric values or Grades.
Read More: How to Use VBA Val Function in Excel (7 Examples)
8. Creating a Function to Test a Range of Values
In this section, we will create a function with the VBA ISNUMERIC and check if the values of the Marks/Grades column are numeric or non-numeric.
Step-01:
➤ Follow Step-01 of Section 1.
➤ Write down and save the following code
Function IsNumericTest(value As Variant) As Boolean
If IsNumeric(value) Then
IsNumericTest = True
Else
IsNumericTest = False
End If
End FunctionThis code will create a function named IsNumericTest.
Step-02:
➤ Return to the main sheet and type the following formula in the cell E5
=IsNumericTest(D5)D5 is the Marks/Grades of a student and IsNumericTest will return TRUE/FALSE depending on the value.
➤ Press ENTER and drag down the Fill Handle tool.
Finally, we will have TRUE for the numeric values or Marks and FALSE for non-numeric values or Grades.
Related Content: How to Use VBA DIR Function in Excel (7 Examples)
9. Counting Non-Numeric Values With VBA IsNumeric Function
We want to count the non-numeric values or grades of the Marks/Grades column and to do this here we will use the VBA ISNUMERIC and have the total number of the non-numeric values we have in the Count column.
Step-01:
➤ Follow Step-01 of Section 1.
➤ Write down and save the following code
Function countnonnumeric(value As range) As Long
Dim cell As range
Dim count As Long
For Each cell In value.Cells
If Not IsNumeric(cell.value) Then
count = count + 1
End If
Next
countnonnumeric = count
End FunctionThis code will create a function named countnonnumeric.
When the cell value will not be a numeric value then the count will be increased by 1.
Step-02:
➤ Return to the main sheet and type the following formula
=countnonnumeric(D5:D11)D5:D11 is the range of the Marks/Grades of the students and countnonnumeric will return the total number of non-numeric grades.
➤ Press ENTER
Finally, you will get the value 3 which means you have 3 Grades in the Marks/Grades column.
Read More: How to Return a Value in VBA Function (Both Array and Non-Array Values)
IsNumeric vs ISNUMBER
- ISNUMERIC checks if a value can be converted into a number and ISNUMBER checks if a value is stored as a number.
- There are some differences between the VBA ISNUMERIC function and the Excel ISNUMBER function and we have tried to show the differences below by using our previously created IsNumericTest function and the inbuilt Excel ISNUMBER function.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, we tried to cover some of the ways to use the VBA ISNUMERIC function. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
