IsNumber in VBA (not IsNumeric)

[Guest]

If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns TRUE.

Why does:

=wtf(A1) return FALSE for:

Function wtf(r As Range) As Boolean
wtf = Application.WorksheetFunction.IsNumber(r.Value)
End Function

??

 

[Doug Glancy]

Gary"s Student,

I forget the explanation for why, but it's something to do with Value and
Dates. If you change Value to Value2 it will work (or leave off the
property altogether). If you Google Dates and Value2 I think you'll find
the explanation in a past post in this group.

hth,

Doug

 

[Guest]

Would this variant work for you? If the boolean variable comes up false you
can use the VB IsDate function to see if it's a date. Apparently IsNumber
differentiates between a date value and a numeric one.

Function wtf(r As Range) As Boolean

Dim blnIsNum As Boolean

blnIsNum = Application.WorksheetFunction.IsNumber(r.Value)

If Not blnIsNum Then blnIsNum = IsDate(r.Value)

wtf = blnIsNum

End Function

 

[Guest]

Doug and Kevin:

Thank you both. Using Value2 in the tiny UDF gets an exact match to using
ISNUMBER() in the worksheet.

Thanks again

 

[Bob Phillips]

Value2 doesn't use the currency or date data types, so they become numeric.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Doug Glancy]

Bob,

So do you think my solution will work for the OP?

Thanks,

Doug

 

[Bob Phillips]

Oh yes, your suggestion was always going to work, I just finished your
explanation as to why <g>

Regards

Bob

Bob,

So do you think my solution will work for the OP?

Thanks,

Doug

 

[Doug Glancy]

Thanks Bob! I appreciate it.

Doug

Oh yes, your suggestion was always going to work, I just finished your
explanation as to why <g>

Regards

Bob