< Day Day Up > |
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 PropertiesA.2.1.1. Range ObjectsThere 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 ObjectsThe 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 PropertyThe 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 PropertyThe 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 PropertyThe 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 MethodsA.2.2.1. PrintOut MethodThe 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 MethodsThese 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 MethodThe 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. |
< Day Day Up > |