Question about VarType

[bill]

With the following macro if a cell has a value of 5 in it, the macro
identifies it as a double, I would have expected it to come back as an
integer. Can you tell me what I'm doing incorrectly?

tia

Sub DisplayCellDataAttributes()

If Not RangeTools.IsRangeValid Then Exit Sub

Application.ScreenUpdating = False

For Each rngCell In Selection

If (VarType(rngCell.Cells) = vbInteger) Then MsgBox
rngCell.Address & vbCrLf & vbCrLf & " Cell DATA Format - Integer" &
vbCrLf & vbCrLf & "Data: " & rngCell.Cells & vbCrLf & vbCrLf &
"VarType: " & VarType(rngCell.Cells)
If (VarType(rngCell.Cells) = vbLong) Then MsgBox
rngCell.Address & vbCrLf & vbCrLf & " Cell DATA Format - Long" &
vbCrLf & vbCrLf & "Data: " & rngCell.Cells & vbCrLf & vbCrLf &
"VarType: " & VarType(rngCell.Cells)
If (VarType(rngCell.Cells) = vbDouble) Then MsgBox
rngCell.Address & vbCrLf & vbCrLf & " Cell DATA Format - Double" &
vbCrLf & vbCrLf & "Data: " & rngCell.Cells & vbCrLf & vbCrLf &
"VarType: " & VarType(rngCell.Cells)


Next

Application.ScreenUpdating = True

End Sub

 

[Bill Li]

Hi Bill,

You should use IF-ELSEIF-END. Please try the following one.

Sub DisplayCellDataAttributes()


Application.ScreenUpdating = False

For Each rngCell In Selection

If (VarType(rngCell.Cells) = vbInteger) Then
MsgBox rngCell.Address & vbCrLf & vbCrLf & "
Cell DATA Format - Integer" _
& vbCrLf & vbCrLf & "Data: " &
rngCell.Cells & vbCrLf & vbCrLf _
& "VarType: " & VarType(rngCell.Cells)

ElseIf (VarType(rngCell.Cells) = vbLong) Then
MsgBox rngCell.Address & vbCrLf & vbCrLf & "
Cell DATA Format - Long" _
& vbCrLf & vbCrLf & "Data: " &
rngCell.Cells & vbCrLf & vbCrLf _
& "VarType: " & VarType(rngCell.Cells)
ElseIf (VarType(rngCell.Cells) = vbDouble) Then
MsgBox rngCell.Address & vbCrLf & vbCrLf & "
Cell DATA Format - Double" _
& vbCrLf & vbCrLf & "Data: " &
rngCell.Cells & vbCrLf & vbCrLf _
& "VarType: " & VarType(rngCell.Cells)
End If

Next

Application.ScreenUpdating = True

End Sub


Best Regards

Bill

 

[keepitcool]

Internally excels computes with something similar to the double data
type, which is how any spreadsheet calculates...

Sub test()
[a1] = CLng(1)
MsgBox TypeName([a1].Value)
End Sub

For numbers the Value property returns ... a Double (always!)

Step 1:
retrieve the cell.value into a Double variable.

Step2:
then test converting it to several other datatypes
if clng(x)=x then ''it maybe a long

Step3:
decide the most likely outcome and report it.
(which is what excel tries to do)


Sub test()
Dim res(1 To 5, 1 To 3) As Variant
Dim i%, j%, s$

On Error Resume Next
With ActiveSheet
For i = 1 To 5
.Cells(i) = Choose(i, 1, 1.1, "text", Date, Time)
res(i, 1) = .Cells(i).Value
res(i, 2) = TypeName(res(i, 1))
res(i, 3) = VarType(res(i, 1))
Next
End With

For i = 1 To 5
For j = 1 To 3
s = s & vbTab & res(i, j)
Next
s = s & vbNewLine
Next

MsgBox s

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >