Microsoft Excel VBA Worksheets

	Worksheets

Worksheets Fundamentals

Introduction

So far, we were referring to the work area as a spreadsheet. Indeed, it is a spreadsheet. In Microsoft Excel, a spreadsheet is called a worksheet. In the previous lesson, we introduced workbooks. Indeed, a workbook is a series of worksheets that are treated as a group.

In the previous lesson, we saw that, by default, when Microsoft Excel starts, it creates a workbook. That workbook is equipped with three worksheets. If you do not need all of them, you can delete those that appear useless. You can also add new worksheets as you see fit.

If you want Microsoft Excel to always start with less or more worksheets, you can change its default settings in the Excel Options dialog box accessible from the Office Button

Excel Options

Practical Learning: Introducing Worksheets

Start Microsoft Excel or a new document
To save the document, press Ctrl + S
Save it as ROSH1
On the Ribbon, click Developer
In the Code section, click the Visual Basic button
To create a form, on the main menu of Microsoft Visual Basic, click Insert -> UserForm
Right-click the form and click Properties
Change its Caption to Red Oak High School - Management

Identifying a Worksheet

In Lesson 1, we saw that each available worksheet is represented by a tab in the lower left area of Microsoft Excel:

Worksheets

A worksheet is an object of type Worksheet. The various worksheets you will use are stored in a collection called Worksheets. Another name for the collection that contains the worksheets is called Sheets. In most cases, you can use either of these two collections. Each worksheet is an object of type Worksheet.

Referring to a Worksheet

In the previous lesson, we saw that, if you have only one workbook opened, to refer to it, you can pass an index of 1 to the Item property of the Workbooks collection to access its Workbook object. Here is an example:

Sub Exercise()
    Workbooks.Item(1)
End Sub

You can omit the Item name if you want and you would get the same result:

Sub Exercise()
    Workbooks(1)
End Sub

Because the worksheets of a document are part of the workbook that is opened, to support them, the Workbook class is equipped with a property named Worksheets or Sheets. Therefore, after identifying the workbook, use the period operator to access the Worksheets or the Sheets property. Here is an example:

Sub Exercise()
    Workbooks.Item(1).Sheets
End Sub

As mentioned already, the worksheets are stored in the Worksheets collection, which is actually a class. Each worksheet can be located based on an indexed property named Item. The Item property is a natural number that starts at 1. The most left worksheet has an index of 1. The second worksheet from left has an index of 2, and so on. To access a worksheet, type one of the Worksheets or Sheets collections, followed by the period operator, followed by Item() and, between the parentheses, type the index of the worksheet you want. For example, the following code will access the second worksheet from left:

Private Sub Exercise()
    Workbooks.Item(1).Sheets.Item(2)
End Sub

Just as we saw that you can omit the Item word on the Workbooks object, you can also omit it on the Worksheets or the Sheets object. This can be done as follows:

Sub Exercise()
    Workbooks.Item(1).Worksheets(2)
End Sub

Or as follows:

Sub Exercise()
    Workbooks(1).Worksheets(2)
End Sub

Each tab of a worksheet has a label known as its name. By default, the most left tab is labeled Sheet1. The second tab from left is labeled Sheet2. To programmatically refer to a worksheet using its label, call the Worksheets or the Sheets collection and pass the label of the tab you want, as a string. Here is an example that refers to the worksheet labeled Sheet3:

Sub Exercise()
    Workbooks.Item(1).Sheets.Item("Sheet3")
End Sub

On all the code we have written so far, we were getting a worksheet from the currently opened workbook. As mentioned already, by default, when Microsoft Excel starts, it creates a default workbook and programmatically creates a Workbooks.Item(1) reference. This means that you do not have to indicate that you are referring to the current workbook: it is already available. Consequently, in your code, you can omit Workbooks.Item(1) or Workbooks(1). Here is an example:

Sub Exercise()
    Sheets.Item("Sheet3")
End Sub

Getting a Reference to a Worksheet

