These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Friday, December 13, 2013

Ward Directory Automator

Spencer Johnson
VBA Final Project
Ward Directory Automator

One of the things that has always bugged me about the start of a new semester is how long it takes for the new ward leadership to finish compiling the ward directory. In some of the wards that I have been in, that process is relatively streamlined and the ward directory is completed within two to three weeks. However, some wards, take months to get the directory out and by the time it is available to everyone, it becomes obsolete because a new semester has started and people have moved in and out of the ward. For example, my current ward barely finished the ward directory a month ago after we had lived in the apartment complex for over 2 months. The semester was almost over before we finally got a “rough draft” of the ward directory that includes names and information only, but no pictures.

In today’s age of technology, I wanted to see if there was a way to automate this process to produce a high-quality, error-free ward directory within a few days of finishing all picture taking and information gathering. I also wanted to see if there is a way to automatically pull the data that already exists from the website and programatically match up that data with the pictures based on picture filename. That way, all the ward clerk would have to do would be quickly rename the pictures in Adobe Bridge or some other program and push a button to automatically generate the bulk of the ward directory.

            The project proved to be much harder than I had original anticipated (see conceptual difficulties section), however, in the end I wrote a program that was able to download ward directory information from, import that workbook into excel, and pull photos from a folder and match them with the information in the ward directory.

If I had more time, I would have liked to spend another 20-30 hours working on adding features to the ward directory such as options for the number of pictures per row or column, a method to clean up the addresses more, and a few fun ideas I had for dating prospects. In total, I would estimate that I spent about 40-50 hours working on this project.

eCommerce Partner Store Sales Report

eCommerce Sales Report – The Saranator
Sara Baird
Dr. Meservy
IS 520 – Fall 2013

Executive Summary
This project was carried out for the benefit of the eCommerce Department at the BYU Bookstore, recently rebranded the BYU Store. As part of that rebranding, the eCommerce department was put in the spotlight. The expectation is that the department will continue to grow and bring increased revenue to the struggling store.

I’ve worked in the eCommerce department for three years and can remember the day the Drakinator stopped working. Four years ago, another IS 520 student named Drake Allsop developed a slick tool for generating special sales reports for my boss, Jenifer. Every month she creates various reports for our partner stores and generates a check request. It’s a tedious process and prone to error because of the many small, repetitive details involved. For my project, I set out to revive the Drakinator and add requested additional features.

This program has been renamed the Saranator and can again generate specialized sales reports based on three form inputs only. The report is printed for every partner store contact so they can monitor inventory sold at the store. I also created another form that allows Jenifer to add new partner stores. Updating this information was impossible for Jenifer because it had previously been hard-coded into one of the many intricate subroutines of the Drakinator. Now, the Saranator allows much more flexibility for future eCommerce growth. This revised tool will again save hours of work for Jenifer and allow her to focus on more important happenings in the eCommerce office.

Thursday, December 12, 2013

Todd Wight - VBA Project Executive Summary - Financial Statement Analysis

Todd Wight
VBA Final Project
MBA 614
Fall 2013

Executive Summary

Company overview
A. Schulman is a global plastics company with over $4B in revenue. In the Winter 2013 semester, my Field Studies team consulted with them on a field Studies project. Part of this consultation required an in-depth analysis of their business units in the Americas. North and Latin America each contain four business units; however, Distribution Services was shut down in Latin America, for the time period we analyzed, so although it is included in the financial statements, it has no data in my computations.

Take the non-standard presentation of financial statements and compute ratios, charts and graphs. This should eliminate the need to number crunch manually or to continually double- and triple-check the results.

The hard part of this work last year was that the financial statements all had different lines on them, depending on the business unit (BU), which made it very time consuming. (Ex.: SG&A would be on line 100 for one BU, but on line 112 for another BU.) It was also difficult because the years started with the most recent first, but that meant I had to create a table with the order reversed in order to create the graphs. I really wished I could have created a macro to do it all automatically and save myself boat loads of time and stress.

Note: I originally intended to include another small project for this assignment, but I did not end up doing it. I cleared this decision with Nathan.

Benefits of this project
·         The automation removes the need to double check all of my computations. The complicated part of the project was that the Excel data was given in reverse order (2012, 2011, 2010) of the order we needed it to be in to make a chronological analysis (2010, 2011, 2012). This was a lot of manual work on my part but with this automation, it happens automatically.
·         The automation made it easy to standardize the formatting on all of the charts. They requested data that took about 40 charts and at the time, it was a huge pain to go back and change all of the formatting. With this code, I could have simply edited a couple properties and re-run all of the code, saving hours of work.
·         Each table needed three charts to make the appropriate comparisons.

