IsNumber get diffrent resuts in function and in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2618885%22%20slang%3D%22en-US%22%3EIsNumber%20get%20diffrent%20resuts%20in%20function%20and%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2618885%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20similar%20sheet%20with%20date%20in%20one%20cell.%3C%2FP%3E%3CP%3EWhen%20I%20used%20sheet%20function%20IsNumber%20(Je.%C4%8C%C3%ADslo%20in%20Czech%20language%20version)%20on%20this%20date%20cell%2C%20result%20is%20true.%3C%2FP%3E%3CP%3EWhen%20I%20used%20the%20same%20function%20in%20VBA%20(Application.WorksheetFunction.IsNumber)%20on%20this%20date%20cell%2C%20result%20is%20false.%3C%2FP%3E%3CP%3EWhy%3F%20Thank%20you%20for%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELibor%20Krkoska%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2618885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619441%22%20slang%3D%22en-US%22%3ERe%3A%20IsNumber%20get%20diffrent%20resuts%20in%20function%20and%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122024%22%20target%3D%22_blank%22%3E%40LiborKrk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20cannot%20reproduce%20the%20problem%20in%20the%20English%20language%20version%20of%20Excel.%3C%2FP%3E%0A%3CP%3EWhich%20date%20did%20you%20use%3F%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20workbook%20demonstrating%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2622891%22%20slang%3D%22en-US%22%3ERe%3A%20IsNumber%20get%20diffrent%20resuts%20in%20function%20and%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2622891%22%20slang%3D%22en-US%22%3EI%20don't%20know%20how%20attach%20a%20workbook.%20So%20I%20describe%20it.%3CBR%20%2F%3EDatacell%20is%20on%20A2%20or%20Cells(2%2C1).%20Datacell%20format%20is%20%22*14.03.2012%22%20or%20%22dd.mm.rrrr%22%20(on%20cell%20-%20czech%20language%20version)%2C%20but%20Cells(2%2C1).NumberFormat%20gives%20me%20%22m%2Fd%2Fyyyy%22.%3CBR%20%2F%3EFormula%20is%20%22%3DJE.%C4%8CISLO(A2)%22%20(on%20cell%20-%20czech%20language%20version).%20FormulaCell.Formula%20%3D%20%22%3DISNUMBER(A2)%22.%20Result%20%3D%20true.%3CBR%20%2F%3EFunction%20is%20%22%3DApplication.WorksheetFunction.IsNumber(Cells(2%2C1).Value)%22.%20Result%20is%20false.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2622990%22%20slang%3D%22en-US%22%3ERe%3A%20IsNumber%20get%20diffrent%20resuts%20in%20function%20and%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2622990%22%20slang%3D%22en-US%22%3EWith%20the%20cell%20formatted%20as%20a%20date%2C%20using%20the%20.value%20method%20returns%20a%20variant%2Fdate%20type%20that%20isnumber%20doesn't%20recognize%20as%20a%20number.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20.Value2%20doesn't%20convert%20to%20date%20data%20types%2C%20so%20would%20return%20a%20variant%2Fdouble%20data%20type.%20Also%2C%20you%20could%20just%20pass%20it%20the%20range%20object%20and%20it%20will%20use%20value2%20as%20the%20default%20property.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20addition%2C%20vba%20has%20an%20isdate%20function%2C%20which%20would%20correctly%20interpret%20either%20.value%20or%20just%20the%20range%20object%20(but%20would%20not%20recognize%20.value2%20as%20a%20date).%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20one%20of%20these%20may%20serve%20your%20purpose%3A%3CBR%20%2F%3Eapplication.WorksheetFunction.IsNumber(cells(2%2C1))%3CBR%20%2F%3Eapplication.WorksheetFunction.IsNumber(cells(2%2C1).value2)%3CBR%20%2F%3Eisdate(cells(2%2C1).value)%3CBR%20%2F%3Eisdate(cells(2%2C1))%3C%2FLINGO-BODY%3E
New Contributor

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 (Application.WorksheetFunction.IsNumber) on this date cell, result is false.

Why? Thank you for help.

 

Libor Krkoska

4 Replies

@LiborKrk 

I cannot reproduce the problem in the English language version of Excel.

Which date did you use?

Could you attach a workbook demonstrating the problem?

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.
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))
Now it works correct. Thank you.
Libor