In the above code segments, we assumed that you onlywant to perform an action on a worksheet and move on. Sometimes you may want to get a reference to a worksheet. To do this, declare a variable of type Worksheet. To initialize it, access the desired worksheet from the workbook using the Item property and assign it to the variable using the Set operator. Here is an example that gets a reference to the second worksheet of the currently opened workbook and stores that reference to a variable:

Sub Exercise()
    Dim Second As Worksheet
    
    Set Second = Workbooks.Item(1).Sheets.Item(2)
End Sub

Selecting a Worksheet

In some circumstances, you will need to perform a general action on a worksheet. Before doing this, you may need to select the contents of the whole worksheet first.

Since there are usually many worksheets presented to you, each is represented by a tab on the lower left corner. Therefore, to select a worksheet:

You can click its tab
You can press and hold Ctrl. Then press either Page Up or Page Down. Once the desired worksheet has been selected, you can release Ctrl

If you have many worksheets, to select a range of worksheets, click a tab that is considered one end of the range. Press and hold Shift, then click the tab at the end of the range and release Shift.

To select worksheets at random, click one of the desired worksheets. Press and hold Ctrl. Then click each desired worksheet. When the selection has been made, release Ctrl.

To programmatically select a worksheet, access the Sheets collection, pass the name of the desired worksheet as string, and call Select. Here is an example that selects a worksheet labeled Sheet1:

Private Sub Exercise()
    Sheets("Sheet1").Select
End Sub

The worksheet that is selected and that you are currently working on is called the active worksheet. It is programmatically identified as the ActiveSheet object (it is actually a property of the current document).

Worksheets Names

The starting worksheets are named Sheet1, Sheet2, and Sheet3. You can change any or all of these worksheet names.

To rename a worksheet:

You can double-click its sheet tab, then type a new name
You can right-click a sheet’s tab, click Rename, and type the new name
While a certain worksheet is selected, on the Ribbon, click Home. In the Cells section, click Format. In the Organize Sheets section, click Rename Sheet:

Then type the new name, and press Enter.

To programmatically rename a worksheet, pass its index or its default name as a string to the Sheets (or the Worksheets) collection, then access the Name property of the collection and assign the desired name. Here is an example:

Private Sub Exercise()
    Sheets("Sheet1").Name = "Employees Records"
End Sub

This code will change the name of the Sheet1 worksheet to Employees Records.

As we saw earlier, you can refer to, or select, a worksheet, using its name. If you had renamed a worksheet, you can use that name to select it. Here is an example that selects a worksheet named Tuition Reimbursement:

Private Sub Exercise()
    Sheets("Tuition Reimbursement").Select
End Sub

Practical Learning: Naming Worksheets

Design the form as follows:

Control	Name	Caption
Label		Rename:
TextBox	txtSheetOldName
Label		As
TextBox	txtNewName
CommandButton	cmdRename	Rename

Double-click the button and implement its Click event as follows:

Private Sub cmdRename_Click()
    Worksheets(txtSheetOldName.Text).Name = txtSheetNewName.Text
    txtSheetOldName.Text = ""
    txtSheetNewName.Text = ""
End Sub

To use the form, on the main menu of Visual Basic, click Run -> Run Sub/UserForm
In the Rename text box, type Sheet1
In the As text box, type Student Registration
Click Rename and notice that the To rename the first worksheet, double-click the Sheet1 tab to put it in edit mode
In the Rename text box, type Sheet2
In the As text box, type Emergency Information and click Rename
Close the form and return to Microsoft Visual Basic

The Views of a Worksheet

Introduction

The regular view of a worksheet is referred to as normal. It shows one large and long display of columns and cells. Microsoft Access allows you to choose among many other views.

Changing the View of a Worksheet

Instead of the regular or normal view, to change how a worksheet displays, on the Ribbon, click View and click one of the buttons in the Workbook View section:

Normal: This is the regular view of a worksheet
Page Layout: This would show various groups on the worksheet. Here is an example:

Page Break Preview: This view shows how the pages would be printed by dividing the worksheet in page breaks:

Full Screen: The worksheet would use the whole screen

