Macros: Automating tasks you perform frequently

If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands.

Record a macro to do a task in one step

Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make are also recorded. When you record macros, Visual Basic stores each macro in a new module attached to a workbook.

For example, if you often enter long text strings in cells, you can record a macro to format those cells so that the text wraps. Select a cell in which you want text to wrap and start recording. Click Cells on the Format menu, click the Alignment tab, select the Wrap text check box, click OK, and then click Stop Recording

You can run a macro by choosing it from a list in the Macro dialog box. To make a macro run whenever you click a particular button or press a particular key combination, you can assign the macro to a toolbar button, a keyboard shortcut, or a graphic object on a worksheet. 

After you record a macro, you can view the macro code with the Visual Basic Editor to correct errors or change what the macro does. The Visual Basic Editor is a program designed to make writing and editing macro code easy for beginners, and provides plenty of online Help. You don't have to learn how to program or use the Visual Basic language to make simple changes to your macros.

The Visual Basic Editor displays the code for recorded macros in a module window.

Sub wrapper()
'
' wrapper Macro
' Macro to make a cell formatted to wrap text
'
' Keyboard Shortcut: Ctrl+w
'
Range("A1").Select
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With
End Sub

You could simplify this macro by deleting all of the indented lines between "With Selection" and "End With" except ".WrapText = True".

With the Visual Basic Editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, rename the modules that store the macros, or rename the macros. For example, if you wanted the text-wrapping macro to also make the text bold, you could record another macro to make a cell bold and then copy the instructions from that macro to the text-wrapping macro.

Microsoft Excel 2000 provides safeguards against viruses that can be transmitted by macros. If you share macros with others, you can certify them with a digital signature so that other users can verify that they are from a trustworthy source. Whenever you open a workbook that contains macros, you can verify their source before you enable them.

The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

The following properties for returning a Workbook object are described in this section:

· Workbooks property

· ActiveWorkbook property

· ThisWorkbook property

Use Workbooks(index), where index is the workbook name or index number, to return a single Workbook object. The following example activates workbook one.

The index number denotes the order in which the workbooks were opened or created. Workbooks(1) is the first workbook created, and Workbooks (Workbooks. Count) is the last one created. Activating a workbook doesn’t change its index number. All workbooks are included in the index count, even if they’re hidden.

The Name property returns the workbook name. You cannot set the name by using this property; if you need to change the name, use the SaveAs method to save the workbook under a different name. The following example activates Sheet1 in the workbook named "Cogs.xls" (the workbook must already be open in Microsoft Excel).

The ActiveWorkbook property returns the workbook that’s currently active. The following example sets the name of the author for the active workbook.

The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property won’t return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.

If you’ll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.

Worksheets Property

Represents a worksheet. The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.

The following properties for returning a Worksheet object are described in this section:

· Worksheets property

· ActiveSheet property

Use Worksheets(index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook.

The worksheet index number denotes the position of the worksheet on the workbook’s tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets (Worksheets. Count) is the last one. All worksheets are included in the index count, even if they’re hidden.

The worksheet name is shown on the tab for the worksheet. Use the Name property to set or return the worksheet name. The following example protects the scenarios on Sheet1.

The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

When a worksheet is the active sheet, you can use the ActiveSheet property to refer to it. The following example uses the Activate method to activate Sheet 1, sets the page orientation to landscape mode, and then prints the worksheet.

This example sets the font size for cell C5 on Sheet1 to 14 points.

This example clears the formula in cell one on Sheet1.

This example sets the font and font size for every cell on Sheet1 to 8-point Arial.

This example loops through cells A1:J4 on Sheet1. If a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

This example sets the font style for cells A1:C5 on Sheet1 to italic.

This example scans a column of data named "myRange." If a cell has the same value as the cell immediately above it, the example displays the address of the cell that contains the duplicate data.

This example sets the value of cell A1 on Sheet1 to 3.14159.

This example creates a formula in cell A1 on Sheet1.

This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces that value with 0 (zero).

This example loops on the range named "TestRange" and displays the number of empty cells in the range.

This example sets the font style in cells A1:C5 on Sheet1 to italic. The example uses Syntax 2 of the Range property.

Repeats a group of statements for each element in an array or collection.

For Each element In group
[statements]
[Exit For]
[statements]

Next [element]

The For...Each...Next statement syntax has these parts:

The For...Each block is entered if there is at least one element in group. Once the loop has been entered, all the statements in the loop are executed for the first element in group. If there are more elements in group, the statements in the loop continue to execute for each element. When there are no more elements in group, the loop is exited and execution continues with the statement following the Next statement.

Any number of Exit For statements may be placed anywhere in the loop as an alternative way to exit. Exit For is often used after evaluating some condition, for example If…Then, and transfers control to the statement immediately following Next.

You can nest For...Each...Next loops by placing one For...Each...Next loop within another. However, each loop element must be unique.

Note   If you omit element in a Next statement, execution continues as if element is included. If a Next statement is encountered before its corresponding For statement, an error occurs.

You can't use the For...Each...Next statement with an array of user-defined types.

This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces the value with 0 (zero).

This example loops on the range named "TestRange" and then displays the number of empty cells in the range.

This example sets the standard font to Geneva (on the Macintosh) or Arial (in Windows).