Posted by: yuvan004 | September 19, 2011

Creating VBA Macros to Manipulate Worksheets in Excel 2007


Office 2007

Summary: Learn how to write Visual Basic for Applications (VBA) macros in Microsoft Office Excel 2007 to automatically name and sort worksheets. In addition, learn how to create and name sets of worksheets based on data in the current workbook, and then save each new worksheet as a new workbook. (12 printed pages)

Mark Roberts, Microsoft Corporation

April 2009

Applies to: Microsoft Office Excel 2007

Contents

Macro?

This section is for Excel users with no knowledge of Excel VBA.

Excel VBA, which stands for Excel Visual Basic for Applications, is the name of the programming language of Microsoft Excel. With Excel VBA you can automate a task in Excel by writing a so called Macro. This can save you a lot of time! More importantly there are certain things you cannot do with Excel alone. Excel VBA allows you to do these things in Excel.

1 Create a Macro: To create a macro in Excel VBA, first activate Excel Visual Basic. Next, you can create a command button and assign a macro to the command button.

2 Excel Macro Recorder: The Excel Macro Recorder is a very useful tool included in Excel VBA. With the Excel Macro Recorder you can record a task you perform with Excel. Next, you can execute the task over and over with the click of a button which can save you a lot of time. This chapter explains the ins and outs of the Excel Macro Recorder.

Basics

This section explains the basics of Excel Visual Basic. It is good to know the basic terminology explained in this section before you start programming in Excel Visual Basic.

1 Macro Security: Setting up your macro security settings correctly is essential to protect yourself against potential viruses. Make sure your macro security settings are set up correctly so no harm can be done to your computer.

2 Visual Basic Editor: Learn how to launch the Visual Basic Editor and get the best configuration of the Project Explorer and the Code Window in your Excel Version. The Visual Basic Editor is the starting point for creating macros in Excel VBA, so it is important to get this configuration right.

3 Macro Comments: Add macro comments to your Excel VBA code and your code will be easier to read as program size increases.

4 MsgBox: The Message Box is a dialog box you can have appear to inform the users of your program.

5 Macro Errors: Dealing with VBA-errors can be quite a challenge. This chapter provides you with a simple tip to deal with these errors.

6 Debug Macros: Before you execute your VBA-code you can first debug your macro. This way most of the errors can be corrected before you execute your code.

7 Objects, Properties and Methods: In this chapter you will learn more about Excel VBA objects. An object has properties and methods. Excel Visual Basic is a semi-object oriented programming language. Learn more about the object hierarchy of Excel Visual Basic.

8 Workbook and Worksheet: In this chapter you will learn more about the Excel VBA Workbook and Excel VBA Worksheet object. You will see that the Worksheet and Workbook object have properties and methods as well, such as the count property which counts the number of active workbooks or worksheets. The Workbook and Worksheet object are commonly used in Excel VBA. They are very useful when your macro code has to be executed on different workbooks or worksheets.

9 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

Programming

This section is for users who want to get the most out of Excel VBA. Excel VBA Programming is not difficult, but you do need to know the keywords used in Excel VBA.

1 Variables: Excel VBA uses variables just like any other programming language. Learn how to declare and initialize an excel vba variable of type Integer, String, Double, Boolean and Date.

2 String Manipulation: There are many functions in Excel VBA we can use to manipulate strings. In this chapter you can find a review of the most important functions.

3 Calculate: Calculate with Excel VBA and add, subtract, multiply and divide values just like you are used to doing in Excel.

4 If Then Statement: In many situations you only want Excel VBA to execute certain code lines when a specific condition is met. The If Then statement allows you to do this. Instead of multiple If Then statements, you can use Select Case.

5 Cells: Instead of the more common Range object we could also use Cells. Using Cells is particularly useful when we want to loop through ranges.

6 Loop: Looping is one of the most powerful programming techniques. A loop (or For Next loop) in Excel VBA enables you to loop through a range of data with just a few lines of code.

