Understanding Worksheets

Worksheets come in collections just as workbooks do. By manipulating worksheets in the Immediate window, you will see some similarities-and some differences-between different collection objects.

Add a New Worksheet

You add a new worksheet to the active workbook the same way you add a new workbook to Excel: by using the Add method.

1. In Excel, close all but one workbook.

   The one workbook should have three sheets, named Sheet1, Sheet2, and Sheet3, with Sheet1 as the active sheet.

2. Switch to the Visual Basic editor. In the Immediate window, type Worksheets.Add and press Enter.

   A new worksheet named Sheet4 appears before the original active sheet.

   

Rename and Delete a Worksheet

A worksheet is an object with properties and methods of its own. You can use those methods and properties to manipulate a worksheet. As you work with worksheets, you will notice that the Auto List of methods and properties will not always appear after you type a period. Later in this chapter, you will learn how to get an Auto List to always appear with a worksheet object.

1. Type ?Worksheets(1).Name and press Enter to display the name of the first work-sheet (probably Sheet4).

   In the same way that you use the Item property (in either its long form or its short form) on a Workbooks object to establish a reference to a single Workbook object, you use the Item property on a Worksheets object to establish a reference to a single Worksheet object. Once you have a reference to a Worksheet object, you can use Worksheet object properties, such as Name.

   The name of a workbook is a read-only property; you have to save a file to change its name. The name of a worksheet, however, is a read-write property; you can change the name directly.

2. Type Worksheets(1).Name = "Input Values" and press Enter.

   The name of the worksheet changes.

   

3. Type Worksheets("Sheet1").Activate and press Enter.

   The worksheet named Sheet1 becomes the active sheet.

4. Type ActiveSheet.Delete and press Enter. Click Delete when warned that the sheet might contain values.

5. Type Worksheets("Input Values").Activate and press Enter.

As with workbooks, you can refer to a single worksheet by number, by name, or by pointing. With the first worksheet named Input Values and activated, the expressions Worksheets("Input Values"), Worksheets(1), and ActiveSheet all establish a reference to the same Worksheet object.

Look at the Return Value of the Delete Method

Normally, with a method, you just execute it, but with a property you either retrieve its current value, or you change its value to something new. Methods do, however, return values, and you might want to see the value that a method returns. For example, when you use the Delete method on a worksheet, the action of the method is to close the worksheet, but the method also returns a value to the macro.

1. Type ?ActiveSheet.Delete and press Enter. Click Delete when warned that the sheet contains data.

   The active sheet disappears, and the word True appears after the statement. When the Delete method succeeds in carrying out its action, it returns the value True. Making the worksheet go away is the action of the method. The word True that appears in the Immediate window is the return value of the method. A method can have both an action and a return value.

2. Type ?ActiveSheet.Delete and press Enter. This time, click Cancel when warned.

   The word False appears after the statement. The Delete method did not complete the action of deleting the worksheet, so it returns the value False.

Many methods follow this pattern of returning True if the action is successful and False if it is not. As you can imagine, this can be useful to know when you write macros.

Look at the Result of the Add Method

The Delete method of the Worksheet object returns either True or False, dependingon whether it accomplishes its intended action. The Add method also has an intended action-to create a new item in the collection. But the Add method does not return True or False. Rather, the Add method returns a reference to the newly created object. You can then use that reference the same way you can use the reference created by the Item property or by the ActiveSheet property.

1. In the Immediate window, type Worksheets.Add.Name = "New Sheet" and press Enter.

   A new worksheet appears in the workbook, with the name New Sheet.

   

2. Type Worksheets.Add.Delete and press Enter. When the confirmation dialog box appears, you can see the new worksheet in Excel. Click Delete, and watch the work-sheet disappear.

This example is bizarre because it deletes the worksheet as part of the same statement that creates it. It does, however, illustrate how the Add method returns a reference to the newly created object.

The Add method has an action-it creates a new worksheet. In addition, as its return value, it returns a reference to the new object. If you don’t use the reference immediately-as part of the same statement-the reference is discarded. If you then want to communicate with the new worksheet, you must establish a new reference using the ActiveSheet property or the Item property. Usually, you don’t bother using the reference returned by Worksheets.Add because using ActiveSheet to establish a new reference is as easy as shouting “Jared” to get a slumbering student’s attention.

Copy a Worksheet

Another useful method for a worksheet allows you to make a copy of the worksheet. You can copy the worksheet either to a new workbook or to the same workbook.

1. In the Immediate window, type Activesheet.Copy and press Enter.

   Copying a worksheet without specifying a destination creates a new workbook that includes the copy.

   

2. Type ActiveWorkbook.Close SaveChanges:=False and press Enter to delete the new workbook.

3. Type Activesheet.Copy Before:=Activesheet and press Enter.

This version of the Copy method tells Excel where to put the new copy: it puts it just before the specified worksheet, which in this case is the original worksheet. If you use the Before argument, you must give it a reference to a worksheet. If you want to copy a worksheet to the end of a workbook, the Copy method has an After argument that you can use instead of the Before argument.

