Vartype, range uninitialized ? Feb2010

[Neal Zimm]

Hi All,
I don't understand why in the second MsgBox
the vartype displays as 0 when vTest appears to
be initialized with the right values.

Thanks,
Neal Z.


Sub Test()

Dim vTest As Variant

Set vTest = ActiveSheet

MsgBox VarType(vTest) & " =vartype", , TypeName(vTest)
' vartype shows 9 for object Worksheet


Set vTest = ActiveSheet.Range("b10")

MsgBox VarType(vTest) & " =vartype" _
& vbCr & vTest.Worksheet.Name _
& vbCr & vTest.Address, , TypeName(vTest)
' vartype shows 0 ?, mso docum says uninitialized
' yet the sheet name and address show OK ??
' TypeName shows Range
End Sub

 

[Ryan H]

Look up VarType in the help section. It will explain the constants that you
are getting, 9 & 0.

The ActiveSheet is an Object thus VarType(ActiveSheet) returns 9

B10 must be Empty. Thus VarType(ActiveSheet.Range("B10")) returns 0. Put
some text in B10 and run your code and see what happens. Then put some
numbers in B10 and see what happens.

Hope this helps! If so, let me know, click "YES" below.

 

[Gary''s Student]

I think you fooled Excel by effectively re'Dimming vTest from a worksheet to
a range. This works:

Sub Test()

Dim vTest As Worksheet

Set vTest = ActiveSheet

MsgBox VarType(vTest) & " =vartype", , TypeName(vTest)
' vartype shows 9 for object Worksheet

Dim wTest As Range

Set wTest = ActiveSheet.Range("b10")

MsgBox VarType(vTest) & " =vartype" _
& vbCr & wTest.Worksheet.Name _
& vbCr & wTest.Address, , TypeName(vTest)
' vartype shows 0 ?, mso docum says uninitialized
' yet the sheet name and address show OK ??
' TypeName shows Range
End Sub