Working with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsFile Used in Tutorial:ExcelMacrosTutorial.xlsmYou work in the Sales Department at a software development company. You have been given aworkbook used to track sales of various product lines, and have been asked to create macrosthat make the workbook easier to use.Once you open the MS Excel file provided, click on the “Enable Contentâ€_x009d_ in the yellow bar is oneis displayed. Save the file with the filename LastName_ExcelMacros.xlsm. You will notice thereis an additional m on the file extension and the file type is a Macro-Enabled Workbook.Step 1: Enabling the Developer TabTo work with macros in Excel, you must be able to view the Developer tab within your version ofMS 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 theDeveloper check box.3. Click the OK button to close the Excel Options dialog box and confirm the Developertab appears in the Excel Ribbon.1|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsStep 2: Naming Cells and RangesNow that you have the Developer tab available in Excel, we are going toname 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 preventediting. We need to fix this. Right click on theConsolidated worksheet tab and choose “UnprotectSheet.â€_x009d_6. Select the range C6:E6. You are going to name this rangeGroupware_Subscriptions. You will type this into thename box in the screenshot below. Hit Enter on your keyboard after typing the nameto confirm entry.7.Selecttherange C7:E7. Name this range Groupware_Licenses.8. Select the range C8:E8. Name this range Groupware_Other.9. Remove the name Trends from the range G5:G21. To do this, go to the Formulas tabat the top. In the Defined Names Group, click on the Name Manager Button.Select the Trends name at thebottom and then click onDelete.2|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information Systems10. Edit the defined name ConsultingRevenueTotals (which represents the rangeC20:E20), so that the name is shortened to Consulting. Select the defined name inthe 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 Formulas12. The defined name SMail_Licenses refers to the range C12:E12. (You can confirm thisby selecting the defined name from the name box drop down – where you namedthem previously). Select cell F12. You will notice the current formula is=SUM(C12:E12). Replace C12:E12 with the defined name SMail_Licenses. Theformula should then read =SUM(SMail_Licenses). Your results of the formula shouldbe 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 formulashould be the same.14. Do the name for SMail_Subscriptions, which refers to the range C11:E11. Selectcell F11. Replace C11:E11 with the defined name SMail_Subscriptions. Your resultsof the formula should be the same.15. In F16:F18, enter formulas using the SUM function that utilize the define names foreach of the ranges, like the three previous examples.1.In cell F16, use the SUM function to total values in the defined rangeMathGenius_Subscriptions.2.In cell F17, use the SUM function to total the values in the defined rangeMathGenius_Licenses.3.In cell F18, use the SUM function to total the values in the defined rangeMathGenius_Other.3|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information Systems16. Your spreadsheet should currently look like the screenshot below:Step 3: Adding Comments to a SpreadsheetsWhen 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 individualdownloads and box sales. Select the cell B13, go to the Review tab and click onNew Comment in the Comments Group.18. You can also edit current comments. If you look closely at the spreadsheet, you willnotice that there is a small red triangle in the top right corner. This signifies thatthere is a comment. If you move your mouse over the red triangle, the comment willdisplay.19. Edit the comment in cell B16 by changing 2015 to 2016. The comment should readas follows: Version2 release delayed. Most Version1 subscriptions and licensesexpired in 2016; contracts expected to renew in 2017 with Version2 release.4|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsStep 4: Creating Macros in MS ExcelInserting a Macro20. In cell G3, Insert a Macro Button. In the Developer tab, click on the Insert drop downin 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 “HideSparklines.â€_x009d_ Resize the button to approximately match the other macro button.5|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsRunning a MacroNow that we have added the pre-defined macro, we need to run itand see the macro at work. To run this macro, click on the HideSparklines button that you just created. You can also run a macroby going to the Developer tab and in the Code Group click on theMacros button. Choose the HideSparklines option and then clickon the Run button on the right. This is the alternative for thosemacros that do not have a quick access button in the worksheet.Recording a Macro with the Developer TabYou should have noticed that you already had a macro button on the worksheet for “View Sparklines.â€_x009d_This is not currently an active macro that has been added to the worksheet. In the following steps, youwill 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 storedin the current workbook with the descriptionDisplays sparkline charts for each rowof product revenues and the keyboardshortcut ctrl + t.a. With the macro recording, perform the following excel functions for addingsparklines 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 Sparklinesdialog box, enter therange C6:E22 in the DataRange box. (Hint: TheLocation Range box shouldalready contain the rangeG6:G22.)e. Click the OK button.26. Click on the Stop Recording button.6|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information Systems27. Assign the ViewSparklines macro to the View Sparklinesmacro button in the worksheet. Right click on the buttonand choose Assign Macros. Choose the ViewSparklinesmacro that you just created. Click OK to close the AssignMacro dialog box. Both of the buttons should now beactive. Confirm that both buttons are operating as theyshould.28. Save your workbook.Editing VB Macro Code29. Go to the Sales Data Entry Form worksheet tab.30. Select the range C5:C11. Right click onthe range and go to Format Cells. Goto the Protection tab and uncheckthe box next to Locked.31. Select the range B5:C10. Createdefined names for the rangeusing the Create from Selectionoption. Go to the Formulas taband click on the Create fromselection button in the DefinedNames group.The names will be in the left column.7|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information Systems32. Assign the Clear macro to the Clear button in the worksheet. Prior to confirming yourchoice, 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 lineof code should read: Range(“C5:C10”).SelectNote: Be sure that you are changing the range in the Sub Clear() portion of the codeat the bottom and not one of the other methods.34.se and return back to the excelworkbook.Clo35. Click on the Clear button and confirmthat your macro clears cell C10. (Youmight have to right click and go backinto Assign Macros Dialog Box toassign the macro to the button afterediting).8|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsUnderstanding the Structure of a MacroThe 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 Sparklinesbutton). Within the dialog box, select the ViewSparklines macro and then click on Edit on the right. Youwill have a window in the forefront that contains all of the code from your recording. If you click on thewindow in the background (Module1), then you will see other code that has been used for hiding thesparklines and clearing text.The code in Module 1, shown here tothe right, is used for hidingsparklines and clearing text. In theSub HideSparklings() area of thecode, it is selecting the rangeG6:G22. It then tells excel to clearthose sparklines. The Sub Clear()code is used in the future section ofthis tutorial to clear text from arange of cells. It selects the rangeC5:C10 and then clears the text inthat range.The code for Module 2, shown tothe left, is a little more complex.This is the code that was createdwhen recording your macro thatadded the sparklines to yourexcel spreadsheet. It first selectsthe range G6:G22. It then addsthe sparklines to the range andformats them.This set of code gives you a glimpse into the process of coding macros within Microsoft Excel. Close andreturn back to the Microsoft Excel.9|Pa geWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information SystemsStep 5: Creating a Validation Rule1. Click inside of cell C5 and add a data validation. To add a data validation to a cell goto 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 (screenshots 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 Inputmessage Click the arrow to select a product.d. The error alert should have the Stop style with the title Invalid Product andthe error message An invalid product has been entered.10 | P a g eWorking with VB Macros in MS Excel TutorialISYS 2263 – Principles of Information Systems2. 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 messageEnter Date of Sale.c. The error alert should have the stop style with the title Invalid Date of Saleand 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 errormessage appears, click cancel and enter the valid date of sale 3/26/2015e. 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.