none
Where can I find the details of object properties? RRS feed

  • Question

  • I'm sure this is very rudimentary for most members of this forum, but it's something I encounter constantly working with Excel VBA. Specifically, my question is where I can find a comprehensive list of property settings for various objects. As an example, the following is the sample code from the object model's article on the Range object:

      

    Sub SetRangeBorder() 

     With Worksheets("Sheet1").Range("B2").Borders(xlEdgeBottom) 
     .LineStyle = xlContinuous 
     .Weight = xlThin 
     .ColorIndex = 3 
     End With 

    End Sub

    In terms of details (with the exception of code samples), the object model only goes so far as Range > Borders. What I'd find extremely helpful is a resource with lists of "sub-properties" like xlEdgeBottom > LineStyle > xlContinuous. Any nudges in the right direction will be much appreciated.

    Monday, August 17, 2015 6:34 PM

Answers

  • I'm sure this is very rudimentary for most members of this forum, but it's something I encounter constantly working with Excel VBA. 

    For the experienced users/programmers you're right, but it is not so easy to find the way through this jungle.

    There are 2 things (except the documentation) that you can use.

    a) Intellisense.

    When you type "Worksheets(" into the VBA editor, you'll see a tooltip that shows that the returned type is Object. As Object is "an empty class" the Intellisense can not provide any help.

    But when you declare the variables with the correct object type, you get the list of all things during you write. Copy this 2 rows:

      Dim Ws As Worksheet
      Set Ws = Worksheets("Sheet1")

    Then write
      Ws.
    and immediately after the dot you get a dropdown with all possible methods/functions/properties. Type an R and you'll see that it jumps to Range, then hit TAB and Range is written into the VBA editor.

    (If you can't see the Intellisense, click Tools\Options and in the Editor tab check all, except the 2 box at the top.)

    b) Object Browser

    In the VBA editor press F2, in the text box on the left of the binoculars type Borders and click the binoculars. As you see the Borders class has an Item property, when you select it, you can see the declaration below:

    Property Item(Index As XlBordersIndex) As Border

    As you see XlBordersIndex and Border is green, means you can click it and get directly to the Enum resp. Class.

    Inside the Border class you can see the LineStyle property, which has the Variant datatype... not really helpful.

    But when you select it and press F1, you'll get the Excel VBA help for this property directly. In the help you can read that you can write a xlLineStyle constant into this property and a short click in the help shows all possible values.

    Andreas.

    Monday, August 17, 2015 7:23 PM

All replies

  • VBA Excel documentation to download:

    https://www.microsoft.com/en-us/download/details.aspx?id=40326

    all objects, properties, methods and so on

    Monday, August 17, 2015 6:49 PM
  • I'm sure this is very rudimentary for most members of this forum, but it's something I encounter constantly working with Excel VBA. 

    For the experienced users/programmers you're right, but it is not so easy to find the way through this jungle.

    There are 2 things (except the documentation) that you can use.

    a) Intellisense.

    When you type "Worksheets(" into the VBA editor, you'll see a tooltip that shows that the returned type is Object. As Object is "an empty class" the Intellisense can not provide any help.

    But when you declare the variables with the correct object type, you get the list of all things during you write. Copy this 2 rows:

      Dim Ws As Worksheet
      Set Ws = Worksheets("Sheet1")

    Then write
      Ws.
    and immediately after the dot you get a dropdown with all possible methods/functions/properties. Type an R and you'll see that it jumps to Range, then hit TAB and Range is written into the VBA editor.

    (If you can't see the Intellisense, click Tools\Options and in the Editor tab check all, except the 2 box at the top.)

    b) Object Browser

    In the VBA editor press F2, in the text box on the left of the binoculars type Borders and click the binoculars. As you see the Borders class has an Item property, when you select it, you can see the declaration below:

    Property Item(Index As XlBordersIndex) As Border

    As you see XlBordersIndex and Border is green, means you can click it and get directly to the Enum resp. Class.

    Inside the Border class you can see the LineStyle property, which has the Variant datatype... not really helpful.

    But when you select it and press F1, you'll get the Excel VBA help for this property directly. In the help you can read that you can write a xlLineStyle constant into this property and a short click in the help shows all possible values.

    Andreas.

    Monday, August 17, 2015 7:23 PM