(Excel)____ VarType returning "wrong" result !
Does anybody know why VarType returns vbString when it should be returning vbObject ?
Code:
Sub Test()
Dim v As Variant 'As Object
Set v = Application
MsgBox VarType(v) '<== returns 8(vbString) instead of 9(vbObject) !!
End Sub
when assigning other objects to the variable other than "Application" , it seems to work as expected.
I have also tried Set v = Excel.Application and Set v = Application.Application but still no joy.
TypeName does work fine and returns Application as expected.
Am I missing something ?
Re: (Excel)____ VarType returning "wrong" result !
From: https://docs.microsoft.com/en-us/off...rtype-function
Quote:
If an object is passed and has a default property, VarType(object) returns the type of the object's default property.
Re: (Excel)____ VarType returning "wrong" result !
Quote:
Originally Posted by
Zvoni
Thanks.
I am aware of that but I suppose that using the Set keyword would resolve the variable to an object type.
Also, this issue happens only with the Application object ... Tried with UserForm, Workbook, Sheet etc and they all return vbObject.
Re: (Excel)____ VarType returning "wrong" result !
All the others don't have a default-property?
I just did a
Code:
Sub main()
Debug.Print Application
End Sub
'Prints "Microsoft Excel"
The Default-Property of the Application-Object is the Name-Property
Try a Debug.Print with any of the other Objects (Debug.Print ThisWorkbook / Debug.Print Workbooks(1)).
You get a runtime-Error 438 - Method not supported
Re: (Excel)____ VarType returning "wrong" result !
Quick and dirty workaround:
Code:
Function MyVarType(ByRef v As Variant) As VbVarType
If TypeOf v Is Object Then
MyVarType = vbObject
Else
MyVarType = VarType(v)
End If
End Function
Sub main()
Dim v As Variant
Set v = Application
Debug.Print MyVarType(v)
End Sub
Re: (Excel)____ VarType returning "wrong" result !
Here is one-liner VarType replacement
Code:
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Function MyVarType(ByRef v As Variant) As VbVarType
Call CopyMemory(MyVarType, v, 2)
End Function
Private Sub Form_Load()
Dim v As Variant
Set v = CreateObject("Excel.Application")
Debug.Print VarType(v), MyVarType(v)
End Sub
cheers,
</wqw>
Re: (Excel)____ VarType returning "wrong" result !
Quote:
Originally Posted by
Zvoni
Quick and dirty workaround:
Code:
Function MyVarType(ByRef v As Variant) As VbVarType
If TypeOf v Is Object Then
MyVarType = vbObject
Else
MyVarType = VarType(v)
End If
End Function
Sub main()
Dim v As Variant
Set v = Application
Debug.Print MyVarType(v)
End Sub
I had a similar workaround ... Thanks very much for your interest.
Re: (Excel)____ VarType returning "wrong" result !
Quote:
Originally Posted by
wqweto
Here is one-liner VarType replacement
Code:
Option Explicit
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Function MyVarType(ByRef v As Variant) As VbVarType
Call CopyMemory(MyVarType, v, 2)
End Function
Private Sub Form_Load()
Dim v As Variant
Set v = CreateObject("Excel.Application")
Debug.Print VarType(v), MyVarType(v)
End Sub
cheers,
</wqw>
Hi wqweto,
Do those first 2 extracted Bytes correspond to the following ? :
From the Variant Type Documentation:
A variable of variant type, for brevity called a "variant", as defined in Visual Basic, needs 16 bytes storage and its layout is as follows:
Offset Size Description
0 2 The value returned by VarType; specifies what kind of data the variant contains.
2 6 Reserved bytes; should be set to zero.
8 up to 8 The data the variant contains.
Re: (Excel)____ VarType returning "wrong" result !
Yes, but description "The value returned by VarType; specifies what kind of data the variant contains" is misleading with couple of gotchas: 1. object's default property is evaluated and 2. VT_BYREF flag is removed
A MyVarType replacement is usually what you want to use instead of the crippled built-in VarType.
cheers,
</wqw>
Re: (Excel)____ VarType returning "wrong" result !
Quote:
Originally Posted by
wqweto
Yes, but description "The value returned by VarType; specifies what kind of data the variant contains" is misleading with couple of gotchas: 1. object's default property is evaluated and 2. VT_BYREF flag is removed
A MyVarType replacement is usually what you want to use instead of the crippled built-in VarType.
cheers,
</wqw>
Cool !:)
Thanks.