Implementation documentation
·         All of the outputs are placed a tab called “Computations” which is created by the VBA code. The code is smart enough to delete any existing sheets with the same name before running the code, and suppresses any possible alerts in the process. This is particularly important since Excel does not allow copying data over tables, so the sub will fail if it does not start on an empty sheet.
·         The subroutine loops through all of the workbooks and worksheets in each workbook and gathers all of the necessary data into an array. The source data is contained in a folder data “data” that resides in the same directory as the workbook running the code. At this stage, no computations are yet made.
·         The data is then copied into the worksheets in Excel tables with specific formatting and headers (bold, color and titles). The cells themselves are formatted as currency.
·         The requested computations are now performed using the tables. The computations utilize matrix computations so that they can be done in a single line of code, avoiding unnecessary double for-loops. The resulting data are also formatted as tables, and since these data are all percentages, they are formatted accordingly.
·         For each table, three line charts are created: One comparing the data for North and Latin America, and one each comparing the business units for North and Latin America. The axes label units reflect the charts themselves, and decimals are removed for simplicity.
o   This portion of the code is hard-coded much more than I would like, but it was a little more complicated since it required specific rows from the tables.
·         Provide a concise, well-organized documentation of what you actually did for your solution. You may want to use tables or bulleted lists to describe the components of your solution and their role in the overall task. In any case, you should provide a textual description of the elements so it is clear what you have done, why it was included, how it is intended to be used in the task. Screen captures may be helpful in illustrating what you have done.

·         This was an individual project and I was not part of any team.
·         I received no assistance from other individuals on this project. This means I did not make any posts to online forums requesting assistance.
·         On many occasions I did consult the internet to learn how to perform various tasks, but I did not copy any extensive code. There were a couple small, modular chunks that I did copy, but in each case I had to modify the code to suit my needs.

Challenges and learning points
·         It was difficult for me to outline the entire project without making a lot of progress on it. This made it a little more difficult to know how and where to modularize my code, and discouraged me from starting with a UserForm that would allow for the dynamic direction of which data to gather and analyze. Towards the end of the project, this became more clear, but modifications of the code at this point were slow.
·         Chart objects can be finicky. I initially used a macro to learn how to use charts in VBA; however, the recorded macro omitted the use of a ChartObject object. Without a ChartObject object, formatting becomes much more complicated, and I eventually realized I would have to re-write this functionality to make use of a ChartObject object.
·         One of the challenges I had during the original project was that they wanted to see a lot of charts and it took a long time. I spent a lot of time trying to figure out how to copy the charts to a PowerPoint presentation. I each chart to occupy a single slide. In the end I was unsuccessful.
·         Going into this project I was not very familiar with the functionality of tables. There are many things that I still do not understand, but they are very useful, especially for aesthetic reasons.
·         From the time I began using tables for the data, I suspected that there would be an easy way to make a new table that was the result of a computation involving two other tables. I did not find a way to do so using table functionality, but I did find a matrix computation that can do it in a single line, avoiding double for-loops. It requires a little bit of manipulation but once that is done, it is a pretty slick bit of code. Here is an example; the matrix computation is the second line of the With block.

·         I realized that there are many ways to work with Ranges and I became much more familiar with them. There is also a lot for me to continue to learn!

What I would have done next
·         I made an attempt to copy each chart to a PowerPoint slide but I was unable to get it to work. The syntax of interfacing with another program was confusing. It would have been fun to do, but given my time restraints and the fact that I didn’t include it in the scope of the project to begin with, I left it out.
·         I would have built user form that would allow the user to select with data and ratios to compute and graph. Right now there is a fair amount of hard-coded work. This works for this project since the scope was well-defined to begin with, but it would be nice to accommodate requests for more analysis in the future. I actually did quite a bit of modularization of my code in the hopes that I would get far enough along to do this part, but I didn’t get there.
·         I would have moved the button to a ribbon.
·         In hindsight, I probably should have moved all of the original data into a single workbook to make things a bit cleaner, but it works as is.

To run the program:
·         Data files will need to be in a folder named “data” that resides in the same folder as this file.

·         Click the button “Analyze Financial Statements” which is on the “Home Base” tab

Math Worksheet Generator

