Excel Macros 1

In this lesson we are going to create a very simple macro, create a button to run the macro, test the macro, and then modify it.

 

Related lessons:  http://bus91l.altervista.org/Assignments/Macros2.htm

http://bus91l.altervista.org/Assignments/Macros3.htm

 

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Create a macro.

Replace formulas with their values using Paste Special.

Create a button to run a macro.

Use the macro icon to “Run” a macro

Edit a macro.

 

Videos: 

Create a Macro     Time = 1:00

https://www.youtube.com/watch?v=WB6hMh8SxxY

Create a Macro Button Form Control     Time = 0:57

https://www.youtube.com/watch?v=pPwabtDFufY

Macro help for the MAC

https://www.youtube.com/watch?v=nvWpFdo7EO0

https://www.youtube.com/watch?v=a2bTaFEhE0A&spfreload=10

Visual Basic Editor Window -  2011 tutorial

Debugging Macros – 2011 tutorial

 

What are Macros? How are they used?

A macro is a computer program that performs a series of operations in a worksheet. Usually you create a macro by recording changes you make to a worksheet. As you are recording your changes, Excel automatically converts your mouse clicks and keystrokes into Visual Basic computer code. Later on, you can play back the macro to repeat the same set of changes.

Here’s one example of how you could use a macro. Suppose you have a report that is printed every day, based on data in an Excel worksheet.  The first time you print the report you would have to select a range of cells to print and send the printing commands to the printer. If you recorded those steps you could reprint that report any time you wanted to, just by pressing an icon.

Here’s another example. Suppose you have a reusable form, like the cashier report we created in the Excel Basics assignment.  After a form has been used for the first time you need to delete the data so the form can be used again. This may require selecting several different cells and deleting data from each one. If you record the procedure as you delete the data from the form, you can use that macro every other time you want to reset the form.

 

Some students using MacBooks have been unable to record macros. Here is what I found when searching the web for a solution.

Whenever I try to record a macro in Excel for Mac in order to view the code and try to customize it, there is no code to see. All I see is:

Sub MacroN()

'

'

'

End Macro()

Why can't I view the code so I can start to make adjustments to my macros?

I have seen the same problem. Try saving the workbook as a macro-enabled workbook before creating any macros. Then close the now macro-enabled workbook and then re-open it. This solved my problem. Good luck.

 

Create A Macro to Simulate the Rolling of Dice.

1.       Open a new workbook in Excel. If you are working in Excel 2013 add two more worksheets so you are seeing three of them.

The default setting blocks macros from running. We can turn them on if they are turned off.

2.       Click on the File tab of the ribbon.  Click on Options.  Click on Trust Center.  Click on Trust Center Settings.   Click on Macro Settings.  Click on the button next to “Enable all macros.” Click on Ok. For Excel 2013 click on Ok twice.

For the MAC: The steps in Excel 2011 are to go into preferences then to security and then UNCHECK the box that says “warn before opening a file that contains macros.” In Excel 2016 it may require going into the Developer Tab to change the settings there.. For more info use this link.

https://support.office.com/en-us/article/Create-or-delete-macro-in-Excel-2016-for-Mac-5bd3dfb9-39d7-496a-a812-1b5e8e81d96a

3.       Click the pull-down arrow below the macro icon [ALT  W  M] on the View tab. Choose "Record Macro."

For the MAC: From the Tools menu Select Macro and then select “Record New Macro.”

A picture of the Record MAcro dialog box.

 

4.       Name the Macro “RollDice” (Macro names won’t work if they include spaces. Do not include the quotation marks.)

Describe the macro: “Simulates the roll of a pair of dice.” (Do not include the quotation marks.) Click OK. In Excel 2013 you should see a small rectangle in the lower left of the worksheet, next to the word Ready. This tells you a macro is being recorded.

5.       Click on the Sheet1 worksheet tab to select it. (Do this step even if you are already working in Sheet1.)

6.       Click on cell A20 to select it.

7.       Enter the function =ROUNDUP(RAND()*6,0) into cell A20

The RAND function will generate a random number between 0 and 1 in cell A20. Multiplying times six and rounding up will convert the random numbers into whole numbers between 1 and 6.

