-
Jul 13th, 2021, 05:21 PM
#1
Thread Starter
Fanatic Member
(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 ?
-
Jul 14th, 2021, 01:24 AM
#2
Re: (Excel)____ VarType returning "wrong" result !
From: https://docs.microsoft.com/en-us/off...rtype-function
If an object is passed and has a default property, VarType( object) returns the type of the object's default property.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jul 14th, 2021, 01:47 AM
#3
Thread Starter
Fanatic Member
Re: (Excel)____ VarType returning "wrong" result !
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.
-
Jul 14th, 2021, 02:42 AM
#4
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jul 14th, 2021, 02:51 AM
#5
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jul 14th, 2021, 03:57 AM
#6
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>
-
Jul 14th, 2021, 04:12 AM
#7
Thread Starter
Fanatic Member
Re: (Excel)____ VarType returning "wrong" result !
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.
-
Jul 14th, 2021, 04:16 AM
#8
Thread Starter
Fanatic Member
Re: (Excel)____ VarType returning "wrong" result !
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.
-
Jul 14th, 2021, 04:21 AM
#9
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>
-
Jul 14th, 2021, 04:28 AM
#10
Thread Starter
Fanatic Member
Re: (Excel)____ VarType returning "wrong" result !
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|