File Used in Tutorial:   ExcelMacrosTutorial.xlsm

You work in the Sales Department at a software development company. You have been given a workbook used to track sales of various product lines, and have been asked to create macros that make the workbook easier to use.

Once you open the MS Excel file provided, click on the “Enable Content” in the yellow bar is one is displayed. Save the file with the filename LastName_ExcelMacros.xlsm.  You will notice there is an additional m on the file extension and the file type is a Macro-Enabled Workbook.

Step 1: Enabling the Developer Tab

To work with macros in Excel, you must be able to view the Developer tab within your version of MS Excel. To enable the Developer tab, follow the following set of instructions:

1.    Click on the File tab to open Backstage view and then click the Options button.

2.    In the Excel Options dialog box, click on the Customize Ribbon option and click the Developer check box.

3.    Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.

Step 2: Naming Cells and Ranges

Now that you have the Developer tab available in Excel, we are going to name our cells and ranges for easier access within our macros.

4.    Go to the Consolidated worksheet tab.

5.    This worksheet is current set to protected to prevent editing.  We need to fix this.  Right click on the Consolidated worksheet tab and choose “Unprotect Sheet.”

6.    Select the range C6:E6. You are going to name this range Groupware_Subscriptions. You will type this into the name box in the screenshot below. Hit Enter on your keyboard after typing the name to confirm entry.

7.    Select the range C7:E7. Name this range Groupware_Licenses.

8.    Select the range C8:E8. Name this range Groupware_Other.

9.   

Remove the name Trends fromthe range G5:G21. To do this, go to the Formulas tab at the top. In the Defined Names Group, click on the Name Manager Button.

Select the Trends name at the bottom and then click on Delete.  

10.Edit the defined name ConsultingRevenueTotals (which represents the range C20:E20), so that the name is shortened to Consulting. Select the defined name in the list, and click on Edit at the top of the dialog box. Edit the name and click Ok.

11.Close the Name Manager Dialog Box.

Step 2: Utilizing Defined Names in Formulas

12.The defined name SMail_Licenses refers to the range C12:E12. (You can confirm this by selecting the defined name from the name box drop down – where you named them previously). Select cell F12.  You will notice the current formula is =SUM(C12:E12). Replace C12:E12 with the defined name SMail_Licenses. The formula should then read =SUM(SMail_Licenses). Your results of the formula should be the same.

13.Do the name for SMail_Other, which refers to the range C12:E12. Select cell F13.  Replace C13:E13 with the defined name SMail_Other. Your results of the formula should be the same.

14.Do the name for SMail_Subscriptions, which refers to the range C11:E11. Select cell F11. Replace C11:E11 with the defined name SMail_Subscriptions. Your results of the formula should be the same.

15.In F16:F18, enter formulas using the SUM function that utilize the define names for each of the ranges, like the three previous examples.

1.            In cell F16, use the SUM function to total values in the defined range MathGenius_Subscriptions.

2.            In cell F17, use the SUM function to total the values in the defined range MathGenius_Licenses.

3.            In cell F18, use the SUM function to total the values in the defined range MathGenius_Other.

16.

Your spreadsheet should currently look like the screenshot below:

Step 3: Adding Comments to a Spreadsheets

When working with complex spreadsheets, it can be important to add comments for further information.

17.
Insert the following comment in cell B13: Other sales include individual downloads and box sales. Select the cell B13, go to the Review tab and click on New Comment in the Comments Group.

18.You can also edit current comments. If you look closely at the spreadsheet, you will notice that there is a small red triangle in the top right corner. This signifies that there is a comment. If you move your mouse over the red triangle, the comment will display.

19.Edit the comment in cell B16 by changing 2015 to 2016. The comment should read as follows: Version2 release delayed. Most Version1 subscriptions and licenses expired in 2016; contracts expected to renew in 2017 with Version2 release.

Step 4: Creating Macros in MS Excel

Inserting a Macro

20.
In cell G3, Insert a Macro Button. In the Developer tab, click on the Insert drop down in the Controls group. Click on the Button option (first option under Form Controls). Click in cell G3.

21.

Click on the HideSparklines option.

22.Double click on the button that is inserted and change the label is say “Hide Sparklines.”  Resize the button to approximately match the other macro button.

Running a Macro

Now that we have added the pre-defined macro, we need to run it and see the macro at work. To run this macro, click on the Hide Sparklines button that you just created. You can also run a macro by going to the Developer tab and in the Code Group click on the Macros button. Choose the HideSparklines option and then click on the Run button on the right.  This is the alternative for those macros that do not have a quick access button in the worksheet.   

Recording a Macro with the Developer Tab