8.       Copy and paste the function into cell A21.

This will create a second random number between 1 and 6 in cell A21. A new random number in cell A20 will also be generated.

Paste Special

Now we want to remove the random number functions and replace them with the whole numbers we just created. We can use the Paste Special-Values command to do this. It replaces functions and formulas with their numerical values.

9.       Select the range A20:A21. Right click and choose Copy. Right click again and click on Paste Special. Select values and Click OK. Press the Enter key to clear the clipboard and finish the pasting operation.

You should now have whole numbers in cells A20 and A21.

10.   Stop the recording by either clicking on the stop recording icon or by clicking the pull-down arrow below the macro icon on the View tab [ALT  W  M] and choosing "Stop Recording." For the MAC try clicking on the tools menu and clicking on the Macros option. You should be able to stop the recording this way.

The start and stop recording macro icon is a small square in the lower left corner, just below the worksheet, next to the word Ready.

 

If the icon looks like this nothing is being recorded. Pressing the icon will start recording a macro.                

If the icon looks like this a macro is being recorded. Pressing the icon will stop the recording.                       

Note: In Office 2013 this icon may be hidden. To display it, right-click on the green status bar, next to the word Ready, and select it from the pull-down list. 

Create a Button on the Sheet1 Worksheet to Run the Macro

11.   Position the cursor in cell C20 of the Sheet1 worksheet.

12.   For Excel 2010 Click on the textbox icon [ALT  N  X] on the Insert section of the ribbon. Click and drag over cells C20:D21 to create the textbox. Release the mouse button.

For Excel 2013 click on the Shapes icon [ALT  N  S  H] and select a rectangular shape. Click and drag over cells C20:D21 to create the rectangle. Release the mouse button.

13.   Inside the textbox type the word “Roll the Dice” to add a label describing the macro. (Do not include the quotation marks.)  In Excel 2013 right click on the shape, select Edit Text. Type the words Roll the Dice. Click anywhere outside of the shape.

14.   Right-click on the textbox and choose "Assign Macro." The Assign Macro dialog box should open. Select the RollDice macro. Click OK. Click anywhere outside of the shape.

15.   Test the RollDice button by clicking on it. You should see a different pair of numbers in A20 and A21. Run the macro a few times to see if it works.  You should get a new pair of numbers every time you run the macro. 

Macros can also be run with the macro icon.

16.   Click on the pull-down arrow below the Macro icon and select View macro. [ALT F8] or [ALT W M V].  For the MAC select the option on the Tools Menu and select Macros.

17.   Select the RollDice macro and then click on Run.

You can run macros from a validation list! If you would like to learn how to do it, this Youtube video teaches you how. Running macros from a validation list

 

Editing Macros

You can change a macro, or create a new one, by editing the macro code. Here are two very short tutorials to help you understand the features in the Editor window.  Read through one.

Visual Basic Editor Window -  2013 tutorial

Visual Basic Editor Window -  2011 tutorial

Here is help on how to use the Editor window to fix a broken macro. Read through one.

Debugging Macros – 2011 tutorial           

Debugging Macros – 2013 tutorial

 

18.   Click on the pull-down arrow below the Macro icon and select View Macro. [ALT W M V].

19.   Select the RollDice macro and then click on Edit. The Editor window should open showing the Visual Basic (VB) code.

Here is our macro program written in Visual Basic (VB) code:

Sub RollDice()

'

' RollDice Macro

' simulates the roll of a pair of dice.

'

    Sheets("Sheet1").Select   

NOTE: This line might be missing from your code, if you skipped step 5 above. It appears in my macro because I started by clicking on the Sheet1 tab to select it before I clicked on cell A20.  With this line of code included Excel will jump into Sheet1 before it runs the rest of the macro.  Without it your macro might run in the wrong worksheet.

    Range("A20").Select

    ActiveCell.FormulaR1C1 = "=ROUNDUP(RAND()*6,0)"

    Range("A20").Select

    Selection.Copy

    Range("A21").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("A20:A21").Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

 

