The HR specialist team was using several excel files, merging them and then assembling stacks of papers while sitting at a table, to complete the annual evaluation process. As you can imagine, this was a very tedious process and very inefficient.  Estimating over 220 hours or  $ 5,400 invested in this process every year with human resource personnel. Excel with VBA is a very powerful tool which can quickly build more efficient solutions for manual office procedures. Aside from the technology efforts required, the first step required was to reevaluate the entire workflow process for all users. After identifying 10 unique steps through the entire process and normalizing the data required, I began to lay out several different VBA functions and data forms to control the workflow and initiate batch processes. For a professional look, the office customization tool allows the ribbon toolbar to be customized. This is a nice way to lay out the 10 steps and connect the different VBA macros and forms for an intuitive user experience.

The finished result was a tool that enabled a single HR specialist to export a file from the HR systems database and easily import the data into a customized spreadsheet. I connected LDAP lookups to query manager and director email addresses, this way the spreadsheet understood the agency’s organizational structure. This information came in handy for the printing process and other internal functions.

I then created a merge function that would export different forms and templates from Word and Excel and merge the data into single PDF packets per employee record. A cool trick I learned while managing a print server is that you can create a second print queue to the same printer and forcefully set custom settings. For this 2nd print queue, I forced the MFP staple function and named the second printer queue copy with a suffix of staple. This allowed me to create a function to print out all of the PDF employee packets by manager alphabetically to the staple print queue so the finished output was already in sorted finished order with matching envelope labels. (No more stacks of papers by hand.)  From this, all the HR specialists had to do was simply stuff the packets into envelopes and apply the next label to the outside. The rest of the workflow was built to help collect and process the evaluation data.

Another function I created for HR was a manager email notification system that allowed the HR specialist to automatically generate reminder requests batches for missing information from employee files. Additionally, I created the automatic pivot tables function, to allow Senior Management the capability to review details of the review process from an agency perspective, and format and print the data for Director review. The final functions allowed a more efficient process to print employee letters at the end of the process. Since there are so many different solutions for technology available with different costs and difficulties, it was important to leverage a pre-existing software package such as Microsoft Office, a little bit of VBA programming, a bit of process improvement workflow analysis, and a bit of creativity, to deliver a functional solution in a short period of time.

This also saved several of my colleagues a lot of time and aggravation and it saved the agency a lot of overtime hours normally associated with this annual process. These are examples of the value benefited by building these types of projects, even if they are only used as short-term solutions. Sometimes you must use the tools you have, to create the tools you need, as long as you keep future compatibilities in mind. By doing so, it will help to forge a path for future long-term solutions by quickly testing out workflow concepts before investing in larger scale solutions. Also, these types of projects prepare you to better understand the problem that could be leveraged with future projects. Bottom-up and top-down design approaches each have their own strength and weaknesses so it’s important to weigh out the pros and cons to any approach.