excel 07 macros

7/28/2019 Excel 07 Macros

1/21

Excel 2007: Introduction to Macros

Instructional Services at KU LibrariesA Division of Information Services

www.lib.ku.edu/instruction

Abst ract:This handout covers the basics of creating and editingMacros in Excel 2007. Navigating the Visual Basic Editorand creating basic edits in the Visual Basic Editor will alsobe covered.

Contents

Related Training Available from Instructional Services ................................................................ 2Introduction ..................................................................................................................................... 2Macro Security ................................................................................................................................ 2EnableAllMacros ........................................................................................................................... 4Recording Macros ........................................................................................................................... 6Playing a Macro .............................................................................................................................. 9Assigning a Keystroke to a Macro ................................................................................................ 10Recording a Relative Reference Macro ........................................................................................ 12Running a Relative Reference Macro ........................................................................................... 13Copying a Macro from a Workbook or Template ........................................................................ 14Visual Basic and Macros .............................................................................................................. 15Opening the Visual Basic Editor................................................................................................... 15

2009 The University of Kansas. All rights reserved
http://www.lib.ku.edu/instructionhttp://www.lib.ku.edu/instructionhttp://www.lib.ku.edu/instruction


2/21

Instructional Services


Instructional Services at KU Libraries - 2 - 2009 The University of Kansas

A Division of Information Services

Understanding the Macro Code .................................................................................................... 19Additional Resources .................................................................................................................... 21Related Training Available from Instructional Services .............................................................. 21Getting Additional Help ................................................................................................................ 21

Related Training Available from Instructional Services

All workshops offered by Instructional Services are free to KU students, staff, faculty, andapproved

affiliates.

To learn more about or register for workshops, receive automatic announcements of upcoming

workshops, and track workshops youve registered for and have attended, visit

www.lib.ku.edu/instruction/workshops. For further workshop related questions, please [email protected].

Introduction

A macro can be described as a tiny program that uses Visual Basic code to automate asequence of actions or instructions. A macro can be simple and consist of only a few tasks orcommands, or be quite complex, involving lots of data manipulation and calculations. To createcomplex macros, it is worthwhile to invest time in learning Visual Basic programming. But forsimple macros, you dont have to do any programming at all.

Simple macros are great for any long sequence of keystrokes that you find yourself repeatingoften, or for combinations of basic Excel actions that would be convenient to automate for aparticular workbook. Macros can be saved with the workbook in which they were created, orthey can be saved in a separate personal macro workbook where they are more accessible.

Macro Security

It is well known that VBA macros can be a source or carrier of computer viruses. Because ofthis, you should never use a macro if you do not know where it came from, and you shouldnever open a document that contains a macro unless you trust the source of the document.

If you click the Macro Security button on the Developer tab you will see an Excel Trust Centerwindow open to display a list of options for macro security.
http://www.ku.edu/computing/services/affiliates.shtmlhttp://www.ku.edu/computing/services/affiliates.shtmlhttp://www.lib.ku.edu/instruction/workshopsmailto:[email protected]:[email protected]://www.lib.ku.edu/instruction/workshopshttp://www.ku.edu/computing/services/affiliates.shtmlhttp://www.ku.edu/computing/services/affiliates.shtml


3/21





You can also display these options by first clicking the Excel Options button on the Office menu,and then choosing the Trust Center option followed by the Trust Center Settings button.

Under the Macro Settings heading, you have four options to choose from:

