N
Neal Zimm
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
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