Vartype, range uninitialized ? Feb2010
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 -- Neal Z |
Vartype, range uninitialized ? Feb2010
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. -- Cheers, Ryan "Neal Zimm" wrote: 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 -- Neal Z |
Vartype, range uninitialized ? Feb2010
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 -- Gary''s Student - gsnu201001 "Neal Zimm" wrote: 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 -- Neal Z |
All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com