Red Green Repeat Adventures of a Spec Driven Junkie

Translating Code from Ruby to Visual Basic

This week, I had to work with data in Excel. The main problem: I received two columns of data, let’s say the columns are: profile_name and government_name. The former would be the name entered in one’s profile on a social networking service. The latter, the name on one’s government issued identification to verify your identity.

profile_name government_name
AndrewLeung Andrew Leung
MaxHax0rs Jeff K
Brian Smith  

The problem is sometimes people fill the government_name field and different from the profile_name field. If government_name is blank, 80% of the time, the profile_name would suffice.

The problem was trying to do this in Excel. I know Excel has a full programming language, Visual Basic, but I’m just not versed in it.

In Ruby

The solution is obvious:

government_name || profile_name

Of course, I do not have access to Ruby within Excel and this isn’t such a big job that warrants an export and import with the CSV gem, I have done it by hand before.

With little time and didn’t want to think on each and every single attendee. I wanted a straight up: If government_id is present, use it, otherwise, use profile_name.

Excel Solution

I knew a bit of Excel, so I searched around for a solution. The ultimate solution I went with is:

=IF(B2<>"", B2, A2)

Meaning in VBA:

VBA Meaning Ruby
IF If operator if
<> not equal to !=
"" blank nil
B2 government name field rsvp_response
A2 profile name field name

To me, the VBA syntax is not immediate obvious, as I don’t work with it daily but once broken down, it does make sense. After finding the equivalent primitives it is just a simple translation from what I do know.

Once I got this translation, it was trivial to work out the code to solve my problem: if the government name field as a value, use it, otherwise, use the name field.

The nice thing about doing it in Excel: there’s no loops involved. Once I wrote out the code for one row, I just replicated for all the other rows to apply the same code on and Excel adjusted the row count in the code, changing A2 to A3, B2 to B3.


In this case, know a little bit of Visual Basic made things easier. I love Ruby, but there are other programming language that are more than capable.

It’s nice to get out of my comfort zone and try solving problems with different tools. It was interesting to see what carried over from Ruby and what doesn’t.