How Do I Quickly Update User Paths?

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.

user paths

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.

Private Sub Workbook_Open()
Dim Cell As Range
Application.Calculation = xlCalculationAutomatic
'update paths
If Application.UserName = "Angelina Teneva" Then
For Each Cell In ActiveSheet.Range("A4:A27")
Cell.Value = Cell.Offset(0, 15)
Next Cell
For Each Cell In ActiveSheet.Range("A4:A27")
Cell.Value = Cell.Offset(0, 17)
Next Cell
End If
End Sub
view raw UpdatePaths.bas hosted with ❤ by GitHub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.