Macro In Limbo
Report automation in market research can come in a number of different forms, with the use of macros being one of them. However there are a number of limitations that may not be always be considered.
Having spoken to many different prospects over the years, some already have some sort of report automation, primarily through using macros and VBA (Visual Basic for Applications) coding to produce charts. For several years I was a macro ‘evangelist,’ using VBA code to automate my reports, although there were numerous issues I encountered which (at the time), I worked around and shrugged off, however inevitably these issues made me look for alternatives in terms of report automation.
The first issue is VBA itself, which isn’t too difficult to pick up for an average programmer, however most of us in market research aren’t programmers at all – sure we can do some Excel formulas a VLOOKUP here and a IFERROR there but not to the extent that we consider ourselves programmers. So to compensate we often rely on YouTube videos and Google searches to self-learn how to write VBA code, or over rely on IT (or another clever whiz internally) to develop and (more often than not!) fix our macros. It is no denying that VBA can be an extremely powerful tool for report automation however the steep learning curve to learn how to VBA code can often prove troublesome.
The next issue I have is the constant fixing needed for macros to work – one macro works perfectly well in Excel 2010, no problem at all – then your IT department suddenly upgrades everyone to Excel 2016 and that great macro just produces error message after error message. Or in another instance that macro works great for one client’s brand tracking reporting, but for Client B – it does nothing at all! And that’s if we realise it hasn’t worked – a slightly undetected table layout change and the macro enters the data into the wrong fields for hundreds or thousands of charts. That means even more data to correct and then constant checking that the macros work each time. As you may be aware, once a macro has been run you cannot simply just undo the changes that are made within the VBA code, you have to go back to the original data table to start again. So the macro needs fixing, this can often take as long as manually creating the presentation yourself, finding the bit of the VBA code which is causing the error or populating the wrong data in tables. That’s if the person who wrote the macro is still at the company, I’ve heard of countless occasions when a company’s ‘macro guru’ has left the company leaving the erroneous macro in limbo.
My last major issue with macros are that they just aren’t very smart! Sure, it takes a very smart person to write them, but the macros themselves aren’t very intelligent. As I said previously a small layout change in a data table or perhaps an increase in brand lists from wave to wave, makes the macro fall over itself. VBA script is unable to handle any errors it encounters – it just puts up an error message and highlights it in yellow. The vast majority of macros are unable to recognize error or variation, they lack the intelligence to recognize something has changed and find an alternative course of action. Of course you can build in different responses in macros, however that takes even longer on the programmer’s part to think of every eventuality when building them.
But there is an alternative to using macros and VBA coding to automate chart production. Our report automation solution, Enterprise, has been in the marketplace for many years helping research agencies and insight teams improve the efficiency of their reports. With a comprehensive group of ‘data wizards’ within the solution, there is no code to write. Plus it comes with customized training and near 24-hour customer support so there’s no need to search YouTube videos to find out how to do something. If someone who is trained up leaves the company, we’ll train the next nominated person – so there’s never any fear of ‘losing’ the internal capability. Our customer success team ensures you’re getting as much out of the tool as possible. Enterprise is sophisticated to handle variation between reports, perhaps the number of brands between different country reports for example. Or even if the order of questions changes between waves of data, the tool will be able to populate the right data in the right chart. It isn’t just the data that can be automated – logos, images, calculations, conditional formatting and rules can also be automated. On average we’re seeing our partners saving between 65-85% of the time it would take them to manually produce their reports. I’d be more than happy to take you through this alternative to VBA scripted automated charting and show how you can start saving time now!
If you would like someone about any charting, reporting or data visualization project, fill in the form below and someone will be in touch.
