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

Thursday, April 18, 2013

Work Activity Tracker VBA


An activity tracking system in the form of an Excel 2010 macro-enabled Workbook that tracks work performed and tasks accomplished for Quality Monitoring employees of the Global Service Center of the Church of Jesus Christ of Latter-day Saints.


1.   Business the System Targets


i.     Quality Monitoring (QM) Team of a Call Center operation. QM Team personnel evaluate quality of service delivered to customers calling, emailing, or chatting with Customer Service Representatives (CSR) of the LDS Church’s Global Service Center (GSC).

ii.   The Call Recording / Quality Monitoring system produces statistics and performance measures of CSRs based on evaluations performed by the QM Team. However, it does not track performance of the QM Team evaluators.

iii. QM Team personnel track their activities and various KPIs manually. These items are reported to GSC management with various manually compiled weekly, monthly, and yearly reports.


2.   Reporting Mechanisms the System will Facilitate


i.     Accountability and Talent Improvement (ATI) HR employee performance measures:

1.   The ATI system tracks annual accountability objectives and talent improvement goals agreed upon by employees and management.

2.   Annual report to management measures level of completion.

3.   Monthly progress is reported to direct supervisors through the ATI system.

ii.   Weekly findings report emailed to direct supervisor:

1.   Progress towards monthly quotas of call evaluations performed

2.   An account of daily work activity

3.   Monthly activity summation on the month-ending week


3.   Efficiency Improvements the System will Implement


i.     Quick and simple recording of the following daily activities:

1.   Time spent on specific work activities

2.   Tagging of Call Evaluations that are candidates for COA submissions

3.   Time spent on completion of ATI objective elements

ii.   Storage of recorded statistics and activities that will allow easy compilation and formatting of the following reports:

1.   Sending of weekly email reports

2.   ATI monthly Progress Notes

3.   Preparation of AIT annual report


4.   General System Functionality and Features


i.     Excel Workbook would be opened at the beginning of a work day and remain open and available for use throughout the day

ii.   User Interface:

1.   Customized Ribbon

2.   Main User Form

Blake Wise - SEO Report

Executive Summary
I am currently doing an internship with a startup named Decisions. They are a workflow automation platform that takes any kind of business rule or workflow and automates it very simplistically. My team is working with their marketing manager and at the beginning of the semester he assigned one of the team members to do weekly SEO reports on Decisions’ competitors. She would Google a keyword, go through all ten of the search results, and then record the key SEO data on each of those results. I immediately thought, “I’m pretty sure I can automate that!” Thus my project was born.
With little human interaction, my project takes the desired keywords, inserts them into Google and cycles through the search results then outputs the wanted SEO data.   

Here are the links to my files:

Sheet merger for student database

This VBA macro was written in response to a request from the TA of the professor of the Family Finance class on BYU campus. The macro was requested when merging a sheet full of data from students’ questionnaires with a sheet that is called the “Student Database” that has all the rest of the student information became monotonous.

The idea of this macro is to find open the student questionnaire sheet, get the data out, check for duplicates, and fill in the missing data for each student in the Student Database. This allows the professor to sort through the data as appropriate and email certain students based off of certain criteria (i.e., students who took the class in fall 2012).

Wednesday, April 17, 2013

VBA Danny Stakland

Executive Summary
At the beginning of the semester I had to create a project plan for my on-campus internship.  It was very difficult to do and I wasn’t able to see it alongside other things I was doing so it was very difficult to keep using it.  For my project I created a program that uses user forms to create project plans and shows different projects alongside each other.  I will be able to use this for creating plans for work, but also for tracking school assignments and for any personal projects that I have going.
The system consists of a ribbon with buttons for adding projects as well as editing the categories that projects go under.  The main form is used to add projects and allows for the input of all the information necessary to do the project.  As part of this form there is a secondary form for adding steps.  Each project has to have at least one step and the steps include the number of hours that it will take, a description, as well as start and finish dates. 
When a new project is created it is given its own sheet with the steps laid out in chronological, descending order.  There is one overview worksheet that has a similar format to the project sheets but includes steps from all the projects.

Project Write Up:
Project File:

Sean Swenson

Natural Gas Workbook

Executive Summary

Transfuels LLC is a company building up the U.S. Natural Gas Infrastructure. We build Liquefied Natural Gas (LNG) stations, thus allowing diesel Semi-Trucks to switch to natural gas. There are two problems we are facing as we build LNG stations. A) The price of Natural Gas is relatively volatile and fluctuates around the U.S. and B) We must build our stations close enough to a liquefaction plant to guarantee supply.

