IsNumeric and isEmpty... bug?

Using isnumeric and isempty I get this odd result.

I have this data pulled out from an empty cell in Excel with nothing in it.

Using IsNumeric on the cell data I get true. Printing it out gives me nothing. Testing with IsEmpty I get false.

What is in an empty cell that is a number, but prints out as nothing?

I even tried printing the cell content out between two single quotes and got nothing printed out between the quotes...???

Driving me nuts. I'm ready to bang my head against the wall.

If anyone can help, that would be much appreciated.

At a guess, do you have the Hide zero values option on?

What is the cell formatted as? Numeric or ?

IsEmpty:

Quote:

---

Returns a Boolean value indicating whether a variable has been initialized.

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

---

The cells are formatted as numbers, When I print out the cells I get '' with the following code:

debug.print "'" & wks.cells(7,7) & "'"

This is the code I used to check it with:

IsNumeric(wks.Cells(y, x) & wks.Cells(y + 3, x) & wks.Cells(y + 4, x) & _
wks.Cells(y + 5, x) & wks.Cells(y + 6, x) & wks.Cells(6, 2) & wks.Cells(7, 2)) And _
Not IsEmpty(wks.Cells(y, x) & wks.Cells(y + 3, x) & wks.Cells(y + 4, x) & _
wks.Cells(y + 5, x) & wks.Cells(y + 6, x))

The isnumeric part returns TRUE
The isempty part returns FALSE
When printed out, I get empty string. I also tried checking it with

if wks.cells(7,7) = "" then
end if

but it fails this check too.

RobDog888, thanks for the info. I misunderstood the use of isempty. It checks is a variant is initialized. I would have preffered the function be called isInit instead though... The cell are formatted as Numeric

What type of data does wks.cells(x,y) return?

penagate, zero value option is checked meaning it shows zero values.

Quote:

---

wks.cells(x,y) return?

---

It returns a Range object

You can check whether the particular cell is empty or not as:

VB Code:

If Range("A1").Value = "" Then
    Debug.Print "A1 Is Empty"
Else
    Debug.Print "A1 Is not Empty"
End If

Quote:

---

Originally Posted by cssriraman

You can check whether the particular cell is empty or not as:

VB Code:

If Range("A1").Value = "" Then
    Debug.Print "A1 Is Empty"
Else
    Debug.Print "A1 Is not Empty"
End If

---

Or the following (to screen out blank spaces):

VB Code:

If Trim(Range("A1").Value) = "" Then
    Debug.Print "A1 Is Empty"
Else
    Debug.Print "A1 Is not Empty"
End If

I ended up using isempty(), but might change it later since its functionality is undefined for datatypes other than strings and used mainly for checking if a variable is initialized. Thanks for the suggestions they might come in handy later.

However, wierdly enough isNumeric() will return true to a cell value that is empty with isEmpty().

isNumeric(ExcelSheet.cells(1,1)) ==> returns true
isEmpty(ExcelSheet.cells(1,1)) ==> returns false