Results 1 to 10 of 10

Thread: (Excel)____ VarType returning "wrong" result !

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    552

    (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 ?

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,594

    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.
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    552

    Re: (Excel)____ VarType returning "wrong" result !

    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.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,594

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,594

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,207

    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>

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    552

    Re: (Excel)____ VarType returning "wrong" result !

    Quote Originally Posted by Zvoni View Post
    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.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    552

    Re: (Excel)____ VarType returning "wrong" result !

    Quote Originally Posted by wqweto View Post
    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.

  9. #9
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,207

    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>

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2013
    Posts
    552

    Re: (Excel)____ VarType returning "wrong" result !

    Quote Originally Posted by wqweto View Post
    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
  •  



Click Here to Expand Forum to Full Width