Excel VBA Macro Programming

This object represents the actual worksheet that you work on. In the hierarchy of the Excel object model, it sits below the Workbook object because all Worksheets are part of a Workbook .

Main Properties, Methods , and Collections

These are the properties, methods, and collections you will use within the Worksheet object.

Calculate

This method calculates one particular worksheet, assuming that the calculation method has been set to manual.

Worksheets(1).Calculate

This is useful if your workbook has many complex calculations and you want only a particular sheet to be calculated.

CheckSpelling

This method checks the spelling within a worksheet, just as if you selected Tools Spelling from the Excel menu.

Worksheets("Sheet1").CheckSpelling

You can add optional parameters for Custom Dictionary , Ignore Uppercase , Always Suggest , and so on.

Comments

This is a collection of all the comments added to the selected worksheet. Comments are the additional text that can be tagged onto a cell. They show up as a red triangle in the top right-hand corner of the cell and appear in full when you hover the cursor over that cell. You can insert, delete, or edit a comment by right-clicking the cell and following the menu options on the pop-up.

You can use this to find out how many comments there are in a worksheet:

MsgBox Worksheets("sheet2").Comments.Count

Delete

This method deletes a worksheet, just as if you selected Edit Delete Sheet from the Excel menu.

Worksheets("sheet1").Delete

PrintOut and PrintPreview

These methods allow you to print out a particular worksheet or to preview the printing of it.

Worksheets("sheet2").PrintOut Worksheets("sheet2").PrintPreview

Protect

This method enables you to protect a worksheet just as if you selected Tools Protection Protect Sheet from the Excel menu. You can supply an optional password as a parameter ‚ just make sure that a record is kept of it!

Worksheets("sheet2").Protect Worksheets("sheet2").Protect ("apple")

In the first sample, no password is given, so you will not be prompted for a password to unprotect . In the second example, the password is ‚“apple. ‚½

Range

This is a very important object within the worksheet and is described in the section ‚“Range Object, ‚½ later in this chapter.

SaveAs

This method saves the workbook under a different filename. Although it is part of the Worksheet object, it saves the entire workbook.

Worksheets("sheet2").SaveAs ("MyFile")

Select

This method selects a particular Worksheet within a Workbook; it is the same as clicking the tab buttons at the bottom of the window.

Worksheets("sheet2").Select

This selects Sheet2.

SetBackGroundPicture

This method places a picture, such as a BMP file, in the background of the spreadsheet:

Worksheets("sheet2").SetBackgroundPicture ("c:\MyPic.bmp")

With a little bit of imagination , you can display some hilarious sequences on your spreadsheet!

Unprotect

This method allows you to unprotect a sheet in code and works in partnership with the Protect method discussed earlier in this section.

Worksheets("Sheet2"),Unprotect ("apple")

You can provide a password, although this is optional. If a password is required but not provided, the password dialog will appear.

Protect and Unprotect can be useful if you have a protected sheet that you want to make some changes to using code. You unprotect the sheet giving the password, make your changes, and then protect the sheet again giving the same password.

Visible

Setting this property to True or False dictates whether a worksheet can be seen. This is the same as hiding a worksheet using Format Sheet Hide from the Excel menu, for example,

Worksheets("sheet2").Visible = False

This hides Sheet2. To make the sheet visible again, set the property to True.