|
<< Click to Display Table of Contents >> Sheets (collection) |
Access paths:
▪Application à Workbooks à Item à Sheets
▪Application à ActiveWorkbook à Sheets
1 Description
The Sheets collection contains all worksheets of a document. The individual elements of this collection are of the type Sheet.
2 Access to the collection
Each open document has exactly one instance of the Sheets collection. It is accessed through Workbook.Sheets:
' Display the number of worksheets in the active document
MsgBox pm.ActiveWorkbook.Sheets.Count
3 Properties, objects, collections and methods
Properties:
▪Count R/O
Objects:
▪Item → Sheet
▪Application → Application
▪Parent → Workbook
Methods:
▪Add
Count (property, R/O)
Data type: Long
Returns the number of Sheet objects in the document – in other words: the number of the worksheets in the document.
Item (pointer to object)
Data type: Object
Returns an individual Sheet object, i.e. one individual worksheet.
Which Sheet object you get depends on the value that you pass to Item. You can specify either the numeric index or the name of the worksheet:
' Show the name of the first worksheet
MsgBox pm.Application.ActiveWorkbook.Sheets.Item(1).Name
' Show the name of the worksheet with the name "Income"
MsgBox pm.Application.ActiveWorkbook.Sheets.Item("Income").Name
Application (pointer to object)
Returns the Application object.
Parent (pointer to object)
Returns the parent object, i.e. an object of the type Workbook.
Add (method)
Adds a new empty worksheet to the document and returns the Sheet object that represents this new worksheet.
Syntax:
Add [Name]
Parameters:
| Name (optional; type: String): The name for the new worksheet. If you omit this parameter, the name is automatically generated ("Table1", "Table2", "Table3", etc.). |
Return type:
| Object |
Example:
Sub Main()
Dim pm as Object
Dim newDoc as Object
Dim newSheet as Object
Set pm = CreateObject("PlanMaker.Application")
pm.Visible = True
' Add a new document
Set newDoc = pm.Workbooks.Add
' Add a worksheet to the document
Set newSheet = newDoc.Sheets.Add("MySheet")
' Display the name of the new worksheet
MsgBox newSheet.Name
End Sub
You can use the Sheet object returned by the Add method like any other worksheet. You can also ignore the return value of Add and get the new worksheet via ActiveSheet.