ISYS 2263 - Principles of Information Systems-

Working with VB Macros in MS Excel Tutorial

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. 1|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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 from the 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. 2|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 3|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 4|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 5|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 6|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 7|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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.
se and return back to the excel
workbook. C l o 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). 8|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 9|Pa ge Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems
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. 10 | P a g e Working with VB Macros in MS Excel Tutorial
ISYS 2263 - Principles of Information Systems 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. 11 | P a g e