This Workbook is designed to facilitate 5 simple processes
1.      Log into a secure website using credentials and extract daily natural gas data
2.      Use simple tools to compare daily gas prices to historical prices to assist in portfolio management
3.      Email reports indicating that the status of Pricing updates
4.      Geocode Longitudinal and Latitudinal Coordinates of addresses
5.      Use addresses to locate the nearest Liquefied Natural Gas supply point and indicate feasibility.

All Five of these functions can be located under the Natural Gas Tab. They are fairly simply to use and will save my company lots of time when planning where to build their next station as well as when to change their natural gas portfolio.

Sales Report Generator

Executive Summary
I work as a student accountant for an on-campus department, BYU Guest Services. As a student accountant, my two main tasks consist of billing other departments for the hours worked by our staff and distributing income to the appropriate departments for whom we cashier. Our current system for billing the departments suffices for the time being, but our systems that deal with reconciling ticket sales are somewhat lacking.

My project consisted of first, developing a standardized report spreadsheet that would only require slight modifications to fit the needs of the many different departments, and then automating certain functions of the report to increase our efficiency of getting these reports to the appropriate departments. Some of the specific features of this automated report include a custom Ribbon labeled “Reports” from which the user can select which report they wish to generate. Additionally, a generic report template allows the user to select the length of a generic event for which we sell tickets, and then select from a database the stored information regarding the event and department.  

Upon selecting the appropriate event, relevant fields are auto-populated and then all the user has to do is input the tickets sold that day. The report spreadsheet also includes macro-enabled buttons which allow the user to auto-fill the spreadsheet with information from a different event, create a new event that can be stored for future use, or modify an existing event without having to interface with the database.  A user-form facilitates these changes.

Finally, upon completion of a report, the user simply clicks a “Print Report” button which selects the appropriate print region (not printing the reconciliation history used for our own department’s accounting) and prints the report to a PDF document ready to send to the customer.

EVE Profitability Tools

Executive Summary

            EVE Online is an MMO known for its sheer complexity and steep learning curve, which have earned it the nickname “spreadsheets in space.”  It seems only fitting that my project aims to add another spreadsheet to the player’s experience.  While the game encompasses makes possible a wide array of options for space-based combat, what actually interest me is the player-driven economy built into the game.  As an economics major with a strong interest in business and entrepreneurship, I was primarily drawn to the industrial side of EVE where players buy and sell the in-game resources and items they acquire, and use them to produce items for sale or use.  Unlike most business or economic simulators, this market is robust and interesting because it is not the sole purpose of the game.  Rather it is the product of natural demand from other players for goods and services that can most easily be acquired from other players, rather than the game itself.
            However, the items produced in the game are completely homogenous, meaning that differentiation and branding are impossible.  As a result, making a profit in manufacturing is cutthroat and relies on careful analysis of profitability and demand.  This spreadsheet was designed to assist in the identification of profitable items to produce at any given time.  As prices and conditions shift, the profitability of producing any given item can change dramatically, so being able to generate a profitability report on-demand is extremely useful.
            The primary functionality of the current version of this project is the production of profitability reports on any item that can be manufactured.  The report includes a wide array of options to allow the user to customize the report, which is particularly important since a large number of these factors can greatly affect the overall profitability.  In the interest of usability, however, the report will generate a “default” profitability statement when it is first opened, so no real intervention is required if a quick look is all that is required.
            The other major functionality of the project is the management of the extensive combined database that makes the profitability reports possible.  This all occurs “under the hood” of the program on hidden sheets, and the average user should rarely have to use these functions aside from the ability to update the web price queries.  However, this is where the bulk of the work for this project has gone, and now that it is done, it should be significantly easier to generate additional reports and features based on this database.
            In creating this project, considerable effort was put into making the tool accessible and adaptable.  While I had originally hoped to include more functionality than just the profitability report and database functions, I decided to focus on making the project professional, user-friendly, and relatively “bulletproof,” rather than include these additional features. 
I’ve spent well over 100 hours on this project, and I’ve greatly enjoyed the process so far.  I plan to continue working on it to add the additional features I would like to see, such as the ability to maintain a “favorites” sheet in the workbook, export generated reports to an excel sheet or other formats, and the generation of quotes and bookkeeping sheets.  I also plan to publish this project to the general EVE community at some point, hence the focus on usability.  I hope you, the end user, find this project an interesting and useful tool as you play the game.

Blog Archive