Carson Marsh- Workout List version 4.0
Executive
Summary:
The company for which I work is called Method 5
Fitness. They provide five core services
to customers who want to create a fitness-centered lifestyle, and one of those
core services is access to a website that contains workout videos for various
workouts including Yoga, Circuit, and Cardio workouts. So far, they have hired on six trainers who
focus on specific kinds of workouts in their videos, which are filmed at the
studio at Method 5 (called “M5”). The
information about these videos is kept on a table in Excel maintained by the
video head, Brannon. Brannon (no, it’s
not Brandon, although everyone thinks it is! J) is also responsible for updating the “Possibilities”
tab when a new trainer creates a video at the studio or when a new type of
workout is introduced. The Possibilities
tab includes the options for each column in the main “Table” tab.
Brannon approached me and asked me if I would create a
system for him where he could filter and analyze the information on the
Table. When I started the project, there
were 49 workouts in the table, and today there are over 80 (although my version
still only has the 49), so Brannon is going to have a hard time keeping track
of them all without time-consuming analysis.
My job was to have VBA perform the analysis for him. I created a system to filter through these
workouts based on criteria chosen by the user and to display the workouts that fit
the criteria on a separate worksheet called “Analysis”. For example, Brannon could use this system to
find all 30 minute workouts recorded by Amy, or he could see how many Yoga
videos use a Yoga Mat as the sole equipment in the video. Later, Brannon approached me and asked if I
would create a different option within the form I created to allow for date
ranges as well. I called this type of
analysis a “Report”, and so now analysis without date ranges uses the “Analyze”
button on the form, and analysis with the date ranges uses the “Run report” button
on that same form.
I also added a second button to the ribbon. This ribbon is called “Specific Report”, and
it creates a report and a table based on a specific Category, Class Name, or
Trainer. If “Trainer” is chosen, the
report will create a worksheet with a table showing workouts by workout
Category; if “Category” or “Class Name” is chosen, the report creates the table
showing workouts by Trainer. The ribbon
tab is called “Analysis”. The first
button, “Analyze”, pulls up the form to run the analysis spoken of in the
previous paragraph, and the “Specific Report” button asks for the category,
class name, or trainer.
Implementation
Documentation:
I’m
going to divide this documentation into an explanation of the two buttons on
the ribbon. The first is the Analyze
button.
All that
the Analyze sub does (as called by the analyze ribbon) is select the “Table”
sheet (with the table that Brannon updates) and shows the form called frmAnalyze. The form has to be initialized, which
includes preparing all of the combo boxes with the values that exist in the
table. The main subs are the “Analyze”
and “Run Report” button click subs. They
both call the same sub, called doTheThing, but with a different argument
specifying whether the thing generated will be a generic analysis or a full on
report. The doTheThing sub is really the
main sub of this section. This is what
it does:
·
The sub fills an array that contains all the
values inputted into the form.
·
If the user had clicked the “Clear it!”
button before clicking “Analyze” or “Run report”, doTheThing will clear the
contents of the “Analysis” spreadsheet.
·
The sub checks to make sure that there is a
value in at least one of the applicable combo boxes or text boxes. If you put in nothing, the caption of the
form changes to “What would you like to analyze?
·
The sub then checks each row in the Table to
see if the workout fits the criteria chosen.
If it does, then the sub adds the workout to the analysis table
·
At the end, the sub kicks out a message box
telling the user how many new workouts were added. It also tells the user how many total workouts
there now are on the “Analysis” spreadsheet (which will be different if the
user didn’t click “Clear it!” and if there were previous workouts on the
spreadsheet.
Secondly,
I’m going to talk about the “Specific Report” button on the ribbon. To follow pattern, the main sub for this
button is called doTheOtherThing. Differently from doTheThing, doTheOtherThing
calls a method, which does some things and calls another method, etc. The first method called is “userChoice”,
which asks for an input of “Trainer”, “Class Name”, or “Category”. It also deletes previous reports. It then returns in a message box with the
options for that input, and then the user is prompted to choose one. From there, the fillArrays sub is called with
the report type and the reported name.
fillArrays
fills a series of arrays that will be pulled to generate the report. These arrays are number of workouts, total
time for the category, and either categories or trainers, depending on the
chosen report type. The fillArrays sub
then calls the generateReport sub, which selects a newly created sheet and
actually creates the report. If “Trainer”
is chosen, the report generated will take the trainer (we’ll call her Trainer
A) and take Trainer A’s workouts sorted by category. For each category, the report will display
total time of workouts in that category and total number of workouts in that
category. If “Category” or “Class Name”
is chosen, the same thing is done for Category A or Class Name A sorted by
trainer.
Finally,
the generateReport sub calls a makeChart sub, which generates a chart based on
the report generated. This chart shows
the total number of minutes per category or trainer. Again, if the report type is “Trainer”, then
the chart will show the minutes Trainer A spent on workouts in each of the
categories, and if the report type is “Category” or “Class name”, the chart
will show the minutes each trainer spent on workouts in that category or with
that class name.
This
is an example of what some of the workouts might look like in Brannon’s
table.
Video Number
|
Category
|
Class Name
|
Duration
|
Trainer
|
Equipment
|
Filmed
|
Edited
|
Uploaded
|
Posted
|
Date Shot
|
Date Posted
|
1
|
Circuit
|
Functional Circuit
|
45 min
|
Kara
|
Resistance Bands
|
Yes
|
Yes
|
Yes
|
Yes
|
10/9/2013
|
10/18/2013
|
2
|
Core
|
Core Blast
|
15 min
|
Kara
|
Resistance Bands
|
Yes
|
Yes
|
Yes
|
Yes
|
10/9/2013
|
10/18/2013
|
3
|
Core
|
Core Blast
|
15 min
|
Sarah
|
Hand Weights
|
Yes
|
Yes
|
Yes
|
Yes
|
10/9/2013
|
10/18/2013
|
Discussion
of difficulties encountered:
Perhaps
the biggest difficulty I encountered happened after I thought everything was
already up and running. Brannon came to
me after I’d made the program for him, and he told me that it had crashed. With the new table he had entered, the
program froze every time he tried to load the “Analyze” form. I had to delete almost every row of his table
to figure it out (we had another saved copy!).
It turns out that setting combo boxes doesn’t work if there is only one
option, and all of Brannon’s videos had been filmed at that point, so there was
no other option and the program froze.
To solve that problem, I created a “Video 0” with “ “ in the “Filmed”, “Edited”,
“Uploaded”, and “Posted” columns. I then
hid it on the table. That allowed for
the initializer (which I took directly from the form project and changed it to
meet my needs) to work right regardless of the status of the “Filmed” and other
columns.
That was
honestly my biggest challenge. I made a
lot of mistakes in dealing with arrays, but I just had to go through the
debugging process to figure out what was actually going into the arrays and
what I was pulling from the arrays. I
had some trouble with my variables; for example, as I first created my message
box to show the trainers, the box displayed six trainers, all of whom where
Amy. Going through the debugging process
took me a while, but it wasn’t as big of a head-scratcher as that first
difficulty.
Assistance:
I did
not receive any assistance on this program aside from the code that we had
written for other projects or that was given to us for other projects in class.
