In the course of my working life I have never been employed directly as a developer, but I have rarely had a role where I haven’t had to develop some or other application to solve a specific problem. I’ve been doing this for 20 years now and have built up quite a portfolio of VBA apps!
In this article I will take you through a number of these as a way of explaining the sorts of applications VBA is suited to. The applications described here have had real world applications and were / are used in a business context.
I’ve written elsewhere about the benefits of VBA and how all Excel owners are sitting on top of a powerful IDE. In that article I also wrote about how Excel VBA is very good at certain classes of task… here we look at specifics:
Auto generating client-quality PowerPoint presentations
I worked in market research for many years back in the nighties and noughties, and really enjoyed it. I was basically a project manager (amongst other things), and a key component of our work was analysing the results of a research project and presenting it to our clients.
We used PowerPoint for our presentations, and for most clients ALL they ever saw of the project was the physical presentation and the report (a ppt with lots of electronic slides) we left behind.
The craft was interpreting the results and deriving usable insights for our clients, but the day-to-day so often involved making tons of charts and cherry picking the ones that were interesting / relevant to the research brief. So much time was spent generating these charts though… time that would have been much better spent analysing data.
So I set out to automate the process – build a system that would take a text file (generated by Quantum – anyone remember that?) and output a PowerPoint presentation. Using Excel charts was not an option – where we delivered slideshows in electronic format (which was all of them) Excel charts compromised the electronic version and was not acceptable to our clients.
Back then charts in PowerPoint used the MSGraph library, whilst the charts you created in Excel used an extension of this (was it called ExcelGraph perchance?) that replaced the MSGraph data table with an Excel range. It was easy to automate charts in Excel that used ExcelGraph, but if these graphs were used / pasted into PowerPoint they did not look quite ‘right’ and the underlying data table caused issues (e.g. they needed an instance of Excel open to edit them, and would often ask for the original spreadsheet, even if you’d cut the links). Clients would not accept this. I still remember the moment I discovered the MSGraph object model – the last piece of the jigsaw I needed to develop this software (thanks to PlanetSourceCode, which I’ve written about in this article).
Once completed I sold the system to 2 market research companies, one of which I had resigned from a few months earlier to develop this software! It saved hours and hours of executive time on every single project: instead of researchers being delivered a huge file of (often printed) data tables to analyse and convert manually into visual information (expressed in charts), it was now possible to deliver a huge stack of polished and high quality charts, from which the exec could cherry pick the ones that were interesting and identify where deeper analysis was required.
The whole look and feel of the slides was configurable, and there were even a couple of chart options that Powerpoint did not offer (pyramid and a type of snake chart some senior people really liked). One really cool feature of the app was that you could create a new chart in Powerpoint as you normally would, then incorporate it into the system as a template for the automation process. The data import process was also configurable – in those days the data files we had to work with were messy and unpredictable. It took a couple of minutes to generate large and polished presentation and was especially useful for continuous tracking surveys.
In hindsight it had broader potential, but I had to choose a stable job vs pursuing this full time. So ‘AutoChart’ (sounded a tad more original in 2003!) went the way of Betamax.
The code itself ran to 20k lines, and whilst I know line count is an indication of nothing much at all, it’s a reminder to me that, for the right tasks, VBA is limited by imagination not potential!
Sending and optimising order files
On 2 separate occasions I have developed systems that have generated order files from raw data and despatched them in email format to external systems. I can safely say that these systems have generated over 6000 separate order files, resulting in around 1m items being distributed.
Creating and sending emails from VBA is relatively simple using the Outlook application and library.
Converting csv files to JSON
I’ve written a stand-alone article on this so won’t go into more details on this here.
Scraping emails to populate a data table
If you were a parent / carer working with home schooled children back in 2015/16 you may have registered to receive a micro:bit. If so, your registration details were sent in email format to the BBC.
Why not use a proper registration process you might ask… that’s a good question and the answer is long and not relevant to this article. Suffice to say we ‘inherited’ this as a solution and had to make do with data trickling in in emails.
The shape of the emails was not predictable, but the presence of key words was, and this was enough to form the basis of an automation process.
As the emails were received by the relevant Outlook mailbox they were scanned for key words (we couldn’t even rely on the Subject line being ‘correct’). Where they were valid registration emails the key fields were extracted from the email body and added to a data table. As a bonus the system even sent a confirmation email to people whose data was successfully extracted, and a follow up email to those where it was not possible to extract all the necessary key fields.
Preparing prompts for CVA research
CVA – Conjoint Variable Analysis – is a technique that allows a researcher to rank a large list of options based on user preference. The cleverness in the technique is that each participant in the study is required to compare a finite set of binary choices and state their preference. With a large enough sample it is possible to rank the options reliably, without asking each participant to compare every single item to every other one.
Prompts for users are usually presented as visual key cards showing both options (the user picks the option they prefer). A well constructed CVA set of prompts will conform to patterns derived by statisticians – the creation of each pair is not random.
The system I developed used VBA as a basis. It worked out which items to pair together, then used inputs (some copy and an image) to create the pairs. Pairs were then output onto PowerPoint slides which could be printed and used directly in the research.
Generating PDFs
If you look at the reseller page of the micro:bit Foundation’s website you will see a downloadable PDF with info about the peripherals that are available for micro:bit.
The first version of this was auto-generated by VBA using a system similar to the one described above (CVA prompts). Bear in mind of course that PowerPoint can output slides as PDFs, so it is therefore possible to create and output PDFs using VBA.
In fairness, the system I developed is not used anymore and updates are done manually (it is easier to add one or two new ones manually – the original system created 24 in one go, and it was way more efficient then hand making them). Nevertheless, at the time of writing the majority of the entries are still the original set that were auto-generated, and the point I am making is that VBA was a great tool for this automation, and would have easily scaled up to much larger numbers.
The micro:bit Foundation’s sponsorship scheme
In my time at the MEF one of my roles was to create and manage the sponsorship scheme, which is a philanthropic initiative to provide free micro:bits to suitable targets. The MEF should be commended for this activity.
At the centre of the workflow is a VBA application that does the following:
- monitors and tracks stock
- generates various reports – monthly for the accountants, quarterly for the board, and ad-hoc
- generates and sends order files to the fulfilment provider
- sends out status updates to people who have registered
- provides a management console for users to interact with the app.
The success of the sponsorship scheme (e.g. at the time of writing over 1300 libraries in the UK had received sponsored micro:bits from the MEF) is a testament to the efficacy of the software that the workflow is built around.
Ex:bit
None of the examples I’ve listed above could be described as ‘fun’ – they are serious business applications. Take a look at this article I wrote on ex:bit for an example of something built in VBA that is a bit lighter!
Unsuitable for VBA
In the interests of balance, here are some applications that I would never use VBA for:
- Games that have a rich visual content. VBA could be OK for text based games, but as soon as I have to draw something on a canvas I reach for my Java.
- Applications that require a rich UI. In theory VBA can be used to develop rich UIs, but in practise the UIs that are practical to build look a bit samey and dated. Where I’ve been able to build UIs that look impressive they are usually over-engineered. Excel VBA apps look like Excel VBA apps (even if the developer uses the Dictator pattern), and sometimes that just doesn’t cut it!
- Stuff you are building for other developers to use. They probably won’t appreciate it!
- Web development. Just, no.
In conclusion
I am sure all of the applications above could have been developed in other platforms and I guess most developers reading this will have thoughts about why their platform of choice is more suitable.
I can’t say categorically that VBA was the best platform to develop these in, but I can say that developing them in VBA was quick and easy and the solutions were robust and fit-for-purpose.
In closing I reiterate my conclusion from the benefits of VBA article: VBA is a jolly useful tool to have in your repertoire.