Different results using IsNumber Function on Date in VBA

Question

Say cell A1 has a Date: 1/1/2017.

Then the macro below gives different results:

• first - True
• second - False.

What is the reason of this difference? How should I use IsNumeric correctly?

Sub TestIsNumber()
    MsgBox WorksheetFunction.IsNumber(Cells(1, 1))
    MsgBox WorksheetFunction.IsNumber(Cells(1, 1).Value)
End Sub

Answer 1

It's .Value vs .Value2

When the cell is formatted as date, .Value converts it to a Variant/Date datatype, while .Value2 returns its embedded numeric number (Variant/Double). .Value2 either returns a string, a number or an error variant. It doesn't convert to date or currency.

.Value2 is the recommended way to read cell values, use it to always have the numeric representation of dates, which is faster and still permits you to manipulate it in VBA (compare it to another date, convert it back to Date datatype, etc..).

Notice that the version WorksheetFunction.IsNumber(Cells(1, 1)) invoked the Array version of Excel's IsNumber (It's similar to function overloading in C++, and the best matched overload is invoked). Hence the Range Object was sent to the function as parameter, not its .Value (implicit call to .value did not happen here). Excel worked on the embedded .Value2 as if you type =IsNumber(A1) in another cell, which returns TRUE in Excel.