Windows collection vba

Linked window frames contain all windows that can be linked or docked. This includes all windows except code windows, designers , the Object Browser window, and the Search and Replace window.

If all the panes from one linked window frame are moved to another window, the linked window frame with no panes is destroyed. However, if all the panes are removed from the main window, it isn't destroyed. Use the Visible property to check or set the visibility of a window.

You can use the Add method to add a window to the collection of currently linked windows. A window that is a pane in one linked window frame can be added to another linked window frame.

Use the Remove method to remove a window from the collection of currently linked windows; this results in the window being unlinked or undocked. The LinkedWindows collection is used to dock and undock windows from the main window frame.

Represents the properties of an object. Use the Properties collection to access the properties displayed in the Properties window. For every property listed in the Properties window, there is a Property object in the Properties collection. Represents the set of references in the project. Use the References collection to add or remove references. The References collection is the same as the set of references selected in the References dialog box.

See also the VBProject object. Use the VBComponents collection to access, add, or remove components in a project. A component can be a form , module , or class. This browser is no longer supported. Download Microsoft Edge More info.

Contents Exit focus mode. Collection Class Reference Is this page helpful? Please rate your experience Yes No. Any additional feedback? Namespace: Microsoft. VisualBasic Assembly: Microsoft. Note Whenever possible, you should use the generic collections in the System. Caution Iterating through a Visual Basic Collection is not a thread-safe procedure. In this article. Add Object, String, Object, Object.

Adds an element to a Collection object. Contains String. Equals Object. Determines whether the specified object is equal to the current object. Inherited from Object. Serves as the default hash function. Remove Int Removes an element from a Collection object. Remove String. Returns a string that represents the current object. CopyTo Array, Int OnDeserialization Object. Add Object. Contains Object. IndexOf Object. Insert Int32, Object. In the fruit example, Apple is added to position 1 and Pear to position 2.

You can use the Before or After parameters to specify where you want to place the item in the collection. Note you cannot use both of these arguments at the same time. To Access the items of a collection you simply use the index. As we saw the index is the position of the item in the collection based on the order they were added. You can also use the Item Property to access an item in the collection. It is the default method of the collection so the following lines of code are equivalent:.

This is a very important point. When a basic data type is stored in a Collection it is read-only. A basic data type is a string, date, integer, long etc. If you try to update a Collection item you will get an error. This may seem like contradictory behaviour, but there is a good reason. Any item that is added to a Collection is read-only. However, when you add an object to a Collection, the object is not added as the item.

A variable with the memory address of the object is added as the item. The item variable is actually read-only but the object it points to is not. All you need to remember is that basic data types in a Collection are read-only. Objects in a Collection can be changed. You can read more about objects in memory here. This is seldom needed. To create a Chart sheet simple right click on any Chart, select Move and select the radio button for New sheet.

The following code displays the type and name of all the sheets in the current workbook. Note to access different type you need the For Each variable to be a variant or you will get an error:.

When you access different items the For Each variable must be a variant. If we declared sh as a worksheet in the above example it would give an error when we try to access a sheet of type Chart. It is rare that you would need a collection of different types but as you can see sometimes it can be useful.

I included the parameter names to make the above example clear. Just remember the key is the second parameter and must be a unique string. In the VBA Workbooks collection it is much better to access the workbook by the key name than by the index.

The order is dependent on the order they were opened and so is quite random:. An example of when to use keys is as follows: Imagine you have a collection of IDs for a 10, students along with their marks. You also have a number of worksheet reports that have lists of student IDs. For each of these worksheets you need to print the mark for each student.

You could do this by adding the 10, students to a collection using their student id as they key. The second issue is that it is not possible to update a value in a Collection. It stores the address of the object. If you need to update a basic value like a long, string etc. You have to remove the item and add a new one. If you wish to use keys there is an alternative to the Collection. You can use the Dictionary. The Dictionary provides more functionality to work with keys.

You can check if keys exist, update the values at keys, get a list of the keys and so on. To access all the items in a collection you can use a For loop or a For Each loop. With a normal For Loop, you use the index to access each item. The following example prints the name of all the open workbooks. You can see that we use the range of 1 to Workbooks. The first item is always in position one and the last item is always in the position specified by the Count property of the collection.

The For Each loop that is a specialised loop the is used for Collections. The For Each Loop. The For Each is considered faster than the For Loop. The For Each loop is neater to write especially if you are using nested loops. Compare the following loops. Both print the names of all the worksheets in open workbooks. The order of the For Each loop is always from the lowest index to the highest. If you want to get a different order then you need to use the For Loop. The order of the For Loop can be changed.

You can read the items in reverse. You can read a section of the items or you can read every second item:. The For loop gives more flexibility here but the reality is that most of the time the basic order is all you need. Using a Collection as a parameter or return value is very easy to do.

We will look at them in turn. It is simple to pass a collection to a function or sub. It is passed like any parameter as the following code example shows:. You can see how useful the sub PrintColl is in the example. It will print all the elements of ANY collection. The size or type of element does not matter. This shows how flexible collections are to use. One subtle point to keep in mind here is passing by value By Val and passing by reference ByRef differ slightly. For a simple variable passing by value means a copy is created.

In the following example, we pass total using both ByVal and ByRef. You can see that after we pass using ByRef the value has changed in the calling procedure:.

If you add or remove item then the collection in the original caller will also be changed. So the Subs in the following example will both remove the first item of the original collection:. The reason for this is that a Collection variable contains a pointer.

This means it contains the address of the collection rather than the actual collection.