IsNumber in VBA (not IsNumeric)
 

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

IsNumber in VBA (not IsNumeric)
 

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

"Gary''s Student" wrote in message
...
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

IsNumber in VBA (not IsNumeric)
 

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

--
Kevin Backmann


"Gary''s Student" wrote:

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

IsNumber in VBA (not IsNumeric)
 

Doug and Kevin:

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

Thanks again
--
Gary's Student


"Kevin B" wrote:

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

--
Kevin Backmann


"Gary''s Student" wrote:

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

IsNumber in VBA (not IsNumeric)
 

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" wrote in message
...
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

"Gary''s Student" wrote in
message
...
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

IsNumber in VBA (not IsNumeric)
 

Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

"Gary''s Student" wrote in
message
...
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

IsNumber in VBA (not IsNumeric)
 

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

Regards

Bob

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

"Gary''s Student" wrote in
message
...
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

IsNumber in VBA (not IsNumeric)
 

Thanks Bob! I appreciate it.

Doug

"Bob Phillips" wrote in message
...
Oh yes, your suggestion was always going to work, I just finished your
explanation as to why <g

Regards

Bob

"Doug Glancy" wrote in message
...
Bob,

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

Thanks,

Doug

"Bob Phillips" wrote in message
...
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" wrote in message
...
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

"Gary''s Student" wrote in
message
...
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