After lamenting a bit yesterday, I thought I’d share some thoughts on VBA.

VBA, or Visual Basic for Applications, is basically a scripting language for Microsoft products like MS Office. For whatever reason, they cut support for the 2008 version of MS Office on OSX, so I had to wait until I got home to write Linda’s script.

As I booted up her laptop, the Windows 7 lights optimistically flew around. Once in Excel, I created a new macro, and then realized I knew what I wanted to do, but not how to do it.

It’s a problem with jumping back into any language after not using it for some time, but it’s especially difficult to jump back into VBA. If you’ve never used VBA, just know that you’ll start out slowly, incredibly slowly.

I liken it to walking in the mud after a long standstill. You’re ankle deep, and you have to wrench your foot out to take that difficult first step. It takes both hands to even budge your foot and as the mud is sucked into the void where your foot once was, it makes a big squelch. With one foot out, you look back and realize that your other foot sunk even deeper. It’s a struggle, but slowly you end up on top rather than within. Though you’re moving forward, every now and then you’ll sink and find yourself stuck.

The problem with VBA is it’s all object based with layers and layers of objects, and each nugget of information isn’t found where you might expect. Once you figure out where the nuggets are, you can try it in code, but should you fail, the VB editor isn’t exactly helpful in letting you know why you failed (I think that MATLAB’s editor has spoiled me).

I’ll give you an example. Say, you want to calculate the geometric mean of a couple cells. Now, the function GeoMean is built into Excel. That is, if you type in =GeoMean() in a cell, you’ll be able to take the geometric mean. But accessing the GeoMean function in a VBA script is a lot more painful. The code looks like this:

Worksheets("Sheet 1").Cells(1, 1).Value = Application.WorksheetFunction.GeoMean(Worksheets("Sheet 2").Range(Cells(1, 1), Worksheets("Sheet 2").Cells(num_rows, 1)))

It literally pains me to see how long that is.

After a few hours of banging my head against the documentation, I got it to do what I wanted. Oh, and that documentation? It’s a mess. Since the object structure is so convoluted, you’ll be clicking yourself into dead ends and unhelpful examples. Searching for what you want on the MSDN site will take you to even more dead ends, mostly unrelated to your task at hand. The main problem with the search is that you can’t search within specific documents, such as the “Excel 2007 Developer Reference”. Instead, searches pull up results from the entire MSDN website.

And just so this isn’t just a long whining post, I do enjoy how easy it is to throw together a working GUI in VBA. But that’s about all I enjoyed about my coding experience last night.

Advertisements