Executive Summary:

                In my three years as a high school math teacher in Denver Colorado, I spent hundreds of hours making worksheets for my classes.  Students were much more likely to do problems on a worksheet then they were out of a book.  I also had more control over the problems that went into the worksheets.  Creating worksheets though is incredibly time consuming.  There are official software programs (like Kuta software) that generate math worksheets for teachers, but they cost thousands of dollars and if school departments are not willing to pay for them, teachers are out of luck.  For my project, I created an excel program that will generate math drill worksheets, including multiplication problems, division problems, and algebra problems.  The program also generates an answer key in a separate worksheet.  Teachers can select one of these three options to create a multiplication, division, or algebra worksheet.  They then can select how many problems they would like to generate, what number range they would like the values to be in the problems, how many columns they would like to have on their worksheet, and if they would like to save the worksheet and answer key as a pdf.   

Final Project: Automated Invoice Calculation and Delivery System (Lowell Smith)

Executive Summary

Purpose of this project:  Create an automated system for extracting from our DB the services provided in a given month, adjusting the pricing according to the CPI Index, creating individual invoices and detailed reports for each customer, providing summary files of revenue and services for accounting and the executive team, and delivering the invoices to our customer contacts.  When done with the click of one button, this will save our company money by significantly reducing employee involvement, the likelihood of manual errors, and consequently strengthening the integrity and professionalism of our company from a financial / billing perspective. 

Company:  University Health Resources (“UHR”) is an advisory firm that provides guidance to its customers throughout the United States.  They are a real company, and their name has been changes. For this project we will assume they have contracts with 14 highly reputable universities.  A university such as BYU calls upon them to provide counseling in handling specific problems that occur in their day-to-day operations.  UHR is a leader in these services yet are still very much a small business.  Executives see no need to upgrade their rudimentary financial system, and as a result their billing and in-house accounting is done manually through Microsoft Excel.  This lack of desire to purchase financial software presents multiple problems:
·        Pricing structure.  When UHR contracts to provide services, they determine a service rate per case handled.  The rates for each university vary depending on how well their negotiations went during the contracting period.  After determining a rate, the price adjusts annually based off the Consumer Price Index (CPI).  UHR has not established a procedure for updating this information.
·        Manual data extraction.  When invoicing occurs each month, UHR manually copies case data from their Microsoft Access DB and sorts and filters for each client.  This manual process is lengthy, laborious, and ripe for user error, sometimes resulting in billing a client incorrectly by thousands of dollars
·        Invoice delivery.  As each monthly invoice is calculated, it is sent to the client via email.  An employee in UHR’s Accounting department types out each invoice by hand and checks to guarantee that it is sent to the right contact and that the relevant fields on the invoice (CPI-adjusted price, case volume, and client contact information) are manually entered.  As they work with over 1,000 clients in actuality, it is clearly cumbersome, and errors in pricing and accuracy are normal.  More and more customers are asking for detailed reports to accompany their monthly invoice so they can see just what services were provided.  Creating this report manually is laborious.  Customers are also frequently billed at wrong rates and must be sent revised invoices.  Similarly, customers with low case rates occasionally are billed at higher case rates and call UHR upset they are being billed incorrectly.  This lack of professionalism does not foster trust in UHR’s services.

One of my goals in working with UHR was to see how VBA can improve their billing procedures to avoid such catastrophic mistakes.  If this system can be streamlined, they will save significant employee expenses on a monthly basis, improve their accuracy, as well as their corporate reputation.

Web Scraping Database Generator for Film Industry

Executive Summary

            Company Background 
  NEBTEK, rents and sales equipment to individuals and Production Companies in the film industry as well as provide technicians to operate the equipment on set if needed.  They have done practically no outreach what so ever for their rentals division, the rentals they get have all been by word of mouth.  The way they get most of their work is from their technicians they work with.  The technicians will usually get asked to do a job and then they tell the production company to use NEBTEK.

            Overview of system

I built a system to help increase their rental jobs by increasing the outreach for technicians and the rentals division.  It gathers information on film jobs and sends out technician resumes to those jobs.

Personal Budget Expense Automation (Wells Fargo)

Project by: Nathan Petersen

This budget expense automation program aims to solve the issue of inaccurate expense allocation for personal budgets, and the inefficiencies with manually entering in this information.  The program has four main program steps:
1.       Downloads bank checking account information

2.       Allows user to first manually allocate expenses to correct expense categories, and save allocation percentages.

3.       As more allocation data are saved, the program learns to automatically allocate similar transactions, allowing increased accuracy and usefulness over time.

4.       Monthly transaction data are placed in a pivot chart for simple analysis.

Charity Vision