Every macro starts with the word “Sub” followed by the name of the macro. It ends with the words “End Sub.” The lines in green that start with an apostrophe  are comments and do not perform any computer code. The actual computer commands are indented. Can you match up what you did in steps 6 to 11 with the VB code?

·         Name the Macro “RollDice

·         Describe the macro: “Simulates the roll of a pair of dice.”

·         Click on the Sheet1 worksheet tab. 

·         Click on cell A20.

·         Enter the function =ROUNDUP(RAND()*6,0) into cell A20.

·         Copy the function in cell A20.

·         Paste the function into cell A21.

·         Press the Enter key to clear the clipboard and finish the pasting operation.

·         Select cells A20:A21.

·         Right click and choose Copy.

·         Right click again and click on Paste Special. Select values.

·         Click OK to paste the values.

·         Press the Enter key to clear the clipboard and finish the pasting operation.

Why did we start the macro by clicking on the Sheet1 worksheet tab? By including that step, the macro will always run in the right worksheet, Sheet1, no matter which sheet our cursor is located in when we run the macro.  Here’s the code: Sheets("Sheet1").Select   

Why does it say sheets and then sheet1 and then dot select?

Sheets is the name of a special type of an “object.” Objects are like computer-code templates.   When you want to create another one of those objects, you simply give it a name. After that you can customize its characteristics.

The instruction is telling Excel to look in the list of Sheet-type objects that have been created and find the one with the name Sheet1. The last part, the dot Select, tells Excel what we want to do with that particular sheet.

Task: Change the macro so it moves the location of the random numbers into two different cells, ten rows further down in the worksheet.  If you are editing a macro you can copy and paste text from one part of the macro to another, or you can type directly into the macro code. 

WARNING: Macros must be written in “text only” format. The text formatting used in Word, and in HTML code, contains hidden characters that will cause the macro to freeze.

20.   If you don’t see this line of code Sheets("Sheet1").Select above the instruction Range("A20").Select, Type it in now. 

Click on the macro code. Replace all of the number 2’s in the cell references with 3’s. A20 becomes A30 and so on. You will be making five or six changes.  

That’s all there is to it. Close the editing window by clicking in the upper right hand corner. Your changes are automatically saved.

Sub RollDice()

'

' RollDice Macro

' This macro simulates the rolling of a pair of dice.

'

    Sheets("Sheet1").Select

    Range("A30").Select

    ActiveCell.FormulaR1C1 = "=ROUNDUP(RAND()*6,0)"

    Range("A30").Select

    Selection.Copy

    Range("A31").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Range("A30:A31").Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

21.   Click on the “Roll the Dice” button. Your new pair of dice should show up in cells A30 and A31.

What happens if we rename Sheet1 to something else? We know that formulas will fix themselves and use the new worksheet name in the formula. Macros do not work this way. If you change a worksheet name the macro will not work. You must edit the worksheet name in the VB code to make it work again.

Note: For the rest of this lesson you need to have three worksheets available to use. Look at the worksheet names at the bottom of the spreadsheet. You should see Sheet1, Sheet2, and Sheet3. If you are using Excel 2013 you might need to insert two more worksheets. One of these new worksheets should be named Sheet2, the other should be named Sheet3. Do that now.

22.   Click on the pull-down arrow below the Macro icon and select View macro [ALT W M V].

23.   Select the RollDice macro and then click on Edit. A new window should open showing the Visual Basic code.

24.   Click on the macro code. Inside the code you should see “Sheet1” Edit the code to change Sheet1 to Sheet2. Close the editing window. Do not change any of the worksheet tab names in the worksheet itself, only make the change in the macro code.

Note if you don’t see the this code Sheets("Sheet1").Select  then  you skipped step 5 and step 20. You can type Sheets("Sheet2").Select  as the first line in the code in the editing window. 

Make sure you have three worksheets in the workbook, with one of them labeled Sheet2.

25.   Run the macro. You should now be seeing random numbers in cells A30 and A31 in Sheet2.Note: macros often fail for unclear reasons. This is not a class in fixing macros that don’t work. If your macro fails, just delete it and re-record it. If it continues to fail, talk to your instructor.

What if we need two macros to perform similar tasks? Rather than create a second macro from scratch, we can simply make a copy of the VB macro code and edit it.

