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