IsNumber in VBA (not IsNumeric)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

??
 
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
 
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
 
Doug and Kevin:

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

Thanks again
 
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)
 
Oh yes, your suggestion was always going to work, I just finished your
explanation as to why <g>

Regards

Bob

Doug Glancy said:
Bob,

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

Thanks,

Doug
 
Thanks Bob! I appreciate it.

Doug

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

Regards

Bob
 
Back
Top