You should have noticed that you already had a macro button on the worksheet for “View Sparklines.”  This is not currently an active macro that has been added to the worksheet.  In the following steps, you will use the developer tab to record the process of adding the sparklines back to your spreadsheet.

23.
Make sure the Use Relative References option is not selected in the developer tab.

24.
Click on the Record Macro option above it.

25.Name the macro: ViewSparklines stored in the current workbook with the description Displays sparkline charts for each row of product revenues and the keyboard shortcut ctrl + t.

a.    With the macro recording, perform the following excel functions for adding sparklines to the worksheet:
Select the range G6:G22.

b.    On the ribbon, click the INSERT tab.

c.    In the Sparklines group, click the Column button.

d.    In the Create Sparklines dialog box, enter the range C6:E22 in the Data Range box. (Hint: The Location Range box should already contain the range G6:G22.)

e.    Click the OK button.

26.Click on the Stop Recording button.

27.Assign the ViewSparklines macro to the View Sparklines macro button in the worksheet. Right click on the button and choose Assign Macros. Choose the ViewSparklines macro that you just created. Click OK to close the Assign Macro dialog box. Both of the buttons should now be active. Confirm that both buttons are operating as they should.

28.Save your workbook.

Editing VB Macro Code

29.Go to the Sales Data Entry Form worksheet tab.

30.Select the range C5:C11. Right click on the range and go to Format Cells. Go to the Protection tab and uncheck the box next to Locked.

31.
Select the range B5:C10. Create defined names for the range using the Create from Selection option.  Go to the Formulas tab and click on the Create from selection button in the Defined Names group.

The names will be in the left column.

32.

Assign the Clear macro to the Clear button in the worksheet.  Prior to confirming your choice, click on the Edit option in the Assign Macros dialog box.

33.In the VB Macro code, edit the range used in the code to the range C5:C10. The line of code should read: Range(“C5:C10”).Select

Note: Be sure that you are changing the range in the Sub Clear() portion of the code at the bottom and not one of the other methods.

34.Close and return back to the excel workbook.

35.Click on the Clear button and confirm that your macro clears cell C10. (You might have to right click and go back into Assign Macros Dialog Box to assign the macro to the button after editing).

Understanding the Structure of a Macro

The VBA code in the code window lists all of the actions you performed when recording your macro. Within your workbook, go back to the Assign Macros dialog box (can right click on the View Sparklines button). Within the dialog box, select the ViewSparklines macro and then click on Edit on the right. You will have a window in the forefront that contains all of the code from your recording.  If you click on the window in the background (Module1), then you will see other code that has been used for hiding the sparklines and clearing text.

The code in Module 1, shown here to the right, is used for hiding sparklines and clearing text. In the Sub HideSparklings() area of the code, it is selecting the range G6:G22.  It then tells excel to clear those sparklines. The Sub Clear() code is used in the future section of this tutorial to clear text from a range of cells. It selects the range C5:C10 and then clears the text in that range.

The code for Module 2, shown to the left, is a little more complex. This is the code that was created when recording your macro that added the sparklines to your excel spreadsheet.  It first selects the range G6:G22.  It then adds the sparklines to the range and formats them.

This set of code gives you a glimpse into the process of coding macros within Microsoft Excel. Close and return back to the Microsoft Excel.

Step 5: Creating a Validation Rule

1.   
Click inside of cell C5 and add a data validation. To add a data validation to a cell go to the Data tab and click on the Data Validation drop down in the Data Tools Group.

The data validation should allow options from a list with the following options (screen shots follow):

a.    The validation rule should ignore blanks and appear as an in-cell dropdown.

b.    For the data validation source, enter the following list: Spring Groupware, Spring Mail, Spring Math Genius, Spring Consulting.

c.    The input message should have the title Product Name and the Input message Click the arrow to select a product.

d.    The error alert should have the Stop style with the title Invalid Product and the error message An invalid product has been entered.

2.    Click in cell C8 and add a data validation for the following options:

a.    The dates should be greater than or equal to 1/1/2015.

b.    The input message should have the title Date of Sale and the Input message Enter Date of Sale.

c.    The error alert should have the stop style with the title Invalid Date of Sale and the error message Date of Sale must be after 1/1/2015.

3.    Enter the following data for a customer order:

a.    In cell C5, select Spring Mail as the Product Name.

b.    In cell C6, enter Springfield Elementary as the Customer Name.

c.    In cell C7, enter Subscription as the Contract Type.

d.    In cell C8, enter the date 7/26/2012 as the Date of Sale. When the error message appears, click cancel and enter the valid date of sale 3/26/2015

e.    In cell C9, enter the value $50,000 as the Total Sale amount.

f.     Do not enter a value in cell C10.

4.    Protect the current worksheet contents. Do not use a password.

5.    Save and submit this tutorial.