Different results using IsNumber Function on Date in VBA
excel, excel-formula, vba
Solution
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.
Problem
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 ```