The Copy method is a convenient way to clone a worksheet. The Copy method does not return a reference to the new worksheet (it returns the True or False that indicates successful completion); if you need to refer to the newly created copy, you must use ActiveSheet or some other method or property.

Manipulate Multiple Worksheets

When you work with collections, you typically work either with the entire collection (as when you closed all the workbooks at the same time by using the statement Workbooks.Close), or you work with a single item from the collection (as when you closed a single workbook by using the statement Workbooks(1).Close). With work-sheets, however, you sometimes need to create a subcollection-a new collection that includes some, but not all, the worksheets in the collection.

1. Type Worksheets(3).Select and press Enter to select the third worksheet in the workbook.

   The Select method allows you to select a new worksheet within the workbook. When you select a single worksheet, it makes it the active worksheet.

   

   Excel can have more than one worksheet selected at the same time. This is equivalent to holding down the Ctrl key as you click worksheet tabs.

2. Type Worksheets(Array(1,3,4)).Select and press Enter to select the first, third, and fourth worksheets.

   Array is a function that lets you treat multiple values as one. With the Array function, you can select more than one worksheet at the same time. Even though three work-sheets are selected, only one is the active worksheet.

   

3. Type Worksheets(3).Activate and press Enter to activate the third worksheet in the workbook, the Sheet1 worksheet, while leaving all three worksheets selected.

   

   When you select more than one worksheet, one of the worksheets is on top as the active worksheet. All three worksheets are selected, but only one is active. You use the Activate method to specify which worksheet should be the active worksheet. If you activate a worksheet that is not within the currently selected set of worksheets- for example, if only one worksheet is selected-the Select method and the Activate method act the same: they select and activate a single worksheet.

4. Type Worksheets(2).Activate and press Enter to select only the second worksheet.

   When you activate a worksheet that is not selected, the selection changes to include only the newly activated worksheet. You can execute methods or assign values to properties for the entire subcollection of worksheets at once.

   

5. Type ?Worksheets(Array(1,3,4)).Count and press Enter.

   The number 3 appears. When you select items from a collection with the Array function, the selected items form a new collection.

Not all collections allow you to use the Array function to create sub-collections. For example, you cannot use Array with the Workbooks collection. Generally the object model reflects the user interface. In the user interface, you can select multiple worksheets at the same time, so the object model lets you use the Array function with a Worksheets object. But in the user interface, you cannot select multiple workbooks at the same time, so the object model does not let you use the Array function with a Workbooks object.

Declare Variables to Enable Auto Lists

In a Visual Basic statement, when you type ActiveWorkbook and follow it with a period, an Auto List of methods and properties appropriate for a workbook appear. But when you type ActiveSheet and follow it with a period, no Auto List appears. This is because a workbook can contain different types of sheets: The active sheet could be a chart sheet as well as a worksheet. In the context of a macro, you can get the Auto List to appear by putting the object reference into a specially designated variable.

1. In the Visual Basic editor, close the Immediate window, click Insert, and click Module.

2. In the module, type Sub Test and press Enter to create a new macro.

3. In the body of the macro, type mySheet and then type a period ( . _).

   No Auto List appears. The word mySheet acts as a variable. When you create a new word and use it as a variable, Visual Basic makes it Variant. Variant means that you can assign anything you want to the variable, and it will change from Integer to String to Workbook to Range as fast as you can assign different values or objects to it. Visual Basic can’t display the Auto List because it really doesn’t know what type of value or object will be assigned to the variable at any given moment. You can, however, promise Visual Basic that you’ll never, ever assign anything other than a Worksheet object to the mySheet variable.

4. Delete the period you just typed. At the top of the macro, just below the Sub Test statement, enter this statement: Dim mySheet As Worksheet.

   This statement declares the variable to Visual Basic-that is, you declare to Visual Basic that mySheet is a variable and that the only thing you’ll ever assign to it is a reference to a Worksheet object. (Dim is an archaic term. It’s short for Dimension and has to do with telling the computer how much space you’ll need for the variable.)

5. Position the cursor at the end of the statement beginning with mySheet, and type a period ( . ).

   Sure enough, the Auto List appears.

6. Type N to select the Name property.

   

7. Press Space, and then type = "Test Sheet".

   The final statement should be mySheet.Name = "Test Sheet".

8. Press F5 to run the macro.

   You get an error message saying, “Object variable or With block variable not set.” Even though you promised that mySheet would contain only a worksheet, you still haven’t assigned a worksheet reference to it.

9. Click End to close the error message, and then after the Dim statement, add the statement Set mySheet = ActiveSheet.

   The Set statement assigns a reference to the ActiveSheet object to the mySheet variable.

10. Press F8 repeatedly to run the macro.

    The macro first assigns the active sheet object to the variable and then changes the name of that sheet.

You can create a variable “on the fly” simply by assigning a value or an object to it, but if you use Dim to declare how you intend to use the variable, Visual Basic can display Auto Lists that make code much easier to write and less likely to contain errors.

CLOSE Excel.