excel 2007 unit i

Microsoft Excel 2007 Microsoft Excel 2007 - Illustrated- Illustrated

Automating Worksheet Automating Worksheet TasksTasks

2Microsoft Office Excel 2007 - Illustrated

• Plan a macroPlan a macro

• Enable a macroEnable a macro

• Record a macroRecord a macro

• Run a macroRun a macro

• Edit a macroEdit a macro

ObjectivesObjectives


• Use shortcut keys with macrosUse shortcut keys with macros

• Use the Personal Macro WorkbookUse the Personal Macro Workbook

• Assign a macro to a buttonAssign a macro to a button

ObjectivesObjectives


Unit IntroductionUnit Introduction

• A A macromacro is a set of instructions that is a set of instructions that performs tasks in the order you performs tasks in the order you specifyspecify• Create macros to automate frequently Create macros to automate frequently

performed tasks that require a series of performed tasks that require a series of stepssteps


Plan a MacroPlan a Macro

• Create macros for tasks that you Create macros for tasks that you perform on a regular basisperform on a regular basis• To create a macro, you record the To create a macro, you record the

series of actions in a special series of actions in a special programming languageprogramming language

• The sequence of actions is important, The sequence of actions is important, so a macro needs to be carefully so a macro needs to be carefully plannedplanned


Plan a Macro (cont.)Plan a Macro (cont.)

• Macro planning guidelinesMacro planning guidelines• Assign the macro a nameAssign the macro a name• Write out the steps the macro will Write out the steps the macro will

performperform• Decide how you will perform the actionsDecide how you will perform the actions• Practice the steps you want Excel to Practice the steps you want Excel to

record and write them downrecord and write them down• Decide where to locate the description Decide where to locate the description

of the macro and the macro itselfof the macro and the macro itself


Enable a Macro (cont.)Enable a Macro (cont.)

• Macro and virusesMacro and viruses• Macros can contain viruses which can Macros can contain viruses which can

damage your computerdamage your computer• Always disable macros if you are not Always disable macros if you are not

working with them.working with them.


Recording a MacroRecording a Macro

• Use the Excel Macro Recorder to Use the Excel Macro Recorder to create a macrocreate a macro• Click the Record Macro button Click the Record Macro button • Name the macroName the macro• Enter the keystrokes and select the Enter the keystrokes and select the

commands you wantcommands you want• Click the Stop Recording buttonClick the Stop Recording button

• Each action you record is translated Each action you record is translated into program codeinto program code


Recording a Macro (cont.)Recording a Macro (cont.)

Enter macro name here


Running a MacroRunning a Macro• Once you record a macro, you should test Once you record a macro, you should test

it to make sure it runs properlyit to make sure it runs properly• Select the macro in the Macro dialog box, then Select the macro in the Macro dialog box, then

click Run to test itclick Run to test it

Listed macros appear here


Editing a MacroEditing a Macro

• The program instructions of a macro, The program instructions of a macro, called called program codeprogram code, are , are automatically recorded in the Visual automatically recorded in the Visual Basic for Applications (VBA) Basic for Applications (VBA) programming languageprogramming language• Each macro is stored as a Each macro is stored as a modulemodule, or , or

program code container attached to the program code container attached to the workbookworkbook

• Edit a macro with the Visual Basic Edit a macro with the Visual Basic EditorEditor


Editing a Macro (cont.)Editing a Macro (cont.)

Comment

Code window

Project Explorer with open module

Properties window

Macro program code


Editing a Macro (cont.)Editing a Macro (cont.)

• Adding comments to Visual Basic Adding comments to Visual Basic codecode• You can explain macro code by adding You can explain macro code by adding

commentscomments• Comments are explanatory text added Comments are explanatory text added

to the lines of codeto the lines of code• A comment must be preceded by an A comment must be preceded by an

apostropheapostrophe


Using Shortcut Keys Using Shortcut Keys with Macroswith Macros

• Assign a shortcut key combination to Assign a shortcut key combination to run a macrorun a macro• Reduces the number of steps it takes to Reduces the number of steps it takes to

run a macrorun a macro• Assign a shortcut key combination in Assign a shortcut key combination in

the Record Macro dialog boxthe Record Macro dialog box


Using Shortcut Keys with Using Shortcut Keys with Macros (cont.)Macros (cont.)

Shortcut to run macro


Using the Personal Using the Personal Macro WorkbookMacro Workbook

• Store commonly used macros in a Store commonly used macros in a Personal Macro WorkbookPersonal Macro Workbook• The Personal Macro Workbook is The Personal Macro Workbook is

always available no matter which always available no matter which workbook is openworkbook is open

• Created the first time you store a macroCreated the first time you store a macro


Using the Personal Using the Personal Macro Workbook (cont.)Macro Workbook (cont.)

Click to store in new blank

workbook

Stores macro in active workbook

Click to store in Personal Macro Workbook


Using the Personal Using the Personal Macro Workbook (cont.)Macro Workbook (cont.)

• Working with the Personal Macro Working with the Personal Macro WorkbookWorkbook• By default the Personal Macro By default the Personal Macro

Workbook is hidden as a precautionary Workbook is hidden as a precautionary measure so you don’t accidentally measure so you don’t accidentally delete anything from itdelete anything from it


Assign a Macro to a buttonAssign a Macro to a button

• You can run a macro by assigning it You can run a macro by assigning it to a button on your worksheet. to a button on your worksheet.

• When you click the button the macro When you click the button the macro will run.will run.


Assigning a Macro to a button Assigning a Macro to a button (cont.)(cont.)

Add text to label the button


SummarySummary

• Use Excel Macro Recorder to create Use Excel Macro Recorder to create a macroa macro

• Run a macro to test itRun a macro to test it

• Use the Visual Basic Editor to edit a Use the Visual Basic Editor to edit a macromacro

• Use shortcut keys with macrosUse shortcut keys with macros

• Use the Personal Macro WorkbookUse the Personal Macro Workbook

excel 2007 unit i

Technology

visual basic

shortcut key

personal macro

excel macro

shortcut keys

personalmacro

adding comments

create macros