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

Monday, April 9, 2012

Spec Sheet Automator - VBA Final Project by Nick Lyon

Executive Summary

For the final project I decided to tackle a real world business problem that my wife faces daily. She is a sole proprietor interior designer and does business as CLID. This business consists of herself working as an independent contractor delivering interior design services working mainly on medical facilities. Currently she has running this business for 4 years and has consistently been working on remodels of skilled nursing centers in California.

The business problem that I tackled for my final project had to do with data entry into excel. I got the idea by asking her what she used excel for in her business and if there was anything that she did on a repeated basis. What came up was the creation of spec sheets, short for specification sheets. Whenever she selects a material, paint color, piece of furniture, etc. she has to create a details spec sheet with all the information to give to client for purchasing. The process for making these sheets is to open an excel spread sheet and manually enter all the information along with proper formatting so she can print it out and send it to the client. Due to the many different types of products she specifies and the detailed required on these spec sheets she was spending lots and lots of time getting these set up and formatted correctly for each item. With hundreds of items per project this was becoming very time consuming. In order to help with this time consuming process I worked on creating a system that would automatically generate the spec sheet and format it how she needs it but also leave it editable so she could make further adjustments.

To make the spec sheet creation process much faster I developed a system in excel that would allow her to select 5 inputs that would be used to generate and format a new spec sheet in seconds. Once the sheet is created she can also go back into it and edit or add details. Built into the system are also several forms that allow her to add projects and manufacturer data which are used in the creation of the specs. By using this spreadsheet customization she estimates she will be able to save days of work over the course of a month.
 
http://files.gove.net/shares/files/12w/njl8/Final_Project_Writeup_Nick_Lyon.pdf
http://files.gove.net/shares/files/12w/njl8/SpecGenerator.xlsm

No comments:

Post a Comment

Blog Archive