26.   Click on the pull-down arrow below the Macro icon and select View Macro  [ALT W M V].

27.   Select the RollDice macro and then click on Edit. A new window should open showing the Visual Basic code.

Copy the entire RollDice code, starting with Sub RollDice() and ending with EndSub.

Paste the text back into the editing window, below the word EndSub.

Edit the name of the first line of the pasted text to say Sub RollDiceHere(). Now we have a new macro. Don’t close the editing window yet.

28.   Delete the instruction Sheets("Sheet2").Select in the new macro RollDiceHere(). Now our new macro will work in any worksheet that is selected when you run the macro, and won’t automatically jump into Sheet2. Don’t close the editing window yet.

Note that we used the RAND function in our macro and then had to manipulate the results to create whole numbers between one and six.  The RANDBETWEEN function lets us generate a list of whole numbers directly, so we can skip the extra steps.  For example if we wanted to generate a list of 52 numbers (to simulate a deck of cards) we could write =RANDBETWEEN(1,52).

29.   In the RollDiceHere macro, Edit the line ActiveCell.FormulaR1C1 = "=ROUNDUP(RAND()*6,0)" to say  ActiveCell.FormulaR1C1 = ”=RANDBETWEEN(1,6)”  Note that there is a space both before and after the first equal sign. Close the editing window.

30.    Before you test any macro it is always a good idea to save your file. If the macro ruins your spreadsheet you can’t undo it, so you want to have a back up. Press the F12 function key to save the file. Select the file type “macros enabled workbook” Name the file MacrosLesson.  If you save the file as an Excel Workbook. You will see this error message and your macros will be disabled.

 

31.   Click on NO to exit this message. In the “save file” dialog box change the ”Save as type:” selection to Excel-Macros Enabled Worksheet. Save the file.

32.   Click in any cell in Sheet3. Click on the pull-down arrow below the Macro icon and select View Macro  [ALT W M V].Select the RollDiceHere macro and then click on Run. You should see random numbers between 1 and 6 in cells A30 and A31.

 

Project: Creating dice images.

 

 

In Sheet3 we want to change the row height and the column width so the cells in the range D2 to J4 form squares. Note, you can’t resize individual cells or a small range of cells, you can only change the sizes of columns and rows.

33.   Click and drag to select the letters D through J at the top of the columns and then right click. Set the column width to 5. The Mac uses a different unit of measurement, so 5 won’t work.  You want to create a block of cells that are the same height and width (squares) Experiment with clicking and dragging to change the column widths and row heights until you get something that looks close. 

Click and drag to select the row numbers 2 through 4 on the left of the worksheet and then right click. Set the row height to 30.

Set the horizontal cell alignment to center. Set the font size to 30.

34.   Enter the number 3 into cell B6 and the number 6 into cell B7. In cells D2 type the following function: = IF ( $B$6 > 1," l ", " ")  Copy the formula into cells D3, D4, and F4.

Note: The text inside quotes in all of the formulas in steps 34 to 37 will be the lowercase letter L, not the number 1.

35.   Edit the formula in cell D3 to change it to  = IF ($B$6  = 6 , " l " , " " )

Copy the formula into cell F3

36.   Edit the formula in cell D4 to change it to  = IF ($B$6  >3," l " , " ")

Copy the formula into cell F2

37.   In cell E3 type the following function: = IF ( MOD ($B$6,2) = 1 , " l " , " ")

 

The MOD function divides the contents of a cell by a constant and returns the remainder. In this case, when we divide the contents of cell B6 by two, and the remainder is one, we know Cell B6 contains an odd number. If the remainder is zero, we know cell B6 contains an even number.

 

If you see a small triangle in the E3 cell it’s because you have just entered a formula that is inconsistent with the formulas surrounding it. We don’t want to show that triangle. To turn it off go to the File tab, find the Excel Options button (In Excel 2013 find Options) and click on it. In the dialog box select Formulas from the menu and find the error checking rules. Uncheck the box next to “Formulas inconsistent with other formulas in the region.” Click OK.

38.   In the range D2:F4 add a thick box border. [ALT H B]