7 Logical Operators: Do you want to execute code in Excel Visual Basic when more conditions are met? Or just one? Or none? Logical operators are what you need! Logical operators such as And, Or and Not are often used in Excel VBA.

8 Range: The Range object which is the representation of a cell (or cells) on your worksheet is the most important object of Excel VBA. It has many properties and methods and they are essential to manipulate the content of your Excel worksheet. In this chapter you will discover the most useful properties and methods of the Excel VBA Range object. They enable you to obtain control over your Excel worksheet.

9 Events: This chapter teaches you how to program workbook and worksheet events. Events are actions performed by users which trigger Excel VBA to execute a macro. For example, when you open a workbook or when you change something on an Excel worksheet, Excel VBA can automatically execute a macro.

10 Array: An Excel VBA array is a group of variables. You can refer to a specific variable (element) of an array by using the array name and the index number.

11 Date and Time: Dates and Times in Excel VBA can be manipulated in many ways. Easy examples are given in this chapter.

12 Function and Sub: The difference between a function and a sub in Excel VBA is that a function can return a value and a sub cannot. In this chapter we will look at an easy example of a function and a sub. Functions and subs become very useful as program size increases.

Controls

This section is about communicating with users using controls or a Userform. Learn how to use these controls in Excel 2010, Excel 2007 or Excel 2003. You can directly place controls on a sheet or place them on a Userform.

1 Textbox: A textbox is an empty field where the user can fill in a piece of text. Learn how to draw a textbox on your worksheet, how to refer to a textbox in your Excel VBA code, and how to clear a textbox.

2 Listbox: A listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.

3 Combobox: A combobox is the same as a listbox but now the user can also fill in his/her own choice if it is not included in the list. Learn how to draw a combobox on your worksheet and how to add items to a combobox.

4 Checkbox: A checkbox is a field which can be checked to store information. Learn how to draw a checkbox on your worksheet and how to refer to a checkbox in your Excel VBA code.

5 Option Buttons: Option buttons are the same as checkboxes except that option buttons are dependent on each other while checkboxes are not. This means that when you check one option button the other option button will automatically uncheck.

6 Userform: This chapter teaches you how to create an Excel VBA Userform (also known as a dialog box). You can download the Userform on this page as well.

Overview

This article describes two scenarios that use VBA code to automate processes that manipulate worksheets in Microsoft Office Excel 2007.

  • In the first scenario, the developer wants each user to add a worksheet to the workbook and type a name for the worksheet in a specific cell. The developer writes code that automatically names the current worksheet by using the specified value and then sorts all of the worksheets in the workbook by name.
  • In the second scenario, the developer wants the user to type a list of region names in a column on a worksheet, plus additional information about each region. The developer writes a macro that copies that information and creates a new workbook file for each region.

Important:

To access the commands that you need to create VBA code, the Developer tab must be visible in the Excel 2007 Ribbon user interface. If the Developer tab is not visible, use the following steps to display it:

  1. Click the Office Button, and then click Excel Options.
  2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Automatically Naming and Sorting Worksheets

In this scenario, the developer wants each user to add a worksheet to the workbook, and then to have Excel automatically name and sort all of the worksheets in the workbook. If you want Excel to perform these actions automatically after a user enters the name of the new worksheet in a cell, you must write your VBA code in an event-handler subroutine. An event-handler subroutine is code that runs when the user performs a particular action in Excel, such as opening or saving a workbook, or entering values into worksheets. In the Excel VBA object model, each user action that can have code associated with it is called an event.

Tip:

If you search the Excel Developer Reference for “event,” you can view all the topics that describe the events that have associated VBA event-handler code. Use the following steps to perform the search:

  1. Click the Microsoft Office Excel Help button.
  2. Click the drop-down arrow next to the Search button.
  3. Click Developer Reference, type “event” in the search box, and then click Search.

The following sections describe how to decide which event to use for the event-handler subroutine for this scenario, and how to write the VBA code to name and sort worksheets.

Choosing the Right Event

