Monday, January 26, 2009

VBA: Alive and... Well?

The future of Visual Basic for Applications (VBA) has been recently called into question. In March 2008, Microsoft dropped their extended support for VB6 and the Microsoft Office 2008 for Mac did not include any VBA functionality.

This is a cause for concern in the applied OR community. In my recent post about the use of Excel for modeling, all four examples relied heavily on VBA. The ability to rapidly develop models using both cell formulas and VBA scripting is invaluable. Taking that a step further, the interface components and widely available platform of Excel are useful when developing end-user tools for clients.

Luckily our fears can be generally put to rest. An MSDN blog article: The Reports of VBA's Demise Have Been Greatly Exaggerated has assured us that:
"[T]he next generation of the Microsoft Office system will definitely contain all of the functionality that developers and power users expect from VBA."
None of this is breaking news if you're on the lookout for it, but if you're an academic director paying close attention to the relevance of your course/project work it's certainly a current issue.

Personally I'm more interested in the questions that all of this raises rather than answers. With Visual Basic (.NET) 9 and C# as viable options for interoperability between general purpose programming and Microsoft Excel, I find the more interesting question to be: Should we be coding tools with VBA?

Ease of development is one thing, but are we really asking too little of ourselves? Sure someone from a non-software development background can develop a subroutine for Excel, but is that really optimal? Personally I think anyone practicing OR should have at some point learned to code in a modern Object Oriented programming language. It's not that difficult and it really maximizes your efficacy. What it takes to teach someone to code pales in comparison to the sheer educational investment necessary to get someone from high school mathematics to Markov Decisions Processes.

With a little effort C# inter-operating with Excel could produce a much "better" solution than VBA. With a modern IDE and a little experience C# can be coded with relative speed. Indeed I did just this when producing a prototype tool for my Masters project. I must admit, though, that deployment did not happen completely without a hitch. Just because I know how to code does not mean I can comfortably shrug off all of the benefits of VBA/Excel including deployment-by-mailed-attachment.

Philosophically matching the weight of the task to the weight of the approach is difficult. It would be irresponsible to code a truely complex application in VBA. Then again, it might be dishonest to dress up a hatchet job of a solution in the guise of an industrial application.

To sum the above three paragraphs up, the part of me that almost took Computer Science as an undergradutae degree fails to find VBA as an acceptably elegant solution. That said the (clearly much louder) part of me that DID take a Business graduate degree sees the silver lining:
  • I read an article by Simon Murphy defending VBA and much of it rang true.
  • Some would promote OpenOffice Calc as an alternative to Excel because it's free and non-proprietary. I would disagree. Excel/VBA solutions are good because their platform is free at the margin. Every organization has, has had and will have Excel, making its continuing use essentially free.
What can I say? The debate will continue to rage on. I find it hard to draw a conclusion for this article, but I will leave our OR readers with a thought: Recall the Simplex algorith. Something doesn't have to be perfect in theory for it to be excellent in practice.

Love it or hate it VBA is here to stay. With its inclusion in current and future MSOffice products (with the exception of Mac 2008, but who cares anyway? ;)) we can safely continue to use it where appropriate. We can continue to teach it to our Masters students as a gold standard of business programming.

1 comment:

Jon Fournier said...

One interesting thing on the deployment side is that Microsoft are pushing the Visual Studio Tools for Office (VSTO) which I believe is supposed to make it a lot easier to build an Excel addin than previously using C# or VB (VB6 or .Net). So it won't be as easy as hitting Alt-F11 in Excel to get to a code editor, but it makes it easier to build, like you said, a more powerful solution than just VBA.

Another thing to note is that using VBA (essentially COM/ActiveX) is painfully slow compared to C, especially in developing user-defined functions. So if you have a large spreadsheet using VBA UDFs then you are going to spend a lot of time watching Excel recalculate.

There's a tutorial on Codematic's website (I think that's Simon Murphy's company, actually) on building XLL addins in C++, which I used to build a library of UDFs that recalculate in less than a quarter second, compared to 30+ seconds as VBA UDFs. Something to think about...

http://codematic.co.uk/Excel-development/Excel-xll/excel-xll.htm