COMPSCI 2034b / DIGIHUM 2144b Data Analytics: Principles and Tools
Assignment #2
Blockbuster VBA Practice
Posted: Due: Total:
February 5th 2018 February 19th 2018 11:55PM 100 Points (5% of Final Grade)
CS2034 – Data Analytics: Principles and Tools Assignment #2
Learning Outcomes
By completing this assignment, you will gain and demonstrate skills relating to: • Creating VBA functions.
• Using compound structures in VBA.
• Calling and reusing functions.
• Developing algorithms and writing code. Instructions
In this assignment, you will download an Excel file from OWL named Assign2.xlsm. You will then create the required functions and subroutines in VBA to make the formulas in this Excel file work correctly.
You are required to follow each step in this assignment and submit an Excel .xlsm copy of your assignment. You must assume that the data in your sheet can change (i.e. you may not hardcode your answers). Each step must be followed precisely including the file naming convention given in the Submission Section. It is expected that you will document your code using comments in su cient detail that the purpose and function of each line is clear to the TA marking your assignment.
You will be assessed on the following:
• Using the correct Assign2.xlsm file from OWL.
• Completion of each task correctly.
• Coding each function as described.
• Using the given function headers without modification. • Commenting your code in su cient detail.
• Assignment submission via OWL. Problem Description
Let’s imagine a world in which Blockbuster still exists. As an employee of London’s local Blockbuster, you have been tasked with analyzing data as it relates to customer’s rental habits. Your boss is trying to purchase new DVDs and wants to know the following:
• How many movies exist in the movie database for each genre. • The total number of movies borrowed for each genre.
• The genre with the largest number of movies.
• The genre with the largest number of total rentals.
• The movie with the largest number of rentals.
• The most expensive movie to make.
• The movie with the highest profit percent.
• The movie with the highest profit for a particular genre.
1 of 4
CS2034 – Data Analytics: Principles and Tools Assignment #2
To do this, you will create a series of VBA functions which will be described in detail. For this assignment you are not allowed to use any of the in-built EXCEL/VBA functions that analyze data such as MAX, SORT, or MATCH. However, you can use LBound, UBound, and Debug.Print. You can also use methods for Range to access the properties such as Cells, Value, Count, Row, and Column.
Download the Assign2.xlsx file from OWL. Save as a macro-enabled file (xlsm) to complete the tasks outlined. This file has sample data and shows you the structure of the file that should be handed in. Keep in mind that when grading, the TA may change the data in the cells. In terms of the genre, it is safe to assume that there will be only 4 genres (Comedy, Action, Drama, Musical) in the final data set.
Tasks
Task 1: Helper functions
The first functions that you will create are helper functions that can be used by other functions.
Function FindMax(valueArray, nameArray) As String
This function takes in two arrays; the first array has the numeric values while the second array has the names. The function will find the value in the first array that is the largest and return the corresponding name (from the second array).
For instance, if the valueArray = (10, 20, 5, 9) and nameArray is (“House”, “Chair”, “Table”, “Book”). The function will return Chair.
Function FindMaxRange(valueRng As Range, nameRng As Range) As String
Similar to the FindMax function, the only di↵erence is that the input values are ranges.
Function printMovieData(title As String, arrayTopic, arrayOther)
This function prints out information about the movies to the Immediate Window. For instance, if the title is “Movie by Genre”, arrayTopic = (“Horror”, “Comedy”, “Action”) and arrayOther is (5, 6, 1).
The output to the Immediate Window will be:
*****************Movies By Genre**************
Horror : 5
Comedy : 6
Action : 1
**********************************************
2 of 4
CS2034 – Data Analytics: Principles and Tools Assignment #2
Task 2
The functions in this task should use the FindMaxRange helper function. Function MovieWithLargestRental(rngMovie As Range, rngMovieRental As
Range) As String
This function finds and returns the movie with the largest number of rentals. There are two input parameters, the first is a range from the worksheet that has the name of the movies, while the second is a range from the worksheet that has the number of times each movie has been rented.
Function MostExpensiveMovieToMake(rngMovie As Range, rngMovieProduction
As Range) As String
This function returns the most expensive movie to make. The first parameter is a range of the names of the movies, the second parameter is the range of production costs.
Task 3
The functions in this task should use the FindMax and PrintMovieData helper functions.
Function MoviesByGenre(genreRng As Range) As String
This function will do two things. First of all, it will determine the number of movies in each genre and print it to the immediate window. Secondly, it will return the genre that has the most number of movies.
Function MoviesByRental(genreRng As Range, rentalRng As Range) As
String
This function is similar to the previous one. First of all, it will determine the total number of rentals for each genre and print to the immediate window. Secondly, it will return the genre that has the most number of rentals.
3 of 4
CS2034 – Data Analytics: Principles and Tools Assignment #2
Task 4
Function FindMovieProfit(nameRng As Range, productionCostRng As Range,
boxOfficeRng As Range) As String
This function will take in a range for the name of the movies, a range for the production cost of the movies, and a range for the box o ce earnings for each movie. The function will determine and return the name of the movie that has the highest profit percentage. Profit percentage is calculated as:
profit = boxO ceEarnings productionCost ⇥ 100 productionCost
Function FindMovieProfitByGenre(nameRng As Range, productionCostRng As
Range, boxOfficeRng As Range, genreRng As Range, genre As String) As
String
As a variation on the previous function, this function will determine the movie that has the highest profit percentage for a specified genre.
Additional helper functions can be created and used if needed.
Submission
Save your Excel file a .xlsm file and name it “userid assign2.xlsx” where userid is your user id. For example, if your uwo e-mail was “dservos5@uwo.ca”, the file should be named “dservos5 assign2.xlsm”. You do not have to submit a PDF copy of this assignment.
Before submitting, ensure that your .xlsm file contains all code for your functions and that your assignment works correctly on the GenLab computers and with Excel 2016 for Windows.
4 of 4