Solved: Using Object Browser: Discover New Info

[YellowLabPro]

I was reading about the Parent Object, which led me to looking for an example in how to use the Parent Object. Looking for an example, I randomnly selected an item, ListColumns, and used its example- this gave me an out of subscript 9 error.


[VBA]
Sub PrintParentName()
Dim wrksht As Worksheet
Dim objListCol As ListColumn

Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
Set objListCol = wrksht.ListObjects(1).ListColumns(3)
Debug.Print objListCol.ListDataFormat.Parent
End Sub
[/VBA]

So I looked at the ListColumn independently and this errored....
438: object does not support this property or method.
These examples were taken directly from the help files-
I assume I am using them incorrectly....

[VBA]
Sub AddListObject()
Dim myNewColumn

Set myNewColumn = Worksheets(1).ListObject(1).ListColumns.Add
End Sub
[/VBA]

[rory]

I'm not in front of a version of Excel that supports that, but I would guess it's a typo and should be:
[vba]Sub AddListObject()
Dim myNewColumn

Set myNewColumn = Worksheets(1).ListObjects(1).ListColumns.Add
End Sub [/vba]

[YellowLabPro]

I don't know about that Rory,
I changed it in the code per your post, this gave a subscript 9 error.
I went back to the help file and I did copy it correctly- so I thought maybe it was a typo on MS's part, but in the help file, the following I think disproves that notion.
Now I am really curious what this object is all about....

Use the ListColumns property of the ListObject object to return the ListColumns collection. The following example adds a new column to the default ListObject object in the first worksheet of the workbook. Because no position is specified, a new rightmost column is added.

[rory]

Well, I would say it has to be:
[VBA]ListObjects(1).[/VBA]
rather than:
[VBA]ListObject(1).[/VBA]
because you are retrieving an item from the ListObjects collection.

[rory]

If you are getting a subscript 9 error I would guess there isn't a ListObject on that worksheet?

[YellowLabPro]

But are we not returning the ListColumns item of the ListObject collection?

This is an interesting point regarding the object in itself, but I am still stuck not knowing what the ListObject or ListColumn is, e.g. I only wrote the sub to see what it does....

[mdmackillop]

First create your list
Create a list

1. Highlight the range of data that you want make into a list. Note You can also select the range of cells to be specified as a list by selecting the range of cells from the Create List dialog box.
2. On the Data menu, point to List, and then click Create List.
3. If the selected data has headers, select the My list has headers check box and click OK.

[YellowLabPro]

Rory,
I agree, but.....
Would not that be the function of the line of code
[vba]Set myNewColumn = Worksheets(1).ListObjects(1).ListColumns.Add[/vba]
that this would add a list column, eliminating the subscript 9 error.

But if MS's help is not in error and I am getting the:
438: object does not support this property or method, then this does not actually support adding a list, or the help file is in error, or I most likely have errored....
The following is how MS wrote it, not an object of the collection, but the collection object.

[vba]
Sub AddListObject()
Dim myNewColumn

Set myNewColumn = Worksheets(1).ListObject(1).ListColumns.Add
End Sub
[/vba]

[rory]

No, you need a ListObject first, for the ListColumns to belong to. (see Malcolm's post)
ListObjects(1) is the equivalent of Listobjects.Item(1), returning a ListObject from the collection of all ListObjects on the worksheet (assuming you have any )
ListColumns is then a property of the ListObject object, not the Listobjects.Collection

An analogy:
You have a collection of Snowboards. Each individual snowboard has certain properties - manufacturer, length, weight, colour, design, price etc. You cannot, however, refer to the colour or length of the snowboards collection - the collection itself does not have such properties, only the individual items do.
HTH

[YellowLabPro]

Ok-
I set up this test scenario- I have setup a list in "Sheet7" it is two columns wide.
I am attempting to run either of these two procedures- and still getting errors.

Object Required 424:
[VBA]
Sub DisplayColumnName()
Dim olistobj
Dim wrksht As Worksheet
Dim objListObj As ListObject
Dim objListCols As ListColumns

Set wrksht = ActiveWorkbook.Worksheets("Sheet7")
Set objListObj = wrksht.ListObjects(1)
Set objListCols = olistobj.ListColumns

Debug.Print objListCols(2).Name
End Sub
[/VBA]

runtime error: 438: object not supported
[VBA]
Sub AddListObject()
Dim myNewColumn

Set myNewColumn = Worksheets(1).ListObject(1).ListColumns.Add
End Sub
[/VBA]

[rory]

First one: this line:
[VBA]Set objListCols = olistobj.ListColumns[/VBA]
should be:
[VBA]Set objListCols = objListObj.ListColumns [/VBA]

Note: use Option Explicit to catch this sort of thing.

Second one is the same error as before - you need Listobjects(1) not Listobject(1).

[YellowLabPro]

thanks Rory,
W/ your help- I was now able to determine what the listobject was-
I was able to change
[VBA]
Set myNewColumn = Worksheets("Sheet7").ListObject(1).ListColumns.Add

Set myNewColumn = Worksheets("Sheet7").ListObjects(1).ListColumns.Add
[/VBA]

For the record, the errors are in the Help File-
I do have the Option Explict set, olistobj is a dimmed variable. But I did this after the fact, after I copied it from the help file and it failed, thinking this was an oversight of MS...

Thanks for helping me sort it out.

Thanks Malcolm for eluding to the inital need of an existing list. My thoughts were this would create, the .add- but now see it the .add is the method to the column, not the list.