Excel 2016 Macros

Excel 2016

Lesson 3 – Editing Macros

the NEWYORK.XLSX workbook. Use the Close_Cities macro to close the TAMPA.XLSX , ATLANTA.XLSX , and CHICAGO.XLSX workbooks. Close CITY57.XLSM.

T YPING M ACRO C OMMANDS  D ISCUSSION

In addition to recording macro commands, you can also type them directly into a macro procedure. Macro procedures work with statements and objects. Some macro steps, such as macro statements, cannot be recorded. For example, you may want to create a condition that executes specific macro commands if the condition is true and other commands if the condition is false. Objects are the elements upon which the macro acts, such as a workbook, worksheet, range, cell, or chart. A collection is a set of multiple objects. The worksheets in a workbook are part of a collection. You use VBA (Visual Basic for Applications) commands to change the properties of an object or collection or to apply a method (action) to an object. The properties and methods differ depending upon the object.

When entering a command, you must use the proper syntax. The syntax of a command is the structure of its elements. As you type commands in the Visual Basic Editor, lists with available properties or methods may appear.

There is often more than one way to perform the same action. For example, you can record a macro that deletes the contents in the range A6:F12 in the Sales worksheet. The steps could be recorded using the following selection methods since you would be manually selecting the worksheet and range.

Sheets("SALES").Select Range("A6:F12").Select Selection.ClearContents

You can accomplish the same result by writing a command, such as the one shown below, that applies a method to the specified object. The object is the range A6:F12 in the Sales worksheet. The command assumes that the correct workbook is open and can be written without the Worksheets property if the command to activate the worksheet was issued in a previous step.

Worksheets("SALES").Range("A6:F12").ClearContents

OFFICEPRO, Inc.

Page 51