Functions ISNUMBER or IsNumeric in Excel

by Gregory on December 13, 2010

I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation.

For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things like a space character can get entered into an otherwise empty cell, and go undetected to the naked eye.

VBA Programming

As part of a VBA macro I looped through each cell in this data range and performed an operation for any cell value greater than zero. This was a good plan for a data range having only number values or empty cells. (Well, not really as you’ll see below.)

However, I not only came across a space character in the user data, but also an accent character (`). As you can see in third column of the the chart pictured below, (>0) greater than zero is TRUE for the space character and the accent mark.

IsNumber or IsNumeric Chart

What I really wanted was the Excel Function ISNUMBER that only shows TRUE when, well, there’s a number. (Fourth column)

So I tried the VBA function IsNumeric in my macro, thinking it to be the same thing, and found that it didn’t work as I had imagined. For some reason the IsNumeric function shows TRUE for and empty cell as you can see in fifth column of the chart.

I wrote a function called IsNum() to simulate the VBA function IsNumeric in the worksheet.

Function IsNum(data As Variant)
If IsNumeric(data) Then
IsNum = True
Else
IsNum = False
End If
End Function

The difference seems to be that the Worksheet Function ISNUMBER takes a Value for an argument, and the VBA function IsNumeric takes an Expression for its argument.

My solution is to use both greater than zero (>0) AND IsNumeric for my logic gate to perform further operations. (Last column in the chart) So in looping through the data range above, operations would only be performed on the numbers (23 and 15).

Lessons Learned

  1. Now I know that an accent character (`) is greater than zero.
  2. The VBA function IsNumeric is different than the Excel worksheet function ISNUMBER.
  3. Testing doesn’t always bring out all the ways a user can change the data. Only time and more users can do that.
  4. One logical operation in a very large VBA macro (9 subroutines w/ 2 functions) can bring down the whole show.
Related Posts Plugin for WordPress, Blogger...
Marco February 18, 2011 at 7:20 am

Hi,
You will need more then adding ” >0 And ” in the code
If the data in a cell has a value of : ’1
Excel function IsNumber = False
VBA function IsNumeric = True
>0 and IsNumeric = True
If the data cell is formatted as ‘text’ and contains value 1:
(in this case the value will not be taken into consideration with the excel “SUM” function)
Excel function IsNumber = False
VBA function IsNumeric = True
>0 and IsNumeric = True

BR.Marco

Gregory February 18, 2011 at 4:13 pm

@Marco, having to work with something like ’1 is why Excel developers get headaches. When ISNUMBER and IsNumeric can’t agree it makes things even more complicated. Thanks for pointing out the discrepancy.

Besh October 23, 2012 at 2:32 am

You can use Len function to test, whether the cell is empty first and then used IsNumeric to test for numeric values. This can manage the problem with empty cells.

Dim L as integer
L = Len(cells(row, culumn)) = 0

Select case L
Case is = 0
‘set output as false
case is >0
‘use IsNumeric function
end select

Gregory October 23, 2012 at 7:08 pm

Actually, that works pretty darn good. Thanks.

Function IsNum2(data As Variant)
Dim L As Integer
L = Len(data)
Select Case L
Case Is = 0
IsNum2 = False
Case Is > 0
If IsNumeric(data) Then
IsNum2 = True
Else
IsNum2 = False
End If
End Select
End Function

Mike January 15, 2013 at 7:00 am

isnumber() is available if you use the following line in your code

Application.worksheetfunction.isnumber()

Gregory January 15, 2013 at 5:04 pm

Thanks for reminding me.

For some reason, which I don’t clearly state in my blog post, I remember not using that line of code, but didn’t want to confuse the issue. I think it had to do with the range of data I was testing was dumped into an Array and the application.worksheetfunction.isnumber function wouldn’t work, so I had to think up something else or re-write a good bit of code.

Eldar Agalarov May 7, 2013 at 3:39 am

Use this function:

Public Function IsNumber(ByRef expression As Variant) As Boolean
IsNumber = Not IsEmpty(expression) And IsNumeric(expression)
End Function

And have fun!

Comments on this entry are closed.

Previous post:

Next post: