5-Year Plan Upload

VBA FINAL PROJECT – 5-Year Plan Upload

Spencer Hafen

EXECUTIVE SUMMARY

In my last job at Intermountain Healthcare, part of my job was assisting people that had problems uploading 5-Year Plan data into the databases Intermountain Healthcare uses.  I found myself helping people who had problems uploading data into these databases a lot.  This was largely because the 5-Year plan was only done once a year, and by the time the next year rolled around most users had forget how to do everything.  Even with detailed instructions, problems were inevitable.  Year in and year out this was a stressful time for whoever had to deal with these problems.  I was struggling to find something useful to do for my Final Project, so I decided I would give my former manager Chris a call to see if he wanted some help automating this 5-Year Plan process.  He said he would love whatever help I could give.  Chris said ideally he would like a single workbook with simple buttons on each worksheet that did  the following things:

1.      Connect users to the correct database (several different databases were being used by different hospitals)

2.      Pull historical data (previously done manually)

3.      Input & Load data (previously done manually in different workbooks)

4.      Run 5-Year Plan Calculations from the Workbook (previously done on the database application itself to avoid problems)

5.      Create a Report to Compare Ratios (currently there was a report to do this, but it wasn’t built into the same workbook)

Ø  I also created Ribbon Buttons.  I told Chris I could create ribbon buttons at the top of the page to do the same thing, but he said that for now he wanted individual buttons on each page to make it as simple as possible for users.  While not part of the final deliverable to Chris, for the purpose of the project I created the ribbon buttons anyways.  That is why on the screen shots below you will see buttons on both the worksheets themselves as well as on the ribbon.

Ø  Instruction Manual: As part of this project I also created an instruction manual to guide users through the new 5-Year plan process.  I debated whether to include the whole instruction manual, but it had a lot of supplementary stuff that doesn’t relate to the project so I just included the necessary pieces in my implementation documentation. 

Final Report

Excel