I'm looking for a way to test, in an Excel VBA subroutine, whether a specific
cell value is (1) a numeric value that has been formated as date, (2) a
numeric value that has been formated as time, or (3) a "reggular" numeric
value.

Use the Vartype function from VBA

See Excel VBA help for details.

--
Regards,
Tom Ogilvy


"Jim Watkins" wrote:

I'm looking for a way to test, in an Excel VBA subroutine, whether a specific
cell value is (1) a numeric value that has been formated as date, (2) a
numeric value that has been formated as time, or (3) a "reggular" numeric
value.

Hi Jim, :) I think you should use the worksheet functions 'INFO' and 'CELL'
with appropriate arguments.

"Jim Watkins" wrote:

I'm looking for a way to test, in an Excel VBA subroutine, whether a specific
cell value is (1) a numeric value that has been formated as date, (2) a
numeric value that has been formated as time, or (3) a "reggular" numeric
value.

"Tom Ogilvy" wrote:

Use the Vartype function from VBA

See Excel VBA help for details.

--
Regards,
Tom Ogilvy


I want to use this idea to create a UDF function "isdate", but my VBA
knowledge does not seem up to it: I created the following code in a new
module in my Personal.xls:

Public Function isdate(xCell As Range)
If VarType(xCell) = vbDate Then
isdate = True
Else
isdate = False
End If
End Function

I entered the formula "=isdate(B17)" in a cell, but it only returned
"#NAME?", and I did not know how to debug it. What am I doing wrong?

=Personal.xls!isdate(B17)


Gord Dibben MS Excel MVP

On Wed, 7 Oct 2009 16:56:01 -0700, Hershmab
wrote:



"Tom Ogilvy" wrote:

Use the Vartype function from VBA

See Excel VBA help for details.

--
Regards,
Tom Ogilvy


I want to use this idea to create a UDF function "isdate", but my VBA
knowledge does not seem up to it: I created the following code in a new
module in my Personal.xls:

Public Function isdate(xCell As Range)
If VarType(xCell) = vbDate Then
isdate = True
Else
isdate = False
End If
End Function

I entered the formula "=isdate(B17)" in a cell, but it only returned
"#NAME?", and I did not know how to debug it. What am I doing wrong?

Thanks very much, a very simple solution!

A further question arising from this: is there any other place to store UDFs
so that I can use them without a worksheet qualifier, i.e just like Excel
worksheet functions?

"Gord Dibben" wrote:

=Personal.xls!isdate(B17)


Gord Dibben MS Excel MVP

On Wed, 7 Oct 2009 16:56:01 -0700, Hershmab
wrote:



"Tom Ogilvy" wrote:

Use the Vartype function from VBA

See Excel VBA help for details.

--
Regards,
Tom Ogilvy


I want to use this idea to create a UDF function "isdate", but my VBA
knowledge does not seem up to it: I created the following code in a new
module in my Personal.xls:

Public Function isdate(xCell As Range)
If VarType(xCell) = vbDate Then
isdate = True
Else
isdate = False
End If
End Function

I entered the formula "=isdate(B17)" in a cell, but it only returned
"#NAME?", and I did not know how to debug it. What am I doing wrong?