Excel VBA Programming Language

Instructions

You will design the following macros. Make sure you attach each of the following macros to a GUI object (a button, an image, a shape, etc.). No matter what you do, make sure it is clear and easy for users to use. Points will be taken off if I have to figure out what a GUI object does. Remember user-friendliness is a key success factor of any software design.

AskToExit macro: This macro asks the user if he/she wishes to close Excel. If YES, save all the work for the user (ActiveWorkbook.save) first and close Excel.

CreateChart macro: This time we are going to try macro recording. Follow the steps below carefully. 1. Start recording macro (Developer | Code | Record Macro) 2. Name your macro CreateChart 3. Highlight the dataset (A1:C21) 4. Create a cluster column chart similar to the following screenshot in a new worksheet. 5. Stop recording your macro (Developer | Stop Macro). 6. If you go back to your VBA editor, you will find that the macro is recorded in Module 2. Double-click it to see whats inside. See the screenshot below for an example. Next, visit this web site to see how you can expand this CreateChart macro with the following requirements (By adding the following three items, you will get 10 points extra credit for this assignment): Chart title: Sales by Season Title of X-axis: Year-Season Title of Y-axis: Sales 7. If you are interested in creating different types of charts, just add the ChartType property of the ActiveChart and set it to your desired chart type. See a list of options here. (Do not change the chart type on the version of the file that you submit to the dropbox)

CountSeasonsBelowKPTTarget macro: This macro counts the total number of seasons with sales smaller than the KPI Target set in G1. Your macro should still work correctly when the KPI target changes. Your macro should also work correctly if the dataset is expanded. (Hint: you will define the dataset dynamically in your macro. See class discussion for more.) Consider using an Excel function in your macro to perform the counting. The result should be displayed in cell G2.

LowestSales macro: This macro identifies the year and season with the lowest sales and shows the result in cell G3. Do not assume that the lowest is always at a certain location. Your macro should still work if I change the numbers in column C. Your macro should still work if more data are added to the end of the 3 dataset. In other words, do not hard code the dataset. The result should be in this format: year-season. For example, 2013-Fall. Remember that there could be multiple seasons that are identified as the lowest (e.g., 2013-Fall, 2015-Spring, and 2016-Spring). Once the lowest sales figure is identified, change the background color of the whole row. For example, 2013-Fall is the first lowest value, you will change the background color of A8:C8. 1. Consider using a loop for this purpose. 2. I dont mind which form of loops that you use, but I strongly encourage that you try all different types of loops that we have discussed in class.

A1 fic Year C D E F G 1 60 Sales 52 2 KPI Target: Number of seasons not meeting the target: Year and Season with the lowest sales: 3 76 4 81 5 62 09 6 32 7 62 00 8 31 9 89 10 78 A B Year Season 2012 Spring 2012 Summer 2012 Fall 2012 Winter 2013 Spring 2013 Summer 2013 Fall 2013 Winter 2014 Spring 2014 Summer 2014 Fall 2014 Winter 2015 Spring 2015 Summer 2015 Fall 2015 Winter 2016 Spring 2016 Summer 2016 Fall 2016 Winter 11 63 12 104 13 76 14 31 15 96 62 16 17 18 81 31 19 34 20 62 21 56 A1 fic Year C D E F G 1 60 Sales 52 2 KPI Target: Number of seasons not meeting the target: Year and Season with the lowest sales: 3 76 4 81 5 62 09 6 32 7 62 00 8 31 9 89 10 78 A B Year Season 2012 Spring 2012 Summer 2012 Fall 2012 Winter 2013 Spring 2013 Summer 2013 Fall 2013 Winter 2014 Spring 2014 Summer 2014 Fall 2014 Winter 2015 Spring 2015 Summer 2015 Fall 2015 Winter 2016 Spring 2016 Summer 2016 Fall 2016 Winter 11 63 12 104 13 76 14 31 15 96 62 16 17 18 81 31 19 34 20 62 21 56