none
VBA Worksheet return type RRS feed

  • Question

  • Hi ,

    I am wonder why in excel VBA , Activesheet property returns an Object(variant) . I expected that returning type will be a Worksheet object.

    thankts


    • Edited by farshid mi Sunday, February 4, 2018 10:16 AM
    Sunday, February 4, 2018 10:15 AM

Answers

  • The Answer is:

    Intellisense doesn't appear because the Worksheets method can return either a single worksheet (with all the usual properties or methods) or an array of worksheets, which have their own methods (like FillAcrossSheets)

    That's why it's convenient to assign a worksheet variable.

    source:

    https://www.excelforum.com/excel-programming-vba-macros/1218427-why-worksheets-1-is-an-object.html#post4835925

    Sample:

    x = Array("Sheet1", "Sheet5", "Sheet7") 
    
    Worksheets(x).FillAcrossSheets _ 
    
    Worksheets("Sheet1").Range("A1:C5")



    • Marked as answer by farshid mi Monday, February 5, 2018 5:39 AM
    • Edited by farshid mi Monday, February 5, 2018 5:40 AM
    Monday, February 5, 2018 5:38 AM

All replies

  • This is because the active sheet could be a worksheet or a chart sheet (or even an Excel 4.0 macro sheet or Excel 5.0 dialog sheet). Since ActiveSheet doesn't have a single type, it is defined as an Object.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 4, 2018 10:19 AM
  • Thanks ,   :)

    And what about Worksheets(1) , It definitely is a worksheet BUT vba returns an Object:

    And for test I add a Macro4 and a Chart Sheet , the following code show Only worksheets:

    For Each sh In Worksheets
        Debug.Print sh.Name
    Next sh


    • Edited by farshid mi Sunday, February 4, 2018 11:06 AM
    Sunday, February 4, 2018 10:58 AM
  • You need to change it to
    For Each sh In Sheets
        Debug.Print sh.Name
    Next sh


    Sunday, February 4, 2018 5:07 PM
  • I know it , But I want to know why worksheets(1) is an object . I what know how VBA designer think .

    • Edited by farshid mi Sunday, February 4, 2018 5:13 PM
    Sunday, February 4, 2018 5:12 PM
  • I know it , But I want to know why worksheets(1) is an object . I what know how VBA designer think .

    A Worksheets object is a VBA collection.  Collections are used extensively in Office Object Models to hold various different types of data.  If you examine the objects available in Excel's Object Model you will notice a pattern ... Worksheets is a collection of Worksheet objects, Panes is a collection of Pane objects, Charts is a collection of Chart objects and so forth.

    As documented at Collection Object, "The items, ormembers, in a collection need only be related by the fact that they exist in the collection. Members of a collection don't have to share the same data type. "

    So it makes sense that when an item is retrieved from a collection that the collection returns an Object.


    • Edited by RLWA32 Sunday, February 4, 2018 6:56 PM
    Sunday, February 4, 2018 6:55 PM
  • Thanks for help.

    And My question is why VBE don't show worksheets(1) members in editor?

    I expect VBE show all worksheets(1) member because  IF worksheets(1) returns a worksheets Object,we must see all member in  list member .
    And I know VBE dont show list member when it cann't recognize Object type . And I want to ask why VBE can't recognize worksheets(1) is a worksheet object?


    • Edited by farshid mi Sunday, February 4, 2018 7:49 PM
    Sunday, February 4, 2018 7:43 PM
  • You'd have to ask Microsoft, but I'm fairly certain they won't tell us.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 4, 2018 8:14 PM
  • :-)  
    Sunday, February 4, 2018 8:22 PM
  • Sub Temp()
    Dim Wksht As Worksheet

        For Each Wksht In Worksheets
            Debug.Print Wksht.Name
        Next
    End Sub

    Also works


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Sunday, February 4, 2018 8:23 PM
  • Thanks for help.

    And My question is why VBE don't show worksheets(1) members in editor?

    I expect VBE show all worksheets(1) member because  IF worksheets(1) returns a worksheets Object,we must see all member in  list member .
    And I know VBE dont show list member when it cann't recognize Object type . And I want to ask why VBE can't recognize worksheets(1) is a worksheet object?


    The viewers that you are using display what is present in the Excel type library.  A type library contains information about COM objects, their properties, methods, and other details.  The Excel type library specifically indicates that the Item property of a Workbooks object will return a Workbook

    .  You can see this from the view of the Excel type library itself that can be obtained by using the Windows SDK oleview.exe utility.  Following is a screenshot -

    However, the Excel type library for a Worksheets object Item property specifies that it will return IDispatch ( this is the base interface of all OLE Automation and is equivalent to a VBA Object type).  See the following screenshot -


    • Edited by RLWA32 Sunday, February 4, 2018 9:19 PM
    Sunday, February 4, 2018 9:18 PM
  • The Answer is:

    Intellisense doesn't appear because the Worksheets method can return either a single worksheet (with all the usual properties or methods) or an array of worksheets, which have their own methods (like FillAcrossSheets)

    That's why it's convenient to assign a worksheet variable.

    source:

    https://www.excelforum.com/excel-programming-vba-macros/1218427-why-worksheets-1-is-an-object.html#post4835925

    Sample:

    x = Array("Sheet1", "Sheet5", "Sheet7") 
    
    Worksheets(x).FillAcrossSheets _ 
    
    Worksheets("Sheet1").Range("A1:C5")



    • Marked as answer by farshid mi Monday, February 5, 2018 5:39 AM
    • Edited by farshid mi Monday, February 5, 2018 5:40 AM
    Monday, February 5, 2018 5:38 AM
  • That's not really the answer to your own your original question, which was correctly answered by Hans.

    There's a bit more to it though. "Worksheets" is actually a "Sheets" class object. Sheets can include most sheet types that might exist in a Workbook, though from memory perhaps not Dialogsheets (I'd need to double check). Worksheets, although a derivative of the Sheets class can only refer to one type of sheet - namely Worksheet.

    Contrary to what you said intellisense does appear with a declared Worksheets variable, whether the object includes one or multiple worksheets, it will be the same as what appears with a declared Sheets object.

    There isn't a named "Sheet" (singular) object, instead individual sheets will be named as Worksheet or Chart.

    You should refer to unknown or mixed sheet types as in the Sheets collection as Object, for example -
    Dim shts As Sheets, objSht As Object
    Set shts = ActiveWorkbook.Sheets
    For Each objSht in shts

    I can't imagine why you marked Rod's reply as abusive, if not intentional you might consider removing it.

    Monday, February 5, 2018 4:50 PM
    Moderator