Visual Basics
For the summer of 2017, I took on a Quality Assurance Engineering Intern position with DenMat Holdings, LLC. DenMat is a global, dental manufacturer who makes everything dental related ranging from consumer toothpastes to dentist high precision equipment. My position at DenMat was created because they needed to move towards a paperless and more efficient system.
Project Requirements
My project was pretty open ended, but had a few specific design constraints that it needed to achieve.
Streamline production and Assembly
Best way to do this is by updating and optimizing compliance forms used on the floor.
Paperless
It's time the Company moves to a paperless system. The Company wants to be more Eco-friendly and save money.
Data Collection
Data being collected on the floor is currently unavailable for management to analyze.
Continuity and Growth
The Company wants a program or system that will be user friendly and has room to grow as time moves forward.
Less than a Month
I worked at DenMat for a total of 25 days, in which time my design process followed these major milestones:
Creating a prototype that could be used experimentally to see where there are issues with the system.
Revise the system after the experiment and find a way to control the forms without letting them get manipulated.
Match or speed up current assembly times.
Teach Management how to use the system and how to continue it forward.
Self Taught
How Much VBA I knew before
I came into the summer with no knowledge that Visual Basics for Applications even existed. I quickly decided that Microsoft’s Macro link ability would be the fastest, most efficient way to convert old PDF controlled forms into Excel Protected workbooks. I spent about a week teaching myself the syntax of the language and its basic limitations.
Code Breakdown
A lot of what I wrote was learned from Google forums. There is a lot of helpful people out there, and it is amazing what you can self-teach yourself just by using resources available to you. I worked hard on understanding why the code worked the way it did, instead of just copying and pasting script from the internet. I created a 60 plus slide PowerPoint to explain each function or callout I had in my scripts with VBA. This PowerPoint was created to help teach my Engineering Managers how to understand my code and replicate it on future forms.
General Overview
Box Diagram
The general layout of my system looked like the diagram to the right. The floor would open a central word document with macrobuttons typed out on it. This template would create a unique document for each worker so multiple people could access the same macrobuttons at the same time. The workers would select the form they wanted to work on, and my code would open the desired form in protected form. Again, it would use a protected template so no one could edit the form, and each worker could individually fill out what they were working on. After populating the form, the worker could print the form to sign and store per FDA law, and they could submit the data to a larger, background database that only the engineering managers had access to. The managers could then use the larger database to analyze data and trends in production.
User Experience
The beauty of the system that I had installed was the simultaneous use across the floor. Previously the Company had run into issues where only one workbook could be open and no one else could edit it until the current user closed the workbook. With my system, everyone could do their own entry, and when they hit submit, their information would get stored in a database that wasn’t regularly open.
Future
The system I created in 25 days had obvious room for improvement, and I did my best to point these out to my bosses before returning to school. The system needed more error testing. From start to finish I had less than a month to get my product perfect. There was clearly not enough time to debug the entire set up, but I did my best. Have followed up with my boss after a month of school, and he only had one slight issue that I helped him correct in under 20 minutes. I also had a vision for the program to make converting old forms rapid. While each form and format were unique, adding similar buttons such as “input date” or “clear all” could have made creating new forms quick. I tried to write as much skeleton code as I could so that creating new forms would be easy and painless, but I only had so much time in the office.
