Excel 2016: Automating tasks with Macros

Updated 2018-11-30

Objectives

·         Learn how to create simple macros using the macro recorder.

Macros

Sometimes you may find yourself doing the same sequence of commands over and over and over. When this happens, you may wish that you could just press one key and the whole sequence of commands could be executed. This is what "macros" are for!

"Macro" is short for macro-instruction. "Macro" means "big", so a macro is a "big instruction". A macro is a collection of commands in Excel that is given a name and, optionally, assigned to a key on the keyboard (or a button on the ribbon). Then whenever you want to execute the collection of instructions, all you have to do is press the key that has been assigned to them (or click on the button on the ribbon), and the program will execute all of the instructions as if they were a single instruction.

To create a macro

All of the features for creating macros are on the Developer tab. If the Developer tab is not visible on your version of Excel, do the following:

·         Click on the File tab.

·         Click on the Options button near the bottom of the menu.

·         In the Categories pane (left side), click Customize Ribbon.

·         In the list of Main Tabs (right side), click Developer to turn the check box on.

·         Click OK to close the Options dialog box.

To enable macros in Excel

Load the Advising.xlsm file. Note that the file extension is "xlsm", not "xlsx". The "m" means that this is a "macro-enabled" file—it has macros in it. If Excel has disabled macros for security reasons and does not let you open this file, you can change the settings by doing the following:

1.       Click on the Developer tab.

2.       In the Code group, click on the Macro Security button.

http://www.tomkleen.com/Excel/2013/010-Macros_files/image001.jpg

3.       Click on the Enable All Macros radio button.

http://www.tomkleen.com/Excel/2013/010-Macros_files/image002.jpg

4.       Click on the OK button

5.       Now Excel requires you to exit and re-open Excel; otherwise the changes you just made will not take effect!

Example: Font macros

Changing the font is something that is done frequently. In Excel, you must click on the Home tab, and then, in the Font group, click on the down-arrow in the font name box and choose the font that you want. Or, you can right-click and click on the down-arrow in the font name box and choose the font that you want. It would be nice if a single keystroke could change the font for you. If you have a handful of fonts that you use repeatedly (I frequently use Verdana, Lucida Console, and Courier New), you can create a macro for each font.

Creating a Verdana macro:

·         Select the text that you wish to change to Verdana.

·         Click on the Developer tab.

·         In the Code group, if the Use Relative References button is not activated, click on it to activate it.

http://www.tomkleen.com/Excel/2013/010-Macros_files/image003.jpg

·         In the Code group, click on the Record Macro button. The Record Macro dialog box will appear.

·         Macro Name: Enter Verdana for the macro name. Macro names must be made up of letters and digits, must begin with a letter, and cannot have blanks in them.

·         Shortcut Key: In the Shortcut key box, enter the upper-case letter V (for "Verdana"). Making it upper-case will still allow ctrl+v (lower-case v) to be recognized as the "paste" command.

·         Store macro in: If you click on Personal Macro Workbook, the macro will be available in all of your workbooks. However, if you are working in the computer lab, this won't work, so click on This Workbook and it will be available in this workbook only.

·         Click on the OK button.

·         Every keystroke, mouse movement, menu selection, etc., that you make from now on will be "recorded" as part of the macro:

·         Click on the Home tab.

·         In the Font group, click on the font drop-down list box and select Verdana.

·         This is the end of your macro.

·         To end your macro, Click on the Developer tab. Then, in the Code group, click on Stop Recording.

 

Test your macro by selecting some text and typing Ctrl+Shift+V. The font for the selected text should change to Verdana.

Creating a Times New Roman macro:

·         Select the text that you wish to change to Times New Roman.

·         Click on the Developer tab.

·         In the Code group, if the Use Relative References button is not activated, click on it to activate it.

·         In the Code group, click on the Record Macro button.

·         Enter Times for the macro name.

·         In the Shortcut key box, enter an upper-case letter T (for "Times").

·         Click on the OK button.

·         Every keystroke, mouse movement, menu selection, etc., that you make from now on will be "recorded" as part of the macro:

·         Click on the Home tab.

·         In the Font group, click on the font drop-down list box and select Times New Roman.

·         This is the end of your macro.

·         To end your macro, Click on the Developer tab. Then, in the Code group, click on Stop Recording.

 

Test your macro by selecting some text and typing Ctrl+Shift+T (upper case). The font for the selected text should change to Times New Roman.

Creating a macro that filters BUAD majors:

·         Click on the Developer tab.

·         In the Code group, if the Use Relative References button is not activated, click on it to activate it.

