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, December 8, 2011

Internet Piping

Internet Piping

Final Project

By Scott Gonzalez

Executive Summary

My father works for a company that installs the underground piping for internet businesses such as Comcast, Utopia, and various other corporate and government entities. He is tasked to produce a weekly production report that takes him about an hour and a half to complete which he does on a calculator first and puts the calculated numbers into Excel second (not a sophisticated user of Excel by any means). The report consists of various employees that make up four crews. The information that needs to be reported by crew is the daily amount of piping feet installed, the daily amount of revenue brought in (calculated by price times the piping feet installed), the daily amount of labor dollars spent, the job number(s) worked on that day, and labor as a percent of revenue. Besides these daily totals, the report should also calculate weekly totals by crew and the weekly totals overall for all crews combined. Finally, there is one crew that installs four pipes at a time with a drill.

A macro is the best tool for my father’s situation. My father does not keep a master copy and copies the prior week’s report to make the next report. Coupling his method of copying worksheets with his propensity to override labor and revenue numbers for various reasons indicates that simply making an articulating spreadsheet would be copied wrong from one week to the next if a formula was overridden. A macro will solve this problem by writing over any one-time changes he made from the prior week. Again, my father takes about an hour and a half to complete this report each week; my plan is to create a macro that will take him at most five minutes to complete the same task.

Contents of Internet Piping - Final Written Project

Detailed Discussion of Project

Step 1 – Create a Master Worksheet and Worksheet Functionality

Step 2 – Create an Employee Name and Wage Worksheet

Step 3 – Create the Terrain Worksheet

Step 4 – Create the Macro for Building a New Weekly Production Report

Step 5 – Create a User Form

Step 6 – Prepopulate the User Form

Step 7 – Use the Inputs to Calculate Labor and Revenue per Crew

Step 8 – Populate the Spreadsheet

Step 9 – Enable Code to Run the Macro for the Corresponding Crew Depending on the Location of the Button

Discussion of Learning

See the full written report and macro-enabled workbook in the links below:

No comments:

Post a Comment

Blog Archive