DisableallmacroswithoutnotificationThis setting will prevent macros in files that are not in trusted locations from being run. (Trustedlocations are normally on your own hard drive or shared folders on your network. If you selectthis option, macros that are not in a trusted location will be disabled. Any alert boxes ormessages associated with macro security will also be disabled.

DisableallmacroswithnotificationThis is Excel 2007s default macro security setting. With this setting, macros in files that are notin trusted locations will be disabled, but you will still see security alerts if a workbook from a nontrusted location contains a macro.

This means you will have the option of choosing to allow or not allow the macro to run.


4/21





Depending on your Trust Center settings, you may see a warning dialogue when you open aworkbook containing a macro.

Once again, clicking the Enable Macros button will allow you to run the macro.

DisableallmacrosexceptdigitallysignedmacrosIf you choose this setting, Excel will basically behave in the same way as it does when youchoose Disable all macros with notification. The only difference is that if the macro is digitallysigned by a publisher that is trusted, you will be able to run the macro. If a macro is digitallysigned but the publisher is not trusted, you will then have the option to either enable the macrosor to trust the publisher. Any macro that is not digitally signed will not be allowed to run.

EnableAllMacrosThis is the weakest macro security setting available. If you use this setting, a macro from anyworkbook can be run, even if it is not in a trusted location and it is not digitally signed. Becauseof the risks associated with running unknown macros, use this setting with caution.

To set the macro security level, select the radio button corresponding to the level of security thatyou want and click the OK button in the lower right of the Trust Center window.


5/21





To digitally sign a macro, click the Developer tab to display the Developer Ribbon and then clickthe Visual Basic button to launch the Visual Basic Editor.

In the tool bar at the top of the Visual Basic Editors window, click the Project Explorer button.

In the navigation panel on the left of the editor window, highlight the VBA Project/Worksheetcontaining the macro you want to sign, and then click the Digital Signature option from theVisual Basic Editors Tools menu.

This will display a Digital Signature dialogue box.


6/21



Click the Choose button to display a list of available certificates. When you are finished, click theOK button.

Recording MacrosIt is not difficult to create a macro in Excel 2007, but first, you should make sure that theDeveloper tab is accessible. To do this, click the Excel Options button on the Office menu andthen click the Popular option on the left of the Excel options window. Under the Top Options forworking with Excel option, you will see a checkbox next to the words Show Developer Tab in theRibbon.



If you check this box, and click OK in the options window, the Developer tab will appear at thetop of the Excel screen. If you click the Developer tab, you will see the Developer Ribbon.


7/21





This Developer Ribbon is your gateway to creating and editing macros in Excel 2007. To recorda macro, click the Record Macro button in the Code button group.

This will display the Record Macro dialogue box.

Here you can give your new macro a name (do not use spaces) and choose the location whereit should be stored. Your options are to store it with this workbook, another workbook, or aspecial macro workbook.

This special macro workbook is called the personal macro workbook. A macro that is storedhere can be run in any Excel workbook on the same computer. If you are creating a macro thatyou think you would like to use in multiple workbooks you should consider saving it in thepersonal macro workbook.

You can also enter a brief description of the macro you are about to create in the bottom textarea (labeled Description). By default, the macro will be called Macro1 (if this is your first macro)and it will be saved in the current workbook.

If you click OK, you will see two buttons appear in the lower left corner of the Excel screen in thestatus bar.


8/21





The small blue square is the stop button, which will stop the macro from recording. The smallgreen triangle is a play button, which is used to play a recorded macro.

When you see these buttons, every keystroke or Excel action that you take with the mouse willbe translated into VBA (Visual Basic for Applications) code by Excel. This code is what makesup your macro.

When you are finished performing the actions or keystrokes you wanted to record, you can stoprecording the macro by clicking the small blue square in the status bar in the lower left.

The sequence of Excel operations or procedures that you recorded will be replayed (meaningthe actions will be performed) every time you run the macro.

If you record a macro and then try to save your workbook, you may see an alert like thefollowing.

If you click the Yes button, the macros created and stored in this workbook will not be saved. Tokeep these macros, you must specify a macro enabled workbook in the Save As type field in theSave As dialogue box.

If the macro is stored in the personal macro workbook, you can run the macro in any workbookyou open (given the appropriate security settings), even if the open workbook was not saved asa macro enabled workbook.

If you choose to store a macro in the personal macro workbook, you will get the followingmessage when you close Excel.


9/21



Instructional Services at KU Libraries Kansas


Clicking the Yes button will commit the macro you created to the personal macro workbook.Macros in this personal macro workbook will be available for use when you open Excel.

Playing a Macro

When you play a macro, the actions or keystrokes you chose for your macro will be performed

in the sequence in which they were recorded.

To play a macro, click the Macros button on the Developer Ribbon.

When you click this button you will see the Macro dialogue box.

- 9 - 2009 The University of


10/21



Instructional Services at KU Libraries Kansas


Here you can see the macros available for use. Note that macros saved in the personal macroworkbook will have Personal.XLSB! in front of their name (such as the macro selected in thelist).

To play the selected macro, click the Run button. To view or edit the Visual Basic code for theselected macro, click the Edit button. To delete the selected macro, click the Delete button.

When you click the Run button, the Macro dialogue box will disappear, and the sequence ofinstructions that was recorded in the macro will be performed on the current workbook.

Note: You can see the macros from this workbook and from the personal macro workbookbecause the All open workbooks option is selected in the bottom drop list of the macrodialogue box. If you select the This workbook option from the list, or the Personal.xlsb option,

only the macros corresponding to the specific option you select will be shown.

Assigning a Keystroke to a Macro

There are two ways to assign a shortcut key to a macro. You can assign a shortcut key whenyou are first recording your macro or you can assign a short cut key to an existing macro.

To assign a shortcut to a macro when you record it, click the Record Macro button on theDeveloper Ribbon. When you see the Record Macro box, just enter a letter in the Shortcut Keydata field. (Note: if you select a shortcut key that has already been assigned you wil l bereassigning it, use the Shift method explained below to avoid reassigning shor tcut keys)

- 10 - 2009 The University of


11/21





Here you can see that the letter d has been assigned as a shortcut key for this macro. Once themacro has been recorded, pressing Ctrl + d will run it. This avoids the process of picking amacro from the Macro dialogue box every time you want to run it. If you press Shift + D whenyou enter the shortcut key, the shortcut will then become Ctrl + Shift + D for the macro.

If you store the macro in the personal macro workbook, the shortcut will be carried with itwherever you use the macro.

To assign a shortcut to an existing macro, click the Macro button to display the Macro dialoguebox. In the Macro dialogue box, select the name of the macro you want to assign a shortcut keyto and then click the Options button.

This will open the Macro Options dialogue box for the macro you selected.


12/21





There is a small data field that can be used to enter a shortcut key for this macro. You can entera letter or use a Shift key/letter combination as before. If you click OK, the shortcut key will be

assigned to the macro.

Recording a Relative Reference Macro

By default, macros employ absolute cell referencing. This means that a macros actions areperformed on the same cells every time the macro is run. If you specify relative cell referencingfor a macro, the actions performed by the macro will be relative to the active cell when you startthe macro. This means that the macro can perform actions on different cells each time it is run,depending on what cell is used as the starting point.

To record a relative reference macro, choose your active cell and click the Use RelativeReferences button before you begin recording.

Once you click this button, simply click the Record Macro button as before to create the actualmacro. When you stop the macro recording process, all of the cells that were involved in themacro will be treated as having relative references.

Lets say, for example, that the active cell is B1 and you create a relative reference macro thatshades the cell C1 in blue. If you make cell K10 the active cell and run the macro, cell L10 willbe shaded in blue. If this were an absolute reference macro, cell C1 will become shaded in blueno matter what the active cell is when you run the macro.


13/21



Instructional Services at KU Libraries 2009 The University of Kansas


Running a Relative Reference Macro

To run a relative reference macro, first make sure that the active cell is chosen such that thecells that you want the macro to run on are in the correct relative position.

As an example, suppose that you created your relative reference macro with A1 as the activecell. The macro that you created changes the column of six cells immediately below A1 (A2:A7)to accounting format.

Here is the data after recording the macro.

- 13 -

Now, if you wanted to convert cells H3:H8 to accounting format, first pick cell H2, which is in thesame relative position to H3:H8 as cell A1 was to A2:A7.

Now, click the Macro button and choose the correct macro from the Macro dialogue box.


14/21





Copying a Macro from a Workbook or Template

If you have an existing macro in a macro enabled workbook or a macro enabled template andyou would like to use that macro in another workbook, you can copy the macro from the sourceworkbook or template into the workbook of your choice.

First, you must use Excel to open the template or workbook containing the macro, as well as theworkbook that you are copying the macro into. When both workbooks are open, select theDeveloper tab on the destination workbook and click the Visual Basic button to display theVisual Basic Editor.

When the Visual Basic Editor opens, you will see all of the currently open workbooks in theProject Explorer panel on the right.

(If you cannot see the Project Explorer panel, click the Project Explorer button in the VisualBasic Editor toolbar.)

When you record a macro for a workbook, it will be contained in a code module shown in theVisual Basic Editor. In the image shown above you can see that Book3 (the destinationworkbook) has no modules, but the other open workbooks and templates (macTemplate.xltmand sourcebook.xlsm) have module folders with them.


15/21





To copy a macro from a template or a workbook, open the module folder for the workbook ortemplate that you want to copy from by clicking on the plus (+) sign corresponding to the folder.

In the following image, the modules folder for macTemplate.xltm has been opened.

To copy the macro from macTemplate.xltm to Book3, just select and drag Module1 from theopen Modules folder up to the Book3 area (drag until you see your mouse pointer turn to a plus(+).

Visual Basic and Macros

You can overcome the limitations of basic recorded macros by using VBA (which stands forVisual Basic for Applications). In Excel, you can add Visual Basic code to your macros to makethem even more powerful.

Opening the Visual Basic Editor

You can open the editor from within Excel in a couple of ways. As you have already seen,

clicking the Visual Basic button on the Developer Ribbon will display the Visual Basic Editor.


16/21





You can also click the Macros button and select a macro that you want to work with from theMacro Dialogue box.

If you click the Edit button on the right of the box, the Visual Basic Editor window will open toshow the Visual Basic code for the selected macro.


17/21





Across the top of the editor window, you will see a menu bar containing menus for saving files,editing code, debugging code, running macros, inserting objects, and more.

J ust beneath this menu bar, you will find a toolbar containing buttons for many of the menuitems, including save, undo, help, copy, paste, and find.

At the far right of this tool bar, you will see a field containing the line and column number of thecursor in the actual Visual Basic editing window. In this image, the cursor is at line 11 andcolumn 24.

In the large main area of the Visual Basic Editor, you will find the actual editing windows.


18/21





These editing windows are where you read, enter, and directly modify the Visual Basic code.You can even open a number of these windows at the same time for different code modules.This gives you great flexibility when it comes to modifying code, as you can see, type, and copyand paste code from one window to another.

On the left of the Visual Basic Editor, you will see the Project Explorer panel. This panel showsthe contents of the currently open workbooks and templates. If you double click on a code

module in the project explorer, the code will open in an editing window where you can modify oradd to it. You can expand and collapse the view of the contents with the (+) and (-) buttons.

If you cannot see the project explorer panel in your Visual Basic Editor, click the ProjectExplorer button on the toolbar to display it.


19/21





Understanding the Macro Code

The process of adding VBA (Visual Basic for Applications) code to your macro does not alwayshave to be difficult. There is an easy and useful shortcut you can take to add code in certainsituations. Moreover, this particular shortcut can teach you a lot about how Visual Basic codeworks.

First, you have to know how to view the VBA code for a macro. To see the code for a macro,click the Macros button on the Developer Ribbon to display the Macro dialogue box.

You can see that there are two macros available for this workbook. If you select one from the listand click the Edit button, you will see the code for the macro that you selected in a Visual Basicwindow.

If there are no macros available for editing in your Macro Dialogue box, you can quickly record asimple macro by clicking the Record Macro button on the Developer Ribbon. (See Lesson 2.1

for more information.)

A macro is essentially a small computer program written in Visual Basic. When you record amacro, Excel writes the Visual Basic code for you. When you view the code for a macro, you are


20/21





looking at the sequence of Visual Basic instructions that perform the specified tasks required ofthe macro.

The name of the macro comes after the word Sub in the first line; you can see that this macro isnamed Macro2.

The statement Selection.Font.Bold = True applies a bold font to the selected range.

The statements in green are comments that provide information about the macro.

The lines of code between the words With and End With create background shading for theselection.

The type of pattern for the shading is designated as solid, with the line: .Pattern = xlSolid

The color/tint of the fill is given by the number -0.249977111117893, from the line:

.TintAndShade = - 0.249977111117893. If you change this to a different number (say to 1) thecolor of the background shading will be different (black).

The words End Sub indicate the end of the macro.


21/21





Additional Resources

Microsoft Office http://office.microsoft.com/en-us/excel/CH101001571033.aspx

About.com http://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htm

Related Training Available from Instructional Services

All workshops offered by Instructional Services are free to KU students, staff, faculty, andapproved

affiliates.

To learn more about or register for workshops, receive automatic announcements of upcoming

workshops, and track workshops youve registered for and have attended, visit www.infotraining.ku.edu.

For further workshop related questions, please email [email protected].

Getting Addi tional Help

IT Customer Service Center staff are available for computing assistance seven days a week. Call, email,

or stop by in person at the Computer Center to get help.

(785) 864-8080

[email protected]

www.technology.ku.edu/~helpdesk

Last Update: 05/07/2009
http://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htmhttp://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htmhttp://www.hreo.ku.edu/policies_procedures/category_details/13http://www.hreo.ku.edu/policies_procedures/category_details/13http://www.infotraining.ku.edu/mailto:[email protected]:[email protected]://www.technology.ku.edu/~helpdesk/http://www.technology.ku.edu/~helpdesk/http://www.technology.ku.edu/~helpdesk/mailto:[email protected]:[email protected]://www.infotraining.ku.edu/http://www.hreo.ku.edu/policies_procedures/category_details/13http://www.hreo.ku.edu/policies_procedures/category_details/13http://spreadsheets.about.com/od/advancedexcel/ss/080703macro2007.htm

excel 07 macros

Documents