Doing Things with Workbooks and Worksheets

Now that you've got your feet wet working with worksheet cells and ranges, it's time to move up one level and learn how you can control a single workbook, as well as an entire collection of workbooks. You cannot prepare a new spreadsheet if you don't know how to open a new workbook. You cannot remove a workbook from the screen if you don't know how to close a workbook. These important tasks are handled by two VBA methods: Add and Close. The next series of drills will give you the language skills necessary for dealing with workbooks and worksheets.

To render this into VBA: Enter this in the Immediate window:

Open a new workbook. Workbooks.Add

Find out the name of the first workbook. ?Workbooks(1).Name

Find out the number of open workbooks. ?Workbooks.Count

Activate the second open workbook. Workbooks(2).Activate

Activate the Chap02.xls workbook. Workbooks("Chap02.xls").Activate

To render this into VBA

Enter this in the Immediate window:

Save the active workbook as ActiveWorkbook.SaveAs

"NewChap.xls." Filename: = "NewChap.xls"

Close the first workbook. Workbooks(1).Close

Close the active workbook without ActiveWorkbook.Close saving recent changes to it. SaveChanges: = False

Close all open workbooks. Workbooks.Close

If you worked through the last example in the practice table above, all workbooks are now closed. Before you experiment with worksheets, make sure to open a new workbook.

When you deal with individual worksheets, you must know how to add a new worksheet to a workbook, select a worksheet or a group of worksheets, name a worksheet, and copy, move, and delete worksheets. In Visual Basic, each of these tasks requires a special method or property.

To render this into VBA

Enter this in the Immediate window:

Add a new worksheet.

Find out the name of the first worksheet.

Select a sheet named "Sheet3."

Activate a sheet named "Sheetl."

Move "Sheet2" before "Sheetl."

Rename worksheet "Sheet2" to "Expenses."

Find out the number of worksheets in the active workbook.

Remove the worksheet named "Expenses" from the active workbook.

Worksheets.Add

?Worksheets(1).Name

Worksheets(3).Select

Worksheets(Array(1,3,4)).Select

Worksheets("Sheet1").Activate

Worksheets("Sheet2").Move Before:=Worksheets("Sheet1")

Worksheets("Sheet2").Name = "Expenses"

?Worksheets.Count Worksheets("Expenses").Delete

Notice the difference between the Select and Activate methods:

■ The Select and Activate methods can be used interchangeably if only one worksheet is selected.

■ If you select a group of worksheets, the Activate method allows you to decide which one of the selected worksheets is active. As you know, only one worksheet can be active at a time.

Tip 2-7: Sheets Other Than Worksheets two additional sheets: DialogSheets

In addition to worksheets, the collection of workbooks contains chart sheets. To add a new chart sheet to your workbook, use the Add method: Charts.Add. To count the chart sheets, use: ?Charts.Count.

In Microsoft Excel prior to version 97, the Workbooks collection included and Modules. Dialogs have been replaced with the much friendlier UserForms. Beginning with Microsoft Excel Version 97, both dialogs and modules were created in the Visual Basic Editor window.

0 0

Post a comment