Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Vartype() and vbObject | Excel Programming | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Passing variable to VarType of other Variable | Excel Programming | |||
Question about VarType | Excel Programming |