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.
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.
What I really wanted was the Excel ISNUMBER fucntion that only shows TRUE when, well, there’s a number. (Fourth column)
So I tried the VBA Excel IsNumeric function 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
IsNum = False
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 About Excel ISNUMBER or IsNumeric Function
- Now I know that an accent character (`) is greater than zero.
- The VBA function IsNumeric is different than the Excel worksheet function ISNUMBER.
- Testing doesn’t always bring out all the ways a user can change the data. Only time and more users can do that.
- One logical operation in a very large VBA macro (9 subroutines w/ 2 functions) can bring down the whole show.