Excel & VBA
-
I build automated spreadsheets using MS Excel and VBA scripting.
-
Scroll down to see some of the projects I have built.
-
- automated analysis of large datasets spanning multiple servers;
- dynamic dashboards that represent complex datasets with adjustable conditions;
- automation of data entry;
- statistical analysis and conditional dynamic calculations;
- user interfaces;
- databases;
- (and even good old spreadsheets with formulas).
Working with VBA since 2013, I have built up a portfolio of Excel VBA based applications that include:
I'm passionate about innovating and automating jobs that humans currently do. In particuar, MS Excel is an excellent opportunity to streamline laborious repetitive jobs, saving hundreds of man hours and reduce wastage due to inaccuracy and imprecision.
VBA Projects
-
Kantar Consulting | GlaxoSmithKline
Working closely with Kantar; we developed and created a marketing training simulation, under the theme of marketing a successful and profitable toothpaste company. The simulation was used worldwide at GlaxoSmithKline training conferences.
The simulation was made entirely in MS Excel, allowing the simulation to be quickly set up on a range of computers without the installation of any extra software.
The Happysmiles project made use of VBA to lead users through the game systematically and award points for correctly attributing funds based on the marketing knowledge provided in the accompanied training session.
-
Atkins | Wood Plc | Heathrow Airport Holdings
On secondment for Wood Plc and working with Heathrow Airport Holdings, I created a series of project trackers which were used to monitor the intrusive groundworks as part of the preliminary ground investigation for the proposed Heathrow Terminal Three.
The trackers made use of VBA scripting to regularly scan the server for CSV, XML, and AGS files, and import these into a central Excel sheet and perform a variety of calculations to asses deadline and quality compliance.
These trackers vastly improved the project management comittee's ability to assess project efficiency and quality control using an otherwise overwhelming dataset, and were used by Heathrow Airport Holdings as a primary measure of progress in weekly meetings.
-
Atkins
Atkins required a tool to import laboratory data in CSV form, perform a series of calculations and coversions on the data, and automatically convert this into a presentable output ready for use in risk assessments.
At the time, data analysis was performed manually which was incredibly time-consuming and was vulnerable to human error. I used VBA to carry out the same process in seconds and with a vastly reduced need to continuously check the data for human errors.
The Soil Screening Tool allowed a certain amount of dynamism; it allowed for the inputting of a range of constantly changing threshold values to perform calculations, but had to be secure against accidental tampering from users without permission.
-
Salisbury NHS Foundation Trust
I worked with the Salisbury NHS Foundation Trust Information Governance team to develop a series of tools to provide them with framework to comply with their legal obligations, as outlined in GDPR. They needed a user-friendly, streamlined, and foolproof audit sheet for staff to complete and return, minimising the need for revisiting the sheets in the case of missing data.
Within the constraints of a high security system, the tools made use of conditional formatting, formulas, links and styling techniques where VBA was not allowed, to give the user an experience that smoothly and linearly took them through their required entry points.
The audit tools significantly decreased user error and time spent consulting with the user about missing data. By providing formulas that carried out completion checks and prompting the user on each section, we were able to produce tools that provided a user-friendly, intuitive experience.
-
Landmark Financial Planning
A non-VBA based solution was need to aquire a range of user data on introduction to the company. Landmark Financial Planning was concerned that their client would be overwhelmed by the initial questions and often had to carry out several revsisits of the questionnaire to gather all of the required data.
In order to provide a streamlined experience, VBA was not used; using formulas, conditional formatting and styling techniques we developed a series of progress wheels that monitored the users progress throughout the form and prompted the user to complete any missing areas.
Landmark Financial Planning were able to significantly reduce their correspondance with client regarding this form, allowing them to complete assessments quicker and provide a more streamlined and professional client experience.
-
ESG
Whilst working for E.S.G. (now Socotec), I created a series of VBA based automation tools to improve and streamline manual data processes, such as data input, normalization, and basic statistical and threshold analysis.
The British Standard soil certification process takes laboratory data and compares it against certain thresholds, and declares if the soil is fit for use on a certain site.
This particular soil analysis template uses VBA to compare values against thresholds, perform some volumetric calculations behind the scenes, and plots each sample on a soil triangle (based on clay-silt-sand) for a quick understanding of the soil composition at a glance.
Kantar Consulting | GlaxoSmithKline
1
2
3
4
Working closely with Kantar; we developed and created a marketing training simulation, under the theme of marketing a successful and profitable toothpaste company. The simulation was used worldwide at GlaxoSmithKline training conferences.
The simulation was made entirely in MS Excel, allowing the simulation to be quickly set up on a range of computers without the installation of any extra software.
The Happysmiles project made use of VBA to lead users through the game systematically and award points for correctly attributing funds based on the marketing knowledge provided in the accompanied training session.