To create the code for this scenario, you must write the VBA code directly in the Visual Basic Editor rather than record a macro. To open the Visual Basic Editor, on the Developer tab, click Visual Basic. You can start adding code by double-clicking one of the default worksheet names, such as Sheet1, or by double-clicking the ThisWorkbook module in the Project Explorer pane of the Visual Basic Editor. Figure 1 shows the Visual Basic Editor window with the Project Explorer pane in the upper-left corner.

Figure 1. Visual Basic Editor window

Initially, you might think that you should add your VBA code to one of the worksheets, and write the event handler subroutine for the Change event of the Worksheet object. If you do that, your code will run whenever the data in the specified target cell is changed, but it will run only when the user adds the name to the cell in that particular worksheet. To create an event-handler subroutine that will run for every worksheet in the workbook (including new worksheets), you must add your code to the ThisWorkbook module and use the SheetChange event of the Workbook object instead. Because the SheetChange event is associated with the Workbook object, an event handler associated with that event can be used to work with any worksheet in the entire workbook. To create an event handler for the SheetChange event, use the following procedure.

To add an event handler to the ThisWorkbook module

  1. On the Developer tab, click Visual Basic to open the Visual Basic Editor.
  2. In the Project Explorer, double-click ThisWorkbook.
  3. In the Object drop-down list (the list above the editing window on the left side that says (General)), select Workbook.

This automatically creates an event handler for the Open event; you must create an event handler for the SheetChange event instead.

  1. In the Procedure drop-down list (the list above the editing window on the right side that now says Open), select SheetChange.

The Visual Basic Editor window should now look like Figure 2.

Figure 2. Visual Basic Editor window with empty Worksheet_SheetChange event handler

The following sections describe how to write the VBA code for the SheetChange event handler.

Writing Code to Name a Worksheet

In this scenario, you want the user to type a name in a particular cell, and then use that value to automatically name the current worksheet. The property you use to set the name of a worksheet is the Name property of the Worksheet object.

If you record a macro and name a worksheet, and then open the macro code in the Visual Basic Editor, you will see something like the following code.

Sub Macro1()

Sheets(“Sheet1”).Select

Sheets(“Sheet1”).Name = “MySheet”

End Sub

This code selects and names a particular sheet (“Sheet1”) in the workbook Sheets collection, but the goal of this scenario is to name the active worksheet. To access the properties and methods associated with current worksheet from VBA code, you use the ActiveSheet property of the Workbook object. Additionally, the code that the macro generates is hard-coded to use the name that you typed while recording it (“MySheet” in the previous example), and this scenario will retrieve the name from the cell where the user typed that value. To specify a particular cell in the code, use the Range object. To retrieve the value from that cell, use the Value property of the Range object. For example, to assign the value that the user entered in cell B3 as the name of the worksheet, use the following code.

 

ActiveSheet.Name = Range(“B3”).Value

Now, you need to determine how to get the event handler for the SheetChange event to run this line of code when a user types a value into cell B3. The SheetChange event provides two parameters: the Sh parameter that provides a Worksheet object that represents the current worksheet, and the Target parameter that provides a Range object that specifies which range on the current worksheet has changed. To complete this part of the code, use the Target parameter in an If statement to conditionally run the code only when the value in cell B3 is changed, as shown in the following example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Range(“B3”) Then

ActiveSheet.Name = Range(“B3”).Value

End If

End Sub

At this point, the code will assign the value entered in cell B3 as the name of the worksheet. However, Excel will display the worksheets in the order that they were added to the workbook, so now, you must determine how to automatically sort the names of the worksheets in alphabetical order.

Writing Code to Sort the Worksheets

The code required to alphabetize the names of the worksheets consists of two nested For Each…Next statements that loop through the Worksheets collection. As it loops, the code compares the names of each worksheet, and swaps the worksheets, when necessary, by using the Move method of the Worksheet object. In computer science, this kind of sorting algorithm is called a bubble sort. It is not the most efficient way to sort values, but because this scenario does not sort a large number of items, it will work fine.

