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

DataType Property
 

vartype(activecell)

See help on Vartype for the constant values returned.

--
Regards,
Tom Ogilvy


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

DataType Property
 

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

DataType Property
 

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