Let’s face it – few analysts are lucky enough to work with clean data, obtainable from a single database.
I certainly wasn’t among the lucky few when I was working as a Reporting Analyst, which meant that before I was even able to produce a standard Excel report, required on an ongoing basis, I had to do a lot of data wrangling.
Having to do this manually every time on top of changing business requirements pushes up your error rate exponentially, which in turn negatively affects the trustworthiness of your analyses and your credibility as an analyst.
Hence I had to make sure that my error rate was kept at a minimum. The way I chose to go about it was to implement a robust set of VBA-codes for every dataset that I had to handle in order to produce an analysis.
It was all working like a charm – all standard reports were produced correctly and within very-short turn-around times, which freed up a lot of my time for more ad-hoc analyses. Furthermore, there were hardly any issues when my stand-in had to refresh the weekly iterations when I was on holiday.
This was also because of the way I had chosen to code all the paths where my input files were located. Instead of hard-coding them in every sub-procedure of my code, I kept them on a tab within Excel, and referred to them using variables in VBA.
This meant that, whenever, a path needed to be changed, it was easily accessible. It also meant that I could easily implement logic in my code, which would change them accordingly to who the person who was using the file in Excel.
You can see the code below
Triggered upon opening the excel workbook (provided macros have been enabled), the first thing that the code checks is the user name to which Excel is registered. If that was me, the code would then take the paths specified in column P. If the Excel user wasn’t me, then the paths specified in column R would be populated.
Thus, the only thing I had to make sure before going on holiday was to make sure I updated the correct paths for my stand-in at the time.
Happy VBA coding!