To organize the code in the VBA project, the sorting code is in its own subroutine named SortSheets that can be called from the code in the SheetChange event handler. To create a new subroutine in the Visual Basic Editor, type Public Sub followed by the name of the subroutine, and then press ENTER. The code for SortSheets subroutine looks like the following code example.

Public Sub SortSheets()

 

Dim currentUpdating As Boolean

currentUpdating = Application.ScreenUpdating

 

Application.ScreenUpdating = False

 

For Each xlSheet In ActiveWorkbook.Worksheets

For Each xlSheet2 In ActiveWorkbook.Worksheets

If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then

xlSheet2.Move before:=xlSheet

End If

Next xlSheet2

Next xlSheet

 

Application.ScreenUpdating = currentUpdating

 

End Sub

Notice that the condition for the If statement that performs the comparison of worksheet names in the inner For Each…Next loop converts both worksheet names to lowercase by using the VBA LCase function.

If LCase(xlSheet2.Name) < LCase(xlSheet.Name) Then

This is done because the comparison operation is case-sensitive. Failing to convert the names to the same case will cause strings with uppercase characters such as “Tom” or “TOM” to be sorted before “harry.”

By default, Excel VBA code updates the screen every time it makes a change. Because the sorting code makes multiple passes to sort each worksheet, the screen will flash as the code compares and moves each worksheet. This flashing can be distracting to users and slow down the code as users add more worksheets to the workbook. To eliminate the flashing and speed up execution of the code, the ScreenUpdating property of the Application object is set to False before the sorting operation is started. After the sorting operation is completed, the ScreenUpdating property is set back to the value stored in the currentUpdating variable to restore screen updating to the state it was in when the SortSheets subroutine was called.

To pull all of this code together, modify the original SheetChange event handler to call the SortSheets subroutine after the name of a new or changed worksheet is assigned. The final code for this scenario looks like the following code example.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target = Range(“B3”) Then

ActiveSheet.Name = Range(“B3”).Value

Call SortSheets

Worksheets(Sh.Name).Activate

End If

End Sub

The new line of code Call SortSheets calls the SortSheets subroutine to automatically sort the worksheets after the name or the worksheet is assigned or changed. The line of code following that, Worksheets(Sh.Name).Activate, calls the Activate method of the Worksheet object to ensure that the worksheet that was current at the start of the sorting operation is active when sorting is completed.

Automatically Creating Workbook Files from Worksheet Data

In this scenario, the spreadsheet developer wants to copy a list of region names in a column on a master worksheet, including additional information about each region, and then run a macro that creates a new workbook file for each region. The coding strategy is to create a new worksheet in the current workbook for each region on the master worksheet, and then to save each of those worksheets as a workbook file.

The code for this solution assumes the following:

  • The master worksheet that contains the list of region names is named REGION SHEET.
  • The first three rows of the master worksheet contain boilerplate information that will be copied to each new workbook.
  • The list of region names is in column B of the master worksheet, and the first name in the list is in cell B4.
  • The row for each region contains additional information that will be copied to the new workbook for that region.

A simplified example of the data in the REGION SHEET worksheet looks like the following table.

Figure 3. Sample REGION SHEET data

Running the macro on this data should create four workbooks named North.xlsx, South.xlsx, East.xlsx, and West.xlsx. Each workbook should contain the first three rows of boilerplate data, followed by the row of information for that region.

Note:

The example shows only four items in the row for each region (region name, owner name, and their associated labels). Because the macro code is designed to copy the entire row, you can include additional information about the region in the row. Additionally, the code is designed to accommodate a variable number of region names, so the user can enter as many region names as necessary starting at cell B4.

The VBA code consists of three parts:

  • A macro to create a new worksheet for each region listed in column B.
  • A function in the macro that checks whether a worksheet with the same name exists before creating a new one.
  • A function in the macro that saves a region worksheet as a new workbook by using the region name as the file name.

Writing Code to Create New Worksheets

