## How to use the Excel ISNUMBER and ISNUMERIC VBA Function

• Post author:
• Post category:Uncategorized

We can use the Excel ISNUMBER and ISNUMERIC VBA functions to detect numeric values in Excel and VBA programming. Both functions return TRUE if the cell contains a number and FALSE, if not. However, the Excel ISNUMERIC function is a VBA function whereas the ISNUMBER function is a worksheet function. Both functions can also yield different results in similar circumstances.

In this tutorial, we will learn how to use the EXCEL ISNUMBER and the ISNUMERIC VBA functions.

## Syntax for EXCEL ISNUMBER Function

`=ISNUMBER(value)`

Where value is the specified cell, formula, function or value to test. The ISNUMBER function checks if a value is stored as a number.

## Syntax for EXCEL ISNUMERIC function

`IsNumeric(expression)`

Where expression is evaluated as a number. The Excel VBA ISNUMERIC checks if a value can be converted to a number.

Figure 1 – Result of excel check if number is True or False

## Setting up Data and VBA for ISNUMERIC and Excel ISNUMBER functions

• We will set a data table as shown below

Figure 2 –  Setting Data for Excel ISNUMBER and ISNUMERIC function

• We will click on Developer and select Visual Basic
• Next, we will click on Insert and select Module
• In the new VBA window, we will enter this Macro code below

`Function IsNumericTest(TestCell As Variant)`

`'Use VBA to test if a cell is numeric via a function`

`If IsNumeric(TestCell) Then 'if TestCell is True`

`   IsNumericTest = True 'Cell is a number`

`Else`

`   IsNumericTest = False 'Cell is not a number`

`End If`

`End Function`

Figure 3 – Excel ISNUMERIC VBA

## Testing Data using Excel VBA ISNUMERIC and ISNUMBER functions

We will test Column A with the Excel ISNUMBER function in Column B and Excel VBA IsNumeric() function in Column C.

1. To check with the ISNUMBER function;

• In Cell B4, we will enter the formula below and press the Enter key

`=ISNUMBER(A4)`

Figure 4 – Excel ISNUMBER

• We will have this result

Figure 5 – Using the Excel ISNUMBER function

• We will click again on Cell B4 and using the fill handle tool, we will drag the formula down the column to get this result:

Figure 6 – ISNUMBER function in Excel

2. For the IsNumeric function test

• We will click on Cell C4, enter the formula below and press the enter key

`=IsNumericTest(A4)`

Figure 7 – ISNUMERIC VBA

• We will have this result

Figure 8 – VBA ISNUMERIC

• Now, we will click on Cell C4 and using the fill handle tool, we will drag the formula down the column to get this result:

Figure 9 – Excel VBA ISNUMERIC

## Explanation

In our example, this is the different results given by the two functions.

Figure 10 – Using Conditional formatting for ISNUMBER versus ISNUMERIC in Excel

By Comparing results using both functions, we will find that

• The Excel IsNumeric function considers empty numeric cells but the Excel ISNUMBER function does not
• The Excel ISNUMBER function finds dates entered with texts and characters as numbers because it is stored as numbers whereas the Excel IsNumeric does not