Center the text vertically and horizontally. [ALT  H  A  M] and [ALT  H A  C].

Select the font icon [ALT H F F] and change it to Wingdings. This will change the letter l into a dot. Wingding font for the MAC doesn’t show a dot, but it’s close enough for our purposes.

Note, you can type the name of a font into the font type box, you don’t have to scroll through all of the font choices. Typing Win should be enough to call up the Wingdings font and then you just press Enter.

39.    Enter the numbers 1 through 6 in cell B6 to test that the dots are all showing correctly for each value. If they are not, go back and correct the formulas you just entered. You should have formulas in seven cells.  If you are seeing hands instead of dots, you used an I (eye) instead of an l (el) in the second argument of your formulas. If you are seeing vertical bars, you used the wrong font. Change it to wingdings.

40.   Copy and paste the contents of the D2:F4 range into the range H2:J4

41.   In the range D2 to F4 set the cell background color [ALT  H  H] to light blue.

42.   In the range H2 to J4 set the cell background color [ALT  H   H]  to light yellow.

Use this shortcut to edit all the formulas in the range H2:J4 to replace $B$6 with $B$7.

43.   Mouse over the range H2:J4. This step is important, we only want to make changes inside the range of selected cells.

44.   Press Ctrl + H to open the Find and Replace dialog box. In a MAC try Command + H

45.   In Find what type in $6. In Replace with type $7. Click on replace all. Click on Close.

 

46.   In cells B6 enter the function =RANDBETWEEN(1,6).

47.   In cell B7 enter the function =RANDBETWEEN(1,6)  Note: You have to type this in, not copy and paste it from cell B6. If you copy and paste both random numbers will be identical.

48.   In cell B8 enter the formula = B6+B7

49.   In cell A6 type Die 1:

50.   In cell A7 type Die 2:

51.   In cell A8 type Total:

52.   In the range A6:B8, center the cell contents. Change the font color to white. Change the fill color to black. Increase the font size to 16. (Keyboard shortcuts are shown in step 39)

53.   Test your work by pressing the F9 key a few times.

 

 

 

Instead of pressing the F9 key to roll the dice, we can create a macro to do it.

 

 

 

54.   On the Formulas tab, click on the down arrow under the Calculations Options icon. [ALT  M  X] The default setting is Automatic. Change it to Manual. For a MAC click on the Excel menu, Select Preferences, select Formulas & Lists, select Calculations, and chance the setting to Manual.

55.   Move your cursor into cell A1 of the Sheet3 worksheet.

56.   On the View tab, click on the Macros icon and choose Record Macro.

57.   Name the macro as Recalculate. In the description box, type: Recalculates the active worksheet.

58.   Click Ok to start recording.

59.   I the Formulas tab, click on the Calculate Now icon.  In a MAC this icon is called Recalculate Now.

60.   Click on the square icon in the lower left of your system tray to stop recording.

61.   To check your macro, click on the Macros icon and choose View Macro. Select the Recalculate Macro and select Edit. Your code should look like this:

 

 

 

62.   If your code does not look like this, edit it so it matches.

63.   Close the window that shows the code.

* Note: You could have skipped all of the steps required to record this macro and created it simply by editing the text in the View macros window. How would you do that? Click on the Macro Icon, select View Macro, select Edit, type in the macro exactly as shown, and close the Edit window.

64.   From the Insert tab, choose Shape, choose Rectangle Shape, and draw the shape over the A2:B3 range

65.   Right click on the shape. Select Assign Macro. Click on the Recalculate Macro. Click on OK. Click anywhere outside of the shape to deselect it.

66.   Select the shape, right click and choose Edit Text or add text. Type     Roll the Dice.

67.   Click on the Roll the Dice button to test it.

68.   Save your file as an Excel Macro-Enabled WorkBook named MacrosLesson.

 

If you would like to learn more about macros, I have a follow up lesson here:

http://bus91l.altervista.org/Assignments/Macros2.htm

 

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number.

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel 2010 Macros

Y/N Create a macro.

Y/N Replace formulas with their values using Paste Special.

Y/N Create a button to run a macro.

Y/N Use the macro icon to “Run” a macro

Y/N Edit a macro.