CS 2034 Data Analytics: Principles and Tools
Assignment 2
Learning Outcomes:
By completing this assignment, you will gain and demonstrate skills relating to
creating functions and subroutines,
using compound structures in VBA,
calling and reusing functions,
developing algorithms and writing code,
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
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. 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
Jamie
高亮
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.
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 difference 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
********************************************
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.
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 office 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
𝑝𝑟𝑜𝑓𝑖𝑡 =
𝑏𝑜𝑥𝑂𝑓𝑓𝑖𝑐𝑒𝐸𝑎𝑟𝑛𝑖𝑛𝑔𝑠 − 𝑝𝑟𝑜𝑑𝑢𝑐𝑡𝑖𝑜𝑛𝐶𝑜𝑠𝑡
𝑝𝑟𝑜𝑑𝑢𝑐𝑡𝑖𝑜𝑛𝐶𝑜𝑠𝑡
× 100
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.
The name of the file you submit should be your UWO userid_Assign2.xlsm. For instance,
my assignment would be oola_Assign2.xlsm. Make sure you attach the file to your
assignment, DO NOT put the code inline in the textbox.
What You Will Be Assessed On:
1. Formatting
2. Reusability/Functional Modularity
3. Completion of Tasks outlined.
4. Assignment submission: via OWL assignment submission.