·         In the Code group, click on the Record Macro button.

·         Enter BUADMajors for the macro name (no blanks).

·         In the Shortcut key box, enter the letter (upper-case) B (for "Business"). Making it upper-case (Ctrl+Shift+B) will distinguish it from the "bold" command (Ctrl+b).

·         Click on the OK button.

·         Every keystroke, mouse movement, menu selection, etc., that you make from now on will be "recorded" as part of the macro.

·         To be sure that your macro works, regardless of which cell the cursor starts in, the first step in your macro must be to put the cursor in the data. Click on the Home tab. In the Editing group, click on the Find and Select button. Click on Go to. Enter an absolute cell reference that is somewhere in the data (e.g. $B$2). This will position the cursor at the selected cell. Click on the OK button.

·         On the Data tab, in the Sort and Filter group, click on the Filter button.

·         Click on the drop-down arrow at the top of the Major column.

·         Click on the (Select All) check box to de-select it (and de-select all of the different major values). Click on BUAD, then click on OK.

·         This is the end of your macro.

·         To end your macro, Click on the Developer tab. Then, in the Code group, click on Stop Recording.

 

Test your macro by turning the AutoFilter feature off, moving the cursor out of the data and pressing Ctrl+Shift+B.

Editing macros

All macros are recorded in a language called Visual Basic. Because Excel translates all of your input into Visual Basic commands, it is not necessary for you to learn Visual Basic to be able to use macros. However, you can still look at the Visual Basic commands that make up the macro and edit them, without having to learn Visual Basic. You can find a little more info on Visual Basic here.

Editing the BUAD filter macro to make it a MATH filter macro:

·         On the Developer tab, in the Code group, click on the down-arrow on the Macros button.

·         From the menu, click on the Macros button.

·         Click on the name of the macro that you want to modify. In this case, select BUADMajors.

·         Click on the Edit button.

·         If the macros aren't displayed right away, you can probably locate them by double-clicking on Module1. The actual Visual Basic instructions that make up your macro will appear in an editor in the right window.

·         Find the desired macro in the editor. Each macro is marked off by horizontal lines separating it from other macros. The macro begins with the word "sub" followed by the macro name, and ends with the words "end sub".

·         Although it is difficult to do much editing without a knowledge of Visual Basic, you can make some simple modifications by deleting existing lines and modifying existing lines.

·         Your macro should look like this.
    Application.Goto Reference:="R2C2"
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$44").AutoFilter Field:=3, Criteria1:=
"BUAD"

·         Replace the "BUAD" with "MATH":

·             ActiveSheet.Range("$A$1:$F$44").AutoFilter Field:=3, Criteria1:="MATH"

·         Return to Excel by clicking on the "View Microsoft Excel" button on the toolbar (the green "XL" symbol).

 

Test your macro. Now it will return all of the MATH majors.

Editing the BUAD filter macro to make it a filter for ANY major:

·         On the Developer tab, in the Code group, click on the down-arrow on the Macros button.

·         From the menu, click on the Macros button.

·         Click on the name of the macro that you want to modify. In this case, select BUADMajors.

·         Click on the Edit button.

·         Find the BUADMajors macro in the editor.

·         Your macro should look like this.
    Application.Goto Reference:="R2C2"
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$44").AutoFilter Field:=3, Criteria1:=
"BUAD"

·         Replace the "BUAD" (including the quotation marks) with this: InputBox ("Enter Major:")   This will replace BUAD with whatever the user enters at the keyboard when prompted with the text "Enter Major:".The instruction should now read:
    ActiveSheet.Range("$A$2:$F$45").AutoFilter Field:=3, Criteria1:=InputBox("Enter major")

·         Return to Excel by clicking on the "View Microsoft Excel" button on the toolbar (the green "XL" symbol).

 

Test your macro by entering different majors.

Assigning macros to command buttons

To assign a macro to a command button:

·         Click on the Developer tab.

·         In the Controls group, click on the Insert button.

·         Under the Form Controls group, click on the Button icon.

·         The mouse cursor will become crosshairs. Drag the mouse over the place on the worksheet where you want the button to appear.

·         When the Assign Macro dialog box appears, select the macro (in this case, BUADMajors) and click on OK.

·         Click on the button and enter a descriptive label, like Filter by Major. Click off of the button when done.

·         You can format the button by right-clicking on the button and clicking on Format Control.

·         Click somewhere off of the button to remove the handles.

To run a macro:

If you assigned the macro to a shortcut key, press the shortcut key.

If you assigned the macro to a button, click on the button.