To get the regular view from Page Layout or Page Break Preview, you can click the Normal button. To get the normal view from Full Screen, you can press Esc.

Operations on Worksheets

The Sequence of Worksheets

By default, worksheets are positioned in a numbered format that makes it easy to count them. More often you will find that, after creating a few of them, you are not satisfied with their positions. You can change their positions if you want.

To move a worksheet, click and hold the mouse on its tab, then move the mouse in the direction of your choice. While you are moving the worksheet, the mouse pointer will turn into a white piece of paper and a small down-pointing triangle will guide you. Once the small triangle is positioned in the desired location, release the mouse.

To programmatically move a worksheet, use the Move() method of the Worksheets or the Sheets collection. The syntax of this method is:

Worksheets(Index).Move(Before, After)

Both arguments are optional. If you don't specify any argument, Microsoft Visual Basic would create a new workbook with one worksheet using the index passed to the collection with a copy of that worksheet. Suppose you are (already) working on a workbook that contains a few worksheets named Sheet1, Sheet2, and Sheet3. If you call this method on a collection with the index set to one of these worksheets, Microsoft Excel would make a copy of that worksheet, create a new workbook with one worksheet that contains a copy of that worksheet. For example, the following code with create a new workbook that contains a copy of the Sheet2 of the current workbook:

Private Sub CommandButton1_Click()
    Sheets.Item("Sheet2").Move
End Sub

In this case, the name of the worksheet you are passing as argument must exist. Otherwise you would receive an error. Instead of using the name of the worksheet, you can pass the numeric index of the worksheet that you want to copy. For example, the following code will create a new workbook that contains one worksheet named Sheet3:

Private Sub CommandButton1_Click()
    Sheets.Item(3).Move
End Sub

If calling the Item property, make sure the index is valid, otherwise you would receive an error.

To actually move a worksheet, you must specify whether it would be positioned to the left or the right of an existing worksheet. To position a worksheet to the left of a worksheet, assign it the Before factor. To position a worksheet to the left of a worksheet, assign it the After argument. Consider the following code:

Private Sub cmdMove_Click()
    Worksheets("Sheet3").Move After:=Worksheets("Sheet1")
End Sub

This code will move the worksheet named Sheet3 to the right of a worksheet named Sheet1.

Adding New Worksheets

As mentioned already, when Microsoft Excel starts, by default, creates three worksheets. We also mentioned that you can change this default number in the Excel Options dialog box. You can add a new worksheet anytime if you judge it necessary.

Some documents are quite complete with just one worksheet, but others need as many worksheets as possible. The number of worksheets you use in a particular document is conditioned by your needs and the memory of your computer.

To add a new worksheet to a workbook:

Click the small tab on the right side of the existing tabs
Click the worksheet tab that will succeed the new worksheet and press Shift + F11
On the Ribbon, click Home. In the Cells section, click the arrow under the Insert button and click Insert Sheet
Right-click a sheet tab and click Insert... This would display the Insert dialog box:

From the Insert dialog box, you can choose to insert a blank worksheet or insert one of the existing templates as a worksheet:

To programmatically create a new worksheet, you can specify whether you want it to precede or succeed an existing worksheet. To support creating a new worksheet, call the Add() method of the Worksheets or the Sheets collection. Its syntax is:

Workbook.Sheets.Add(Before, After, Count, Type)

All of these arguments are optional. This means that you can call this method as follows:

Private Sub cmdNewWorksheet_Click()
    Sheets.Add
End Sub

If you call the method like that, a new worksheet would be created and added to the left side of the active worksheet.

If you want to create a new worksheet on the left side of any worksheet you want, you can first select that worksheet and call the Add() method. For example, suppose you have three worksheets named Sheet1, Sheet2, and Sheet3 from left to right and you want to insert a new worksheet between Sheet2 and Sheet3, you can use code as follows:

Private Sub cmdNewWorksheet_Click()
    Sheets("Sheet2").Select
    Sheets.Add
End Sub

To be more precise, you can specify whether the new worksheet will be positioned to the left or to the right of another worksheet used as reference.

