Archive for the ‘Office’ Category

Is VBA still relevant?

October 31, 2009 1 comment

John Durant made a post about whether VBA still makes sense. I mostly agree with him that VBA still has a relevant place, specially in the financial market.

My start with VBA, or Access Basic to be precise, was all the way back in 1994. As a technical support representative, I’d created an issues tracking database to allow us to easily log different statistics for a given customer call.

Over the years, I played with different programmatic aspects of Office and was always impressed with the speed with which I was able to develop and deploy a solution to suit business needs (or just for my own amusement). In this scope, one would not think of Office as an enterprise app but as a one-off, quick and dirty, solution to a problem. Nevertheless, a solution is a solution, and some times, a quick solution is all you, or the business (more importantly), need. Of course, in the financial market, there were complex pitch books or analytics or document management or similar Office based LOB applications already in place.

Jumping forward to my entry in the financial sector, there were mixed feelings in the IT department about using Excel as a base for any solution. The main concern was maintaining a plethora of workbooks which could lead to a chaotic environment where:

  1. Multiple versions of the same workbook existed.
  2. Multiple solutions were deployed for the same problem.
  3. Users had to rely on the IT staff for Excel support.
  4. Deployment was manual, or at best, via email.
  5. Documentation and source control didn’t exist.

For an IT staff, all these are valid and very real concerns. To address these issues, we preferred a pure .Net solution, outside of Excel, if we had a choice (mainly due to point 3 and 4 above which existed with VSTO back then). Although, we did have to resort to using VSTO for quite a bit of functionality.

For the most part, everyone was happy. But, as developers supporting front office operations know from experience, there’s almost a daily need to provide business with quick “reports” or other analytics in Excel. We simply didn’t have the luxury to push back with a half-a-day or a day requirement to develop the feature before testing and deployment. This is where VBA made sense for us.

We developed a library of common functionality which we referenced from a workbook. Most of the times, deploying a new report was simply a matter of tweaking the stored proc name or the sql passed to the addin. This allowed us to respond mostly within an hour to meet business needs.

We still had to rein this beast in, though. So we managed a catalog of workbooks sent to the desk by portfolio and by feature. Anytime we came across more than 1 version of the workbook deployed, we would migrate that functionality to .Net application and then ask the user to switch from Excel to .Net.

So, all in all, I do think that VBA is still a powerful tool that can be useful in the financial market, especially when paired with how comfortable traders and analysts are with Excel. But one needs to watch out for going overboard with deploying too many VBA based solutions without porting them to .Net whenever possible.

Categories: Office