The basic strategy is to create a new worksheet for a region name, copy the data for that region to the new worksheet, and then save the new worksheet as a new workbook. When the new workbook is saved, the worksheet used to create that workbook is deleted. The following code example shows the entire CreateWorkbooks macro that creates new workbooks based on the contents of the REGION SHEET worksheet.

Sub CreateWorkbooks()

Dim newSheet As Worksheet, regionSheet As Worksheet

Dim cell As Object

Dim regionRange As String

 

Set regionSheet = Sheets(“REGION SHEET”)

 

‘ Turn off screen updating to increase performance.

Application.ScreenUpdating = False

 

‘ Build a string that specifies the cells in column B that

‘ contain region names starting from cell B4.

regionRange = “B4:” & regionSheet.Range(“B4”).End(xlDown).Address

 

For Each cell In regionSheet.Range(regionRange)

If SheetExists(cell.Value) = False Then

‘ Add a new worksheet.

Sheets.Add After:=Sheets(Sheets.Count)

‘ Set newSheet variable to the new worksheet.

Set newSheet = ActiveSheet

‘ Copy boilerplate data from first three rows

‘ of the master worksheet to the range starting at

‘ A1 in the new sheet.

regionSheet.Range(“A1:A3”).EntireRow.Copy newSheet.Range(“A1”)

‘ Copy and paste the column widths to the new sheet.

regionSheet.Range(“A1:A3”).EntireRow.Copy

newSheet.Range(“A1”).PasteSpecial xlPasteColumnWidths

‘ Copy the entire row for the current region and

‘ paste starting at cell A4 in the new sheet.

cell.EntireRow.Copy newSheet.Range(“A4”)

‘ Name the new sheet.

newSheet.Name = cell.Value

‘ Call the SaveWorkbook function to save the current

‘ worksheet as a new workbook file.

SaveWorkbook (cell.Value)

‘ Turn off alerts, and then delete the new worksheet

‘ from the current workbook.

Application.DisplayAlerts = False

newSheet.Delete

‘ Turn alerts back on.

Application.DisplayAlerts = True

End If

Next Cell

 

‘ Notify the user that the process is complete.

MsgBox “All workbooks have been created successfully”

 

‘ Turn screen updating back on.

Application.ScreenUpdating = True

 

End Sub

The first several lines declare variables that are used in the remaining lines of code to work with elements in the workbook. For example, the code uses the regionSheet variable to work with the REGION SHEET worksheet that contains the seed data for the new workbooks.

The following line of code creates a string that specifies the range of cells in column B that contain region names, and then assigns that value to the regionRange variable.

regionRange = “B4:” & regionSheet.Range(“B4”).End(xlDown).Address

The code that follows the & operator identifies the last cell in column B that contains a region name and returns its address. This enables you to add as many rows for region names as you need in column B, and then be sure that the code will create a workbook for every row that contains data. The code identifies the last cell that contains data by specifying the first cell that contains data, Range(“B4”), and then calls the End property of the Range object with the xlDown enumeration to move the selection to the last cell in column B that contains data, returning a Range object that represents that cell. This is equivalent to selecting cell B4 in the worksheet and then pressing END+DOWN ARROW. For the sample data at the beginning of this section, this operation will move the selection to cell B7 (which contains the region name “WEST”). The code then uses the Address property of the Range object to return the cell’s address.

The For Each…Next loop that follows then loops through each region name in the range specified by the regionRange variable to create a worksheet for each region. Before the macro attempts to create a new sheet, the If statement within the loop (If SheetExists(cell.Value) = False Then) passes the region’s name to the SheetExists function to check whether a worksheet with the region’s name already exists in the workbook. The code for the SheetExists function is another loop that checks each of the worksheets in the workbook’s Sheets collection, and returns True if it finds an existing worksheet, or False, if it does not. The following example shows the code for the SheetExists function.

Function SheetExists(sheetName As String)

Dim sheet As Worksheet

For Each sheet In Sheets

If sheet.Name = sheetName Then