Nate Jackson
Executive Summary

                The organization that I built my project for is Charity Vision.  Charity Vision is a non-profit that performs charity surgeries in third-world countries.   They currently are operating in 23 countries and dozens of doctors across the globe.   They are unique in as a charity because instead of sending America doctors overseas to perform these surgeries, they enable local doctors to perform these surgeries.   Due to their large network, they need a way to uniformly gather reports from each of these countries.    Once they gather this information, they need to be able to analysis it to gain a better understanding of how each country or doctor is performing.  My project is designed to grab this data they have collected and to dynamically generate reports based on each region, country and doctor.  

Payroll Helper for Middlebury Interactive Languages

Stephen Godfrey
IS 520 – Spreadsheet Automation
December 10th, 2013

Executive Summary

Company Profile

Middlebury Interactive Languages (MIL) is a company that creates software that helps children to learn foreign languages. They employ roughly 150 people and have two offices, one in Middlebury Vermont and another in Provo, Utah. Vermont houses their executive team and their client services team. Provo on the other hand houses mainly technical staff such as IT, Development, Course Content, and Professional Services.

Recently they created a tool (written in Ruby) that can connect to multiple learning management systems. Some of these systems are Blackboard, Canvas, Desire to Learn, Brainhoney, and Moodle. Yet they still have a home-built learning platform (written in PHP) which some of their older customers still use.
MIL offers courses in multiple languages, but the most notable are French, Mandarin, Spanish, German, and Latin. Every language has specific courses built for students in Elementary School, Middle School, High School, and Advanced Placement.

Since they connect to multiple systems, they also need to obtain reports and data from those systems. And that is how I became employed at MIL as a Business Intelligence Developer. My primary task is to take data from multiple sources, and warehouse that data into a central location. They also need information gathered to process their payroll, and that process is quite technical. Yet I realized that part of it could be automated.

System Overview

We needed our enrollment data from Blackboard, but they would only give us an Excel file with the data. We repeatedly asked for direct database access or access through an Application Programming Interface, but they would not yield. So we came up with an alternative solution.

They would build a report that matches our database structure, and I would fill in the rest of the cells, and import the modified spreadsheet into our database. The most challenging task was matching the teacher names in our list to theirs. I had to add the teacher names and ids to a second spreadsheet and do a vlookup for the teacher name, and add the teacher’s id for the given enrollment. But the process was tedious, and so I decided to automate it.

I created a payroll helper that makes the process easy. When you simply open the workbook and the form automatically appears in the center of the screen. Then there are command buttons in the middle column that complete various tasks. Lastly, the ‘Output CSV’ file will create a csv from the data in the workbook, and save it in the same folder that input file is located. That csv will then be reviewed for any inconsistencies and imported into the teacher payroll database.

Home Teaching Organizer

Executive Summary:

In the Church of Jesus Christ of Latter-day Saints, all active males are assigned to “home teach” other members of their congregation (or “wards”). Conversely, all members, unless notably hostile toward the Church, should be assigned a pair of two home teachers (“companions”). Elders Quorum Presidents and High Priest Group Leaders are responsible for making such assignments. That task is daunting, and needs frequent updating. To make matters more burdensome, the only standard way to handle the assignments is on a certain computer in the Church.

This VBA project seeks to allow those responsible for organizing Home Teaching to do it from home on Excel. Users can download their latest Ward Directory from, fill it with useful information (activity level, quorum with which they are assigned, what Priesthood is in their home, and other notes), and use that to guide their organization of home teaching assignments. The user can then assign companions and assign them to particular families, and then print convenient slips to hand out to the Priesthood holders, giving them all of the contact information of their companion, home teaching families, and the person they report to (“district leader”).

Colin Blair and Andy Heaton's "Mr. Budget" Budgeting Software

Executive Summary

Historically, budgeting software has dealt solely with the debits and the credits.  We have identified a need for a more personalized budgeting software that lets the user specify what is most important, while also providing the user with valuable financial advice.  Mr. Budget is a VBA-based budgeting program that gives users an unparalleled amount of control over the organization of money they are trying to save.  

Mr. Budget allows you to create savings categories and then allocate percentages or straight dollar amounts from your income to those categories.  As you set financial goals, Mr. Budget will help you allocate your money to meet these goals while your actual earnings remain safely in your bank account.  Thus, your money is virtually split up and you know how much you can withdraw for certain things while not going over what you have saved for that category.  

Mr. Budget knows that amazing budget software alone doth not a financially secure person make.  Mr. Budget’s financial advice feature utilizes a robust web component to aggregate excellent financial advice on a variety of financial topics for the user using a combination of VBA and HTML 5.  

Carson's VBA project

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
Class Name
Date Shot
Date Posted
Functional Circuit
45 min
Resistance Bands
Core Blast
15 min
Resistance Bands
Core Blast
15 min
Hand Weights

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.

            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.

Blog Archive