DataType Property

The Excel HELP file demonstrates how to test for data types in a PivotTABLE:

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
Select Case pvtTable.PivotFields("ORDER_DATE").DataType
Case Is = xlText
MsgBox "The field contains text data"
Case Is = xlNumber
MsgBox "The field contains numeric data"
Case Is = xlDate
MsgBox "The field contains date data"
End Select

How do I test for DataTypes in a cell in a regular worksheet (my crude
attempts are)

msgbox ActiveCell.DataType
msgbox Range("A1").DataType

vartype(activecell)

See help on Vartype for the constant values returned.

--
Regards,
Tom Ogilvy


"Access101" <[email protected]> wrote in message
news:[email protected]...
> The Excel HELP file demonstrates how to test for data types in a
PivotTABLE:
>
> Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
> Select Case pvtTable.PivotFields("ORDER_DATE").DataType
> Case Is = xlText
> MsgBox "The field contains text data"
> Case Is = xlNumber
> MsgBox "The field contains numeric data"
> Case Is = xlDate
> MsgBox "The field contains date data"
> End Select
>
> How do I test for DataTypes in a cell in a regular worksheet (my crude
> attempts are)
>
> msgbox ActiveCell.DataType
> msgbox Range("A1").DataType
>

Thanks for you help. I was hoping to be able to test for these DataTypes:

'xlParamTypeBinary
'xlParamTypeChar
'xlParamTypeDecimal
'xlParamTypeFloat
'xlParamTypeLongVarBinary
'xlParamTypeNumeric
'xlParamTypeSmallInt
'xlParamTypeTimestamp
'xlParamTypeUnknown
'xlParamTypeVarChar
'xlParamTypeBigInt
'xlParamTypeBit
'xlParamTypeDate
'xlParamTypeDouble
'xlParamTypeInteger
'xlParamTypeLongVarChar
'xlParamTypeReal
'xlParamTypeTime
'xlParamTypeTinyInt
'xlParamTypeVarBinary
'xlParamTypeWChar

"Tom Ogilvy" wrote:

> vartype(activecell)
>
> See help on Vartype for the constant values returned.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Access101" <[email protected]> wrote in message
> news:[email protected]...
> > The Excel HELP file demonstrates how to test for data types in a
> PivotTABLE:
> >
> > Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
> > Select Case pvtTable.PivotFields("ORDER_DATE").DataType
> > Case Is = xlText
> > MsgBox "The field contains text data"
> > Case Is = xlNumber
> > MsgBox "The field contains numeric data"
> > Case Is = xlDate
> > MsgBox "The field contains date data"
> > End Select
> >
> > How do I test for DataTypes in a cell in a regular worksheet (my crude
> > attempts are)
> >
> > msgbox ActiveCell.DataType
> > msgbox Range("A1").DataType
> >
>
>
>

Numbers are stored as double

If the number is formatted as currency, then it would return currency

if the number is formatted as a date, then it would return a date

then you have string, error and empty.

All the constants that don't fit in that, you would ignore.

Reference your posted sub:

Case Is = xlText
MsgBox "The field contains text data"
Case Is = xlNumber
MsgBox "The field contains numeric data"
Case Is = xlDate
MsgBox "The field contains date data"

--
Regards,
Tom Ogilvy



"Access101" <[email protected]> wrote in message
news:[email protected]...
> Thanks for you help. I was hoping to be able to test for these DataTypes:
>
> 'xlParamTypeBinary
> 'xlParamTypeChar
> 'xlParamTypeDecimal
> 'xlParamTypeFloat
> 'xlParamTypeLongVarBinary
> 'xlParamTypeNumeric
> 'xlParamTypeSmallInt
> 'xlParamTypeTimestamp
> 'xlParamTypeUnknown
> 'xlParamTypeVarChar
> 'xlParamTypeBigInt
> 'xlParamTypeBit
> 'xlParamTypeDate
> 'xlParamTypeDouble
> 'xlParamTypeInteger
> 'xlParamTypeLongVarChar
> 'xlParamTypeReal
> 'xlParamTypeTime
> 'xlParamTypeTinyInt
> 'xlParamTypeVarBinary
> 'xlParamTypeWChar
>
> "Tom Ogilvy" wrote:
>
> > vartype(activecell)
> >
> > See help on Vartype for the constant values returned.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Access101" <[email protected]> wrote in message
> > news:[email protected]...
> > > The Excel HELP file demonstrates how to test for data types in a
> > PivotTABLE:
> > >
> > > Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
> > > Select Case pvtTable.PivotFields("ORDER_DATE").DataType
> > > Case Is = xlText
> > > MsgBox "The field contains text data"
> > > Case Is = xlNumber
> > > MsgBox "The field contains numeric data"
> > > Case Is = xlDate
> > > MsgBox "The field contains date data"
> > > End Select
> > >
> > > How do I test for DataTypes in a cell in a regular worksheet (my crude
> > > attempts are)
> > >
> > > msgbox ActiveCell.DataType
> > > msgbox Range("A1").DataType
> > >
> >
> >
> >