How to test cells data type?

[vb_albion]

Hi to all.

How do I test if certain variable is decimal (or integer) number, through a vba?

I need something like:
IF Worksheets("Book1").Cells(1,1).DataType = Decimal THEN ....
and
IF Worksheets("Book2").Cells(1,1).DataType = Integer THEN ...

but I don't know appropriate syntax. :think:


Thanks

[geekgirlau]

There may be a more elegant way to do this, but this works:

Function GetDataType(varValue As Variant)
If IsNumeric(varValue) Then
If InStr(1, varValue, ".") > 0 Then
GetDataType = "Decimal"
Else
GetDataType = "Integer"
End If
Else
GetDataType = "Text"
End If
End Function

[MountainVogu]

Hi

Builds on post at j-walk.com/ss/excel/tips/tip62.htm (http://www.j-walk.com/ss/excel/tips/tip62.htm).

Not the most elegant solution in the world but it works


Option Explicit

Function CellType(cell)
Dim Counter As Long
Dim Result as Boolean
On Error Resume Next
' Returns the cell type of the passed cell
Application.Volatile 'A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.
Select Case True
Case IsEmpty(cell): CellType = "Blank"
Case Application.IsText(cell): CellType = "String"
Case Application.IsLogical(cell): CellType = "Logical"
Case Application.IsErr(cell): CellType = "Error"
Case IsDate(cell): CellType = "Date"
Case InStr(1, cell.Text, ":") <> 0: CellType = "Time"
Case IsNumeric(cell)
For Counter = 1 To 4
Result = TestDataType(cell.Value, Counter)
If Result = True Then Exit For
Next
Select Case Counter
Case 1
CellType = "Integer"
Case 2
CellType = "Long"
Case 3
CellType = "Single"
Case 4
CellType = "Double"
Case Else
CellType = "Unknown"
End Select
End Select
End Function

Function TestDataType(ByRef Value, ByRef DataTest) As Boolean
Dim I As Integer
Dim L As Long
Dim S As Single
Dim D As Double
On Error GoTo Blowout:
Select Case DataTest 'try an make each variable equal to the value
Case 1 ' will trigger error if value not consistent with
I = Value ' data type
Case 2
L = Value
Case 3
S = Value
Case 4
D = Value
Case Else
TestDataType = False
Exit Function
End Select
TestDataType = True
'Check if Integer incorrectly assigned for some reason putting a decimal value
'into an integer variable simply trucates it rather than raising an error !
If DataTest = 1 And InStr(1, Str(Value), ".", vbTextCompare) > 0 And _
TestDataType = True Then TestDataType = False
Exit Function
Blowout:
TestDataType = False
End Function



To use this function in a worskheet, just copy the code and paste it to a module. Then, you can enter a formula such as:
=CellType(A1) in B2 if A1=10.58 B2 will show Long

You can then nest it inside an if statement.

As I say not elegant, I'm sure the regulars can improve it greatly and for my experience this would be appreciated.

TTFN

"Better a bad day on the snow than a good day in the Office"

[vb_albion]

:clap: Great Help.

Thank You VM :friends: :friends:

[RonMcK]

There may be a more elegant way to do this, but this works:

Function GetDataType(varValue As Variant)
If IsNumeric(varValue) Then
If InStr(1, varValue, ".") > 0 Then
GetDataType = "Decimal"
Else
GetDataType = "Integer"
End If
Else
GetDataType = "Text"
End If
End Function Would I be correct in assuming that the ISTEXT() built-in worksheet function does not work on a Mac? This despite its appearing in the Excel for Mac VBA Help files?
:(
Thanks,

Ron