Excel VBA is brilliant, but is there something better?
13 December 2022
What I have learnt
For about 15 years I have been writing Excel VBA and, it is brilliant.
If you are unfamiliar, VBA is a coding language that sits within Excel and allows you to do a range of things, not least the automation of laborious manual processes.
I have spent many years working on transformation programmes and typically I would write VBA to fill the gap until strategic solutions were developed. A couple of days of coding to provide an 18-month interim solution was often a no-brainer.
The problem came when I tried to perform handovers. No matter how well I thought I had documented the code / conducted training sessions, the reality is there is a learning curve that needs to be climbed. The simple fact is that the majority of Excel users, do not have the time or the inclination to achieve this. An initial glance at VBA code can be a real turn-off for even the most experienced Excel user.
The “Lack of Supportability” argument for Excel VBA has been around for many years. I don’t fully buy it, the lack of supportability is driven by the lack of desire to support VBA solutions – so it is a self-fulfilling prophecy. However, the end result is the same, in that it is very difficult to transition any developed code to other teams to support it.
The solution to this problem I have often heard through the years is to replace Excel VBA with strategic platforms.
Sounds great!
Hang on a minute, isn’t one of the main drivers for an Excel VBA solution to provide a “fleet-of-foot” solution whilst Strategic platforms are being developed? Also, not every organisation has the kind of budget available to implement these systems anyway.
Is there a better way?
So, what if you had the best of all worlds? The ability to produce “fleet-of-foot” solutions, which are transparent, supportable and therefore much easier to wrap controls around. Sounds great!
Hang on a minute, but how can this be possible? Any tool/solution would require a learning curve and therefore all the handover issues described above would apply.
Recently I have joined Schematiq and have started using Schematiq Workbench and this neatly solves the problem.
The approach is brilliantly simple.
Virtually all of the functionality that I developed using VBA code has been captured in “Excel-style” functions. Combine this with the ability to represent whole tables of data into one cell within Excel and the end result is incredibly powerful.
Ok, so there is a learning curve, but the incremental learning required by a typical Excel user is minimal, because it builds on the approach/mindset of a user and not that of a specialist IT developer.
For example, I had been using Schematiq for about one week and at that point I believe I could solve (in a different way) about 50-75% of the VBA solutions that I developed over the past 15 years.
The layout of the solution is simple, logical and transparent, making any handover very simple.
Moreover, within a few weeks of learning, a typical Excel user could have developed the solution themselves anyway, without any additional support. This creates a massive cost avoidance opportunity for any organisation looking to transform or remediate complex Excel processes.
A significant side benefit is that the transparency of solution makes checking, de-bugging and auditing so much simpler as well.
It is hard to believe it can be this simple, so I have illustrated this with an example in the Appendix below.
Jason Budd
(15 years a VBA developer, now 5 months with Schematiq)
Schematiq approach to gathering data and applying mappings
A common approach to a VBA solution would be to gather source and mapping data, transform the data before producing an output. Let’s consider the first of these, gathering and mapping data.
I have not included an illustration of the VBA approach, partly because there are numerous ways to do this (no doubt leading to a debate as to the best approach!) but whichever method you select the point is the same i.e. the difficulty to conduct a handover of whatever solution you select.
To do this in Schematiq, the solution is unbelievably simple!
NB I completely accept that this step could be completed manually using Vlookup or Index/Match formulae, and if this was the only step then that would be the approach. The point being this is the first step in automating a manual process, hence we are looking to eliminate the manual effort which led us to write a VBA solution in the first place. The end result could be a 20 step Schematiq process, transparent on one Excel sheet with the data readily visible after each step of the process.
Want to learn more? Book a Demo