A.2. Workbook and Worksheet Objects

The majority of the properties and methods that you use will be part of the workbook and worksheet objects. For these properties and methods, I group together related items.

A.2.1. Workbook and Worksheet Properties

A.2.1.1. Range Objects

There are several properties that return Range objects, and they are part of the Worksheet object. The ones that you use most often are Cells, Columns, Range, and Rows. You saw many of these used in the main text of the book. The code example that I show iterates through all of the cells in a range, one cell at a time, and prints the cell's formula in the Immediate Window.

 Dim xlapp As Excel.Application Set xlapp = Application   Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim xlcell As Excel.Range Set xlws = xlapp.ActiveWorkbook.ActiveSheet Set xlrng = xlws.Range("A1:A10") For Each xlcell In xlrng.Cells   Debug.Print xlcell.Formula Next xlcell 

A.2.1.2. Sheet Objects

The Sheet objects are part of the Workbook object. All of the following properties are Sheet object collections: Sheets, Worksheets, and Charts. Use a For Each...Next loop to cycle through the objects. The sheets collection is described in the section on Application objects earlier in this appendix.

A.2.1.3. QueryTables Property

The QueryTables property is a collection of QueryTable objects in the worksheet. If you remember from the main text, a QueryTable is a table of data on a worksheet that comes from an external data source. The following code cycles through all of the QueryTable objects on a worksheet and refreshes the data.

 Dim xlapp As Excel.Application Set xlapp = Application Dim qry As Excel.QueryTable Dim xlws As Excel.Worksheet Set xlws = xlapp.ActiveWorkbook.ActiveSheet For Each qry In xlws.QueryTables   qry.Refresh Next qry 

A.2.1.4. Name Property

The Name property refers to the name of the Worksheet and Workbook objects. You can read and write this property when dealing with a worksheet, but it is read-only when dealing with a workbook. When you change the name of the worksheet, it changes what is shown on the tab for the worksheet in the workbook. The Name property of the Workbook object returns the filename without the path.

A.2.1.5. Names Property

The Names property is the same as the Name property. The Names property is a collection of all the Name objects in a workbook or worksheet. You create names in Excel by going to InsertNameDefine. The following code prints each of the name objects in the active workbook to the Immediate Window. Use this code when you need to know the actual range that relates to a named range:

 Dim xlapp As Excel.Application Set xlapp = Application Dim xlwb As Excel.Workbook Dim xlnm As Excel.Name Set xlwb = xlapp.ActiveWorkbook For Each xlnm In xlwb.Names   Debug.Print xlnm.Name & " - " & xlnm.RefersTo Next xlnm 

There is also a property called RefersToR1C1 of the Name object that is used if you need to know the range address in R1C1-style notation.

A.2.2. Workbook and Worksheet Methods

A.2.2.1. PrintOut Method

The PrintOut method refers to the Worksheet and Workbook objects. If you call the method from a Workbook object, it prints the entire workbook. If you call the method from a Worksheet object, it prints only the worksheet.

A.2.2.2. Copy, Delete, Paste, and PasteSpecial Methods

These methods are part of the Worksheet object. The Copy and Delete methods are also available on many other objects in Excel.

A.2.2.3. Protect Method

The Protect method applies to both Workbook and Worksheet objects. Call this method to protect an object from changes. The nice part about the Protect method is that when you call it from code, you can protect the object for only the user interface. This enables you to make changes to the object through code without having to unprotect the object.