Home > Office > Is VBA still relevant?

Is VBA still relevant?

October 31, 2009 Leave a comment Go to comments

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
  1. myharrism
    October 31, 2009 at 10:16 am

    Its akin to a process I experienced whilst designing helicopters for the British military. Yea I know a bit of a diversion…but stay with me. 😉

    Out in the field in training or on a battleground a military engineer would fix a problem under pressure from the pilots. This would be expedited with whatever means the engineer had available. Whilst functional the re-fit was often not maintainable.

    Photographs or drawings of the change would be sent through to our Design office. A solution would be engineered and included in the next version of the aircraft. In addition the aircraft would be re-fitted with the designed solution if possible.

    This process could take up to 10 years though…and therein lies the difference with software.

    What your advocating is almost a prototyping system. Designs are tried with the customer, feedback loops is short, anything that lasts is worth building into the .NET solution.

    I read this the other day and its kind of in the same space:

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: