Using Properties and Methods That Are Unique to Collections

Collections have a Count property, an Add method, and an Item method. This section describes how to use these language elements.

Using the Count Property

The Count property returns the number of elements in a collection. For example, the following code uses the Count property to display the number of worksheets in the active workbook.

Sub NumWorksheets()
    MsgBox "Number of worksheets in this workbook: " & _
        ActiveWorkbook.Worksheets.Count
End Sub

The Count property is useful when you want to loop on the elements in a collection, although in most cases a For Each...Next loop is recommended instead. The following example shows how you can use the Count property to loop on the worksheets in the active workbook, hiding every other worksheet.

Sub HideEveryOther()
    For i = 1 To Worksheets.Count
        If i Mod 2 = 0 Then
            Worksheets(i).Visible = False
        End If
    Next i
End Sub

Using the Add Method

The Add method creates a new element in a collection. This results in a new element in Microsoft Excel also. For example, when you use the Add method on the Worksheets collection, Visual Basic adds a new Worksheet object to the collection, and Microsoft Excel adds a new worksheet to the workbook.

The Add method takes different arguments, depending on the collection it's applied to. For example, the Add method for the Workbooks collection takes one argument (template), but the Add method for the Worksheets collection takes four arguments (before, after, count, and type). You can find the arguments for the Add method in the Object Browser or in Help.

Using the Add Method's Return Value

The Add method returns a reference to the new object it creates. If you need to refer to the new object in your code, you should set an object variable to the return value and then use the object variable to refer to the new object.

For example, the first procedure in the following code creates a new worksheet in the active workbook and then hides the worksheet by setting its Visible property to the constant xlVeryHidden. The public object variable newSheet is set to the return value of the Add method. The variable newSheet can be used by any procedure in any module of the active workbook. Hidden worksheets are an excellent place for a macro to temporarily store numbers and other data, as shown in the other two procedures in the following example.

Public newSheet As Object

Sub CreateScratchWorksheet()
    Set newSheet = Worksheets.Add
    newSheet.Visible = xlVeryHidden
End Sub

Sub FillRanges()
    newSheet.Range("F9").Value = "some text"
    newSheet.Range("A1:D4").Formula = "=RAND()"
End Sub

Sub ShowValue()
    MsgBox newSheet.Range("A1").Value
End Sub

Using the Item Method

Most of the code you see in this book and in Help uses a shorthand syntax for accessor methods. The following two lines of code, which perform the same action in Microsoft Excel, demonstrate the shorthand syntax.

Worksheets("Sheet1").Range("A1").Value = 3        ' shorthand

Worksheets.Item("Sheet1").Range("A1").Value = 3    ' same action

You may see the Item method used occasionally in other books about Visual Basic. For brevity in code, you can always omit the Item method and use the shorthand syntax. The shorthand syntax occurs because the Item method is the default method for a collection object.