Forum Discussion
Solved
IsNumber get diffrent resuts in function and in VBA
I have similar sheet with date in one cell. When I used sheet function IsNumber (Je.Číslo in Czech language version) on this date cell, result is true. When I used the same function in VBA (Applica...
- With the cell formatted as a date, using the .value method returns a variant/date type that isnumber doesn't recognize as a number.
But, .Value2 doesn't convert to date data types, so would return a variant/double data type. Also, you could just pass it the range object and it will use value2 as the default property.
In addition, vba has an isdate function, which would correctly interpret either .value or just the range object (but would not recognize .value2 as a date).
I think one of these may serve your purpose:
application.WorksheetFunction.IsNumber(cells(2,1))
application.WorksheetFunction.IsNumber(cells(2,1).value2)
isdate(cells(2,1).value)
isdate(cells(2,1))
I don't know how attach a workbook. So I describe it.
Datacell is on A2 or Cells(2,1). Datacell format is "*14.03.2012" or "dd.mm.rrrr" (on cell - czech language version), but Cells(2,1).NumberFormat gives me "m/d/yyyy".
Formula is "=JE.ČISLO(A2)" (on cell - czech language version). FormulaCell.Formula = "=ISNUMBER(A2)". Result = true.
Function is "=Application.WorksheetFunction.IsNumber(Cells(2,1).Value)". Result is false.
Datacell is on A2 or Cells(2,1). Datacell format is "*14.03.2012" or "dd.mm.rrrr" (on cell - czech language version), but Cells(2,1).NumberFormat gives me "m/d/yyyy".
Formula is "=JE.ČISLO(A2)" (on cell - czech language version). FormulaCell.Formula = "=ISNUMBER(A2)". Result = true.
Function is "=Application.WorksheetFunction.IsNumber(Cells(2,1).Value)". Result is false.
With the cell formatted as a date, using the .value method returns a variant/date type that isnumber doesn't recognize as a number.
But, .Value2 doesn't convert to date data types, so would return a variant/double data type. Also, you could just pass it the range object and it will use value2 as the default property.
In addition, vba has an isdate function, which would correctly interpret either .value or just the range object (but would not recognize .value2 as a date).
I think one of these may serve your purpose:
application.WorksheetFunction.IsNumber(cells(2,1))
application.WorksheetFunction.IsNumber(cells(2,1).value2)
isdate(cells(2,1).value)
isdate(cells(2,1))
But, .Value2 doesn't convert to date data types, so would return a variant/double data type. Also, you could just pass it the range object and it will use value2 as the default property.
In addition, vba has an isdate function, which would correctly interpret either .value or just the range object (but would not recognize .value2 as a date).
I think one of these may serve your purpose:
application.WorksheetFunction.IsNumber(cells(2,1))
application.WorksheetFunction.IsNumber(cells(2,1).value2)
isdate(cells(2,1).value)
isdate(cells(2,1))
- Now it works correct. Thank you.
Libor