SheetExists = True

Exit Function

Else

SheetExists = False

End If

Next

End Function

The first line of code after the SheetExists check adds a new worksheet after the last sheet in the workbook’s collection by using the Add method of the Sheets collection.

Sheets.Add After:=Sheets(Sheets.Count)

The remaining code in the CreateSheets macro calls the EntireRow property of the Range object to select the specified ranges of rows from the REGION SHEET worksheet, followed by a call to the Copy method to copy those rows to the new worksheet. Looking at the code, you might wonder why the Copy method is applied twice to the three rows of boilerplate information.

regionSheet.Range(“A1:A3”).EntireRow.Copy newSheet.Range(“A1”)

 

regionSheet.Range(“A1:A3”).EntireRow.Copy

newSheet.Range(“A1”).PasteSpecial xlPasteColumnWidths

 

This is done to preserve the column widths for wider cells in the boilerplate information. The first Copy method operation copies all of the data from the A1:A3 range in the REGION SHEET worksheet to the destination in the new worksheet, but using that operation alone does not preserve column width information. The second call to the Copy method does not pass a value for the Destination parameter, which causes the Copy method to copy the range data to the clipboard. The last line of code calls the PasteSpecial method with the xlPasteColumnWidths enumeration to paste the column widths to the destination in the new worksheet.

The next two lines of code name the new worksheet by using the name of the region, and then call the SaveWorkbook function to save the new worksheet as a new workbook file as shown in the following example.

‘ Name the new sheet.

newSheet.Name = cell.Value

‘ Call the SaveWorkbook function to save the current

‘ worksheet as a new workbook file.

SaveWorkbook (cell.Value)

 

The next section discusses how to write the code for the SaveWorkbook function.

The final lines of code inside the If statement delete the new worksheet after it has been saved as a new workbook by using the Delete method of the Worksheet object, as shown in the following example.

‘ Turn off alerts, and then delete the new worksheet

‘ from the current workbook.

Application.DisplayAlerts = False

newSheet.Delete

‘ Turn alerts back on.

Application.DisplayAlerts = True

Note that before calling the Delete method, the DisplayAlerts property of the Application object is set to False. That setting suppresses the confirmation dialog box that Excel displays when a user attempts to delete a worksheet that contains data.

The final lines of the CreateWorkbooks macro display a message box to notify the user that the process is complete by using the MsgBox function, and then turn screen updating back on as shown in the following example.

‘ Notify the user that the process is complete.

MsgBox “All workbooks have been created successfully”

 

‘ Turn screen updating back on.

Application.ScreenUpdating = True

 

Writing the Code to Create New Workbooks

The following example shows the code for the SaveWorkbook function.

Function SaveWorkbook(workbookName As String)

Dim filePath As String

 

filePath = “C:\Region Sheets\” & workbookName & “.xlsx”

Sheets(workbookName).Copy

ActiveWorkbook.SaveAs Filename:=filePath

ActiveWorkbook.Close

 

End Function

Most of the code in this function is easy to understand. The code uses the filePath variable to specify the path and file name for the new workbook (using the name of the region as the file name). The code example assumes that you have a folder named C:\Region Sheets, but you can change the code to use any existing location.

The effect of the following line of code is less obvious.

Sheets(workbookName).Copy

This line of code uses the Copy method of the Sheets collection. When the Copy method is called without specifying either of the optional Before or After parameters, it copies the worksheet into a new workbook in memory rather than to another location in the collection of worksheets in the current workbook. The remaining two lines of code use the ActiveWorkbook property of the Application object to access the new workbook, and then call the SaveAs and Close methods of the Workbook object to save and close it.

Conclusion

In this article, you learn how to write two Visual Basic for Applications (VBA) macro solutions that automate operations on worksheets in Excel 2007. The first solution automates the process of naming and sorting worksheets in the current workbook. The second solution automates the process of creating and naming sets of worksheets based on data in the current workbook, and then saves each new worksheet as a new workbook file.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: