VBA assignment question

6. The “Option Explicit” feature must be “ON” in all questions. You can set this in the VBE in: Tools > Options > Editor tab > “Require Variable Declaration” (1 point).
7. Always be sure to include a button or some other GUI element on your spreadsheet that enables the user to call/execute your programs (2 points). Assume that the program is intended for the user who has no idea about VBA, and design GUI accordingly. Test all user inputs thoroughly, the user must not get VBA errors (3 points).
8. Before submitting the resulting files with macros, make sure they work on a lab computer. Also, double-check that all functionality requested in the assignment is realized (1 point / missing feature).
9. Ensure all subroutines that are longer than a few lines, or with complex non-obvious logic, have comments summarizing the purpose and main logic of VBA code (1 point). All VBA code must be indented and spaced to facilitate reading (2 points).
10. All Excel-generated code must be optimized following 80/20 rule (see Class 3 optimization examples). I.e. if unoptimized code is 20 lines, fully optimized is 5 lines, you target should be 20 - (20-5)*0.8 = 8 lines (2 points). Assignment should contain MINIMUM or NO redundant code, for example if you are applying the same formatting to several cells, you should create a separate procedure rather than copying the same code over several times (2 points).

Q1 – 10 points max: build a dynamic “Top X” customers report using arrays; application of bubble sort algorithm; use of in-sheet VBA controls; use of dynamic named ranges; in-sheet data validation.

Use “Marketing campaign support” file for this question.

You are a marketing manager working on a campaign to reward high spending customers. IT downloaded for you a data set from the corporate ERP system that contains POS transactions from company retail outlets. You are looking to get a list of top spenders to email a special offer to, by selecting either “Top X” customers, or those with above “Minimum Amount” spent.

You need to build an Excel app that will provide the following functionality:

“Input” sheet

1. Specify the number of top spending customers – “Top X”
   1. Use sheet cell for this input. Use in-sheet “Data Validation” functionality to make sure the user can only enter a positive integer for the Top X customers.
2. Specify “Minimum Amount”
   1. Use sheet cell for this input. Use in-sheet “Data Validation” functionality to make sure the user can only enter a total sales amount that is between the minimum and maximum values possible, based on the pivot table.
   2. Display min and max allowed amount for the user next to the input cell.
3. Include 2 radio buttons so that the user can select whether we’re using “TopX” or “Minimum Amount” spent search criteria. Use ActiveX controls.

“Pivot” sheet

1. Add a sheet with a pivot table that will display “Sum of Sales” by Customer, based on the data from “Orders” sheet.
2. Use dynamic named range for Pivot table data source.

Logic:

1. Provide the user with a button to run the search on the selected criteria.
2. Clear the results of the previous search on “Input” sheet.
3. Refresh the pivot table.
4. Load dynamic arrays: customer names and sales amounts from the pivot table.
5. Sort the arrays in descending order using bubble sort algorithm (see slide deck for the code). Make sure as you sort the amounts array, you also make corresponding switches in the customer names array.
6. Taking into account the search mode (“Top X” or “Minimum Amount”), output corresponding Customers and Amounts from the sorted arrays into columns F and G of “Input” sheet.

Q2 – 10 points max: automating Solver using VBA; updating a chart using VBA.

Use “Scheduling” file for this question.

You are managing a weekly scheduling process for a chain of fast-food restaurants, and looking to use Excel Solver to find the optimal employee schedule for all locations. All employees work 5-day shifts, and can start on any day of the week. You are given a data set with the average estimated number of employees, by location, by day of the week, that would be sufficient to satisfy the customer demand.

Create a Solver model and necessary VBA code to find an optimal solution for the number of employees starting on each day of the week by location. Record the resulting schedules into the blue highlighted space of the provided Data sheet, along with the total number of employees estimated for each location. Place the solver model on “Model” sheet.

On a separate sheet “Report”, build an automatically updated Pivot table displaying:

• Average total number of employees required, formatted as 00.0 (columns)
• Standard deviation of total number of employees, formatted as 00.0 (columns). Use Pivot Table summary function.
• Results need to be displayed By Province (rows)