Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vartype() and vbObject
HI All,
I'm learning/testing with the vartype function and using variants in called sub procs. Code below is parts of larger macros. Sub Test2 processes TestVar variable depending on its type. 1. In sub Test1, why is vartype of a cell range 8204? Seems 'odd'. The help shows 8192 for an array and 12 for vbVariant. Help is silent on why a cell range's vartype is not vbObject. 2. With more digging, I found the TypeName function. Is: If "Range" = typename(TestVar) then ..... a 'good' way to check for a cell range object ? 3. Assuming an array is NOT a variant array containing different data types in its elements, are the statements below roughly equivalent to test for a numeric array ? (e.g. integer or long) (Note: Coding efficiency not a consideration here) Dim Name as string Dim TestAy As Variant 'code establishing TestAy's element values if isarray(TestAy) then if isnumeric(TestAy(1)) then .... '#1 assumes base 1 option. if "*()" like typename(TestAy) then '#2 name = typename(TestAy) if instr("InteLong", left(name,4)) 0 then ..... end if Thanks. ------------ sub Test1() Dim TstRng As Range Set TstRng = ActiveSheet.Range("p146:w146") MsgBox VarType(TstRng), , "ActiveSheet.Range(p146:w146)" 'got 8204 call Test2(TstRng) end sub sub Test2(TestVar as variant) if vartype(testvar) = vbstring then 'not shown code works when testvar is string elseif vartype(testvar) = vbarray + vbinteger then 'not shown code works when testvar is integer array elseif vartype(testvar) = vbobject then 'thought i'd get sub Test1's TstRng here, but did not. 'using TypeName(testvar) worked. else msgbox "Error vartype " & vartype(testvar) 'tested ok with "bad" input end if end sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vartype() and vbObject
VarType(range-object)) returns the type info about the default property of
the object which is Value (though if a range had not yet been assigned to an object variable then VarType will return vbObject 9). If the range object refers to multiple cells Value returns a variant array, irrespective of the cell contents, hence 8192 array + 12 variant = 8204 If the range refers to a single cell, VarType will return the type of the individual value I don't quite follow your points 2 & 3 but post back if the above does not clarify what you are after. Regards, Peter T "Neal Zimm" wrote in message ... HI All, I'm learning/testing with the vartype function and using variants in called sub procs. Code below is parts of larger macros. Sub Test2 processes TestVar variable depending on its type. 1. In sub Test1, why is vartype of a cell range 8204? Seems 'odd'. The help shows 8192 for an array and 12 for vbVariant. Help is silent on why a cell range's vartype is not vbObject. 2. With more digging, I found the TypeName function. Is: If "Range" = typename(TestVar) then ..... a 'good' way to check for a cell range object ? 3. Assuming an array is NOT a variant array containing different data types in its elements, are the statements below roughly equivalent to test for a numeric array ? (e.g. integer or long) (Note: Coding efficiency not a consideration here) Dim Name as string Dim TestAy As Variant 'code establishing TestAy's element values if isarray(TestAy) then if isnumeric(TestAy(1)) then .... '#1 assumes base 1 option. if "*()" like typename(TestAy) then '#2 name = typename(TestAy) if instr("InteLong", left(name,4)) 0 then ..... end if Thanks. ------------ sub Test1() Dim TstRng As Range Set TstRng = ActiveSheet.Range("p146:w146") MsgBox VarType(TstRng), , "ActiveSheet.Range(p146:w146)" 'got 8204 call Test2(TstRng) end sub sub Test2(TestVar as variant) if vartype(testvar) = vbstring then 'not shown code works when testvar is string elseif vartype(testvar) = vbarray + vbinteger then 'not shown code works when testvar is integer array elseif vartype(testvar) = vbobject then 'thought i'd get sub Test1's TstRng here, but did not. 'using TypeName(testvar) worked. else msgbox "Error vartype " & vartype(testvar) 'tested ok with "bad" input end if end sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vartype() and vbObject
For testing types, I normally use TypeOf Is. E.g.,
If TypeOf R Is Excel.Range Then This is particularly well-suited to circumstances in which two different objects have the same type name. For example, both Word and Excel have an object named "Range". By using TypeName, you cannot be sure whether you have a Word Range or an Excel Range. By prefixing the typelib name ("Excel") to the object name, you can be sure that you are referencing the object type that you think you are. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 12 Dec 2008 07:21:01 -0800, Neal Zimm wrote: HI All, I'm learning/testing with the vartype function and using variants in called sub procs. Code below is parts of larger macros. Sub Test2 processes TestVar variable depending on its type. 1. In sub Test1, why is vartype of a cell range 8204? Seems 'odd'. The help shows 8192 for an array and 12 for vbVariant. Help is silent on why a cell range's vartype is not vbObject. 2. With more digging, I found the TypeName function. Is: If "Range" = typename(TestVar) then ..... a 'good' way to check for a cell range object ? 3. Assuming an array is NOT a variant array containing different data types in its elements, are the statements below roughly equivalent to test for a numeric array ? (e.g. integer or long) (Note: Coding efficiency not a consideration here) Dim Name as string Dim TestAy As Variant 'code establishing TestAy's element values if isarray(TestAy) then if isnumeric(TestAy(1)) then .... '#1 assumes base 1 option. if "*()" like typename(TestAy) then '#2 name = typename(TestAy) if instr("InteLong", left(name,4)) 0 then ..... end if Thanks. ------------ sub Test1() Dim TstRng As Range Set TstRng = ActiveSheet.Range("p146:w146") MsgBox VarType(TstRng), , "ActiveSheet.Range(p146:w146)" 'got 8204 call Test2(TstRng) end sub sub Test2(TestVar as variant) if vartype(testvar) = vbstring then 'not shown code works when testvar is string elseif vartype(testvar) = vbarray + vbinteger then 'not shown code works when testvar is integer array elseif vartype(testvar) = vbobject then 'thought i'd get sub Test1's TstRng here, but did not. 'using TypeName(testvar) worked. else msgbox "Error vartype " & vartype(testvar) 'tested ok with "bad" input end if end sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vartype() and vbObject
Peter,
Thanks, item 2 is moot with your answer to 1. Item 3 (since #1 was not working when I posted) was a round about way using a mask and the like verb to test the contents of what TypeName function returns. It worked for me, but seemed a bit overwrought in the coding. -- Neal Z "Peter T" wrote: VarType(range-object)) returns the type info about the default property of the object which is Value (though if a range had not yet been assigned to an object variable then VarType will return vbObject 9). If the range object refers to multiple cells Value returns a variant array, irrespective of the cell contents, hence 8192 array + 12 variant = 8204 If the range refers to a single cell, VarType will return the type of the individual value I don't quite follow your points 2 & 3 but post back if the above does not clarify what you are after. Regards, Peter T "Neal Zimm" wrote in message ... HI All, I'm learning/testing with the vartype function and using variants in called sub procs. Code below is parts of larger macros. Sub Test2 processes TestVar variable depending on its type. 1. In sub Test1, why is vartype of a cell range 8204? Seems 'odd'. The help shows 8192 for an array and 12 for vbVariant. Help is silent on why a cell range's vartype is not vbObject. 2. With more digging, I found the TypeName function. Is: If "Range" = typename(TestVar) then ..... a 'good' way to check for a cell range object ? 3. Assuming an array is NOT a variant array containing different data types in its elements, are the statements below roughly equivalent to test for a numeric array ? (e.g. integer or long) (Note: Coding efficiency not a consideration here) Dim Name as string Dim TestAy As Variant 'code establishing TestAy's element values if isarray(TestAy) then if isnumeric(TestAy(1)) then .... '#1 assumes base 1 option. if "*()" like typename(TestAy) then '#2 name = typename(TestAy) if instr("InteLong", left(name,4)) 0 then ..... end if Thanks. ------------ sub Test1() Dim TstRng As Range Set TstRng = ActiveSheet.Range("p146:w146") MsgBox VarType(TstRng), , "ActiveSheet.Range(p146:w146)" 'got 8204 call Test2(TstRng) end sub sub Test2(TestVar as variant) if vartype(testvar) = vbstring then 'not shown code works when testvar is string elseif vartype(testvar) = vbarray + vbinteger then 'not shown code works when testvar is integer array elseif vartype(testvar) = vbobject then 'thought i'd get sub Test1's TstRng here, but did not. 'using TypeName(testvar) worked. else msgbox "Error vartype " & vartype(testvar) 'tested ok with "bad" input end if end sub -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vartype() and vbObject
Chip, good tip as always, Thanks.
-- Neal Z "Peter T" wrote: VarType(range-object)) returns the type info about the default property of the object which is Value (though if a range had not yet been assigned to an object variable then VarType will return vbObject 9). If the range object refers to multiple cells Value returns a variant array, irrespective of the cell contents, hence 8192 array + 12 variant = 8204 If the range refers to a single cell, VarType will return the type of the individual value I don't quite follow your points 2 & 3 but post back if the above does not clarify what you are after. Regards, Peter T "Neal Zimm" wrote in message ... HI All, I'm learning/testing with the vartype function and using variants in called sub procs. Code below is parts of larger macros. Sub Test2 processes TestVar variable depending on its type. 1. In sub Test1, why is vartype of a cell range 8204? Seems 'odd'. The help shows 8192 for an array and 12 for vbVariant. Help is silent on why a cell range's vartype is not vbObject. 2. With more digging, I found the TypeName function. Is: If "Range" = typename(TestVar) then ..... a 'good' way to check for a cell range object ? 3. Assuming an array is NOT a variant array containing different data types in its elements, are the statements below roughly equivalent to test for a numeric array ? (e.g. integer or long) (Note: Coding efficiency not a consideration here) Dim Name as string Dim TestAy As Variant 'code establishing TestAy's element values if isarray(TestAy) then if isnumeric(TestAy(1)) then .... '#1 assumes base 1 option. if "*()" like typename(TestAy) then '#2 name = typename(TestAy) if instr("InteLong", left(name,4)) 0 then ..... end if Thanks. ------------ sub Test1() Dim TstRng As Range Set TstRng = ActiveSheet.Range("p146:w146") MsgBox VarType(TstRng), , "ActiveSheet.Range(p146:w146)" 'got 8204 call Test2(TstRng) end sub sub Test2(TestVar as variant) if vartype(testvar) = vbstring then 'not shown code works when testvar is string elseif vartype(testvar) = vbarray + vbinteger then 'not shown code works when testvar is integer array elseif vartype(testvar) = vbobject then 'thought i'd get sub Test1's TstRng here, but did not. 'using TypeName(testvar) worked. else msgbox "Error vartype " & vartype(testvar) 'tested ok with "bad" input end if end sub -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing variable to VarType of other Variable | Excel Programming | |||
Question about VarType | Excel Programming |