Practical Learning: Creating Worksheets

Change the design of the form as follows:

Control	Name	Caption
Label		Create a new worksheet named:
TextBox	txtNewWorksheet
CommandButton	cmdNewWorksheet

Double-click the Create button and implement its Click event as follows:

Private Sub cmdNewWorksheet_Click()
    Worksheets.Add Before:=Worksheets("Sheet3")
    Worksheets(Worksheets.Count - 1).Name = txtNewWorksheet.Text
    txtNewWorksheet.Text = ""
End Sub

To test the code, on the Standard toolbar of Microsoft Visual Basic, click the Run Sub/UserForma button
Click the Create A New Worksheet Named text box and type 6th Grade
Click Create
In the same way, create new worksheets named 5th Grade, 4th Grade, 3rd Grade, 2nd Grade, and 1st Grade
Close the form

Removing Worksheets

As your work progresses, you will decide how many worksheets you need for your particular workbook. Just as we learned to add worksheets, you can delete or remove the worksheets you do not need anymore. Since a worksheet is not a file, when you delete a worksheet, it is permanently gone. If one or more cells of the worksheet contain data, you will receive a confirmation message to decide.

To delete a worksheet:

You can right-click its tab and click Delete
In the Cells section of the Home tab of the Ribbon, click the arrow of the Delete button and click Delete Sheet

To programmatically remove a worksheet, call the Delete() method of its collection. When calling this method, pass the name of the worksheet you want to remove to the collection.

Practical Learning: Deleting Worksheets

Change the design of the form as follows:

Control	Name	Caption
Label		Delete the worksheet named:
TextBox	txtRemoveSheet
CommandButton	cmdDelete	Delete

Double-click the Create button and implement its Click event as follows:

Private Sub cmdRemoveSheet_Click()
    Worksheets("Sheet3").Delete
    txtRemoveSheet.Text = ""
End Sub

To test the code, on the Standard toolbar of Microsoft Visual Basic, click the Run Sub/UserForm button
Click the Delete The Worksheet Named text box, type Sheet3
Click Delete
After reading the warning, click Delete
In the same way, delete the worksheet named Sheet2
Close the form
Save the document

Hiding or Revealing a Worksheet

As mentioned already, by default, Microsoft Excel makes three worksheets available to you. We also reviewed how you can add new ones or delete some of them. Instead of deleting a worksheet, you can hide it for any reason you judge necessary. If the document contains more than one worksheet, you can hide one or more worksheets or even the entire workbook.

To visually hide a worksheet in a workbook, click any cell in that worksheet to make it active:

Right-click the sheet tab and click Hide
In the Cells section of the Home tab of the Ribbon, click Format, position the mouse on Hide & Unhide, and click Hide Sheet

The worksheet’s tab will disappear from the screen although it is not deleted. To hide a group of worksheets, select them and proceed in the same way.

To visually unhide the hidden worksheets:

Right-click any tab and click Unhide...
In the Cells section of the Ribbon, click Format, position the mouse on Hide & Unhide, and click Unhide Sheet...

This would open the Unhide dialog box with a list of the the names of the hidden worksheets:

If you have more than one hidden sheet, select the desired worksheet and click OK.

Worksheets and the Web

You can publish the content of a worksheet to a web page. In Microsoft Excel, you can publish just one worksheet or the whole document. To perform publishing, first decide on what you want to publish, a worksheet or the whole document.

To save a file for the web, click the Office Button, position the mouse on Save As and click Other Formats. In the Save As Type combo box, select Web Page (.html). Specify whether to save the whole document or only some worksheets. Click Save. Read the message box and click Yes.

In the previous lesson, we saw how to save a workbook as a Microsoft Excel document. If you want to save it in HTML format, pass the first and the second argument of the Workbook.SaveAs() method::

Workbook.SaveAs(FileName, FileFormat)

In this case, pass the second argument as xlHTML. Here is an example:

Sub Exercise()
    Workbooks(1).SaveAs "Affiche10.htm", xlHtml
End Sub


Home	Copyright © 2007-2010, FunctionX