How to Use VBA IsNumeric Function (9 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

VBA IsNumeric

Download Workbook


VBA IsNumeric Function: Syntax & Arguments

⦿ Syntax

IsNumeric (Expression)

VBA IsNumeric

⦿ 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.

VBA IsNumeric

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.

VBA IsNumeric

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

random values

After that, a Module will be created.

random values

Step-02:
➤ Write the following code

Sub checkvalue1()

Dim x As Variant
x = InputBox("Give any Value")

MsgBox IsNumeric(x)

End Sub

Here, 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).

random values

➤ Press F5.

Then you will get the following input box and if you write the value 100 and press OK,

random values

you will get a message box that says “True”.

random values

For writing the string Cat and pressing OK in the input box,

VBA IsNumeric

We are getting a message box saying “False”.

random values

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 Sub

Here, 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”.

VBA IsNumeric

➤ Press F5.

Then you will get the following input box and if you write the value 200 and press OK,

IF-THEN-ELSE

you will get a message box that says “The Given Value is numeric”.

IF-THEN-ELSE

For writing the string Cat and pressing OK in the input box,

IF-THEN-ELSE

We are getting a message box saying “The Given Value is not numeric”.

VBA IsNumeric

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 Sub

Here, 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”.

VBA IsNumeric

➤ Press F5.

Then you will get the following input box and if you write the value 25 and press OK,

opposite result

you will get a message box that says “FALSE”.

opposite result

For writing the string Alaska and pressing OK in the input box,

opposite result

We are getting a message box saying “TRUE”.

VBA IsNumeric

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 Sub

Here, 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.

VBA IsNumeric

➤ Press F5.

Afterward, you will get a message box saying “False” which means blanks are not numeric.

blanks

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 Sub

Here, 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.

dates

➤ Press F5.

Afterward, you will get a message box saying “False” which means dates are not numeric.

dates

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 Sub

Here, 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.

VBA IsNumeric

➤ Press F5.

In return, you will get a message box saying “False” also this time.

dates

Read More: VBA Date Function (12 Uses of Macros with Examples)


Similar Readings:


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 Sub

Here, 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.

VBA IsNumeric

➤ Press F5.

After that, you will get a message box saying “False” which means times are not numeric.

time

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 Sub

Here, 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.

time

➤ Press F5.

Then, you will get a message box saying “False” also this time.

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 Sub

Here, 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.

VBA IsNumeric

➤ Press F5.

Finally, you will get a message box saying “True” this time.

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.

VBA IsNumeric

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 Sub

We 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.

range of values

➤ Press F5.

After that, we will have TRUE for the numeric values or Marks and FALSE for non-numeric values or Grades.

range of values

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.

VBA IsNumeric

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 Function

This code will create a function named IsNumericTest.

creating a function

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.

creating a function

➤ Press ENTER and drag down the Fill Handle tool.

creating a function

Finally, we will have TRUE for the numeric values or Marks and FALSE for non-numeric values or Grades.

VBA IsNumeric

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.

VBA IsNumeric

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 Function

This 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.

counting non-numeric values

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.

counting non-numeric values

➤ Press ENTER

Finally, you will get the value 3 which means you have 3 Grades in the Marks/Grades column.

counting non-numeric values

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.

VBA IsNumeric


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.

practice


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.


Related Articles

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo