Trick and Tips: Simplifying Migration from VBA to Google Apps Script

Friday, March 13, 2015

Simplifying Migration from VBA to Google Apps Script

Editor’s Note: Guest author Bruce McPherson is a contributor to the Excel Liberation website and blog. -- Eric Koleda

If you are new to Google Apps Script and the JavaScript programming language, migrating legacy automation code written in Microsofts Visual Basic for Applications (VBA) can be a daunting task. This blog post describes a Google Apps Script library which mimics the behavior and calling structure of common VBA functions, allowing you to more easily convert your existing applications.

Retaining compatibility

If you are planning to continue to use VBA, you can minimize both the work involved in maintaining the same capability in both platforms, and in porting from one to the other, by preserving backwards compatibility with VBA. This means breaking a few JavaScript conventions, but the result is worth it.

For example, JavaScript variables are normally written in lowerCamelCase, with classes being in UpperCamelCase. VBA is not case sensitive, and uses hungarian notation by convention, except for the built-in functions, which have a capitalized first letter. Since the objective here is to minimize change, I have decided to retain this capitalization for VBA functions replacements (for example CStr(), Mid() etc. ).

In VBA, indices (normally) start at 1, while in JavaScript they start at 0. Since these functions are to minimize change in application written in VBA, they also start at 1. For example Mid(x, 1, 2) means the first 2 characters of
string x in both VBA and JavaScript versions.

Enumeration of collections

JavaScript does not have a collection object. The vEquivalents library provides an implementation of a collection class so that continuity for migrated code that relies on the collection can be maintained. But how to enumerate through that collection? There are a number of ways, but the forEach() method of the collection most closely resembles the For Each member in collection approach VBA developers are familiar with. The syntax may seem a little fiddly at first, since it passes the code you want executed against each member of the collection as an anonymous function.

var coll = new collection();
// by index
for (var i=1; i <= coll.count() ;i++) {
  DebugPrint (coll.item(i));
}
// by key
for (k in coll.keys()) {
    DebugPrint (coll.item(k));
}
// forEach 
coll.forEach(
  function (item, index) {
    DebugPrint (item, index);
  }
);

Including vEquivalents in your Google Apps Script Project

With the great new libraries functionality, you can now include these scripts in your project by using the project key "MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j", or you can make a copy of the scripts directly to include in your own project. You will find a selection of other scripts in the library, but the VBA equivalents are all in the module vEquivalents. Note that as you include external libraries in your project (see here for how), you need to prefix the functions with the library identifier (for example mcpher.CStr())

Examples

You can access the documentation here, and you will see that most of the common VBA functions are included. Some examples are

var x = Trim(s);
var x = Len(s);
var a = Split(s);
var x = Instr(1, s1, s2);
var d = DateSerial(y, m, d);
MsgBox(s);
var x = InputBox(s);
DebugAssert (b, s);
var w = ActiveSheet();

Going beyond the built-in VBA functions

Using the same approach, I have converted many other VBA utility classes and procedures built over time and added them to this library. This means that the implementation of something on either platform not only looks the same, but can be accomplished in hours or even minutes. For example, on my blog I publish a daily API, implemented in both VBA and Apps Script (both versions even use ScriptDB for the same parameter data). Heres a recent one.

function testUkPostcodes() {
  mcpher.generalDataSetQuery ("uk postcodes", "uk postcodes", "postcode");
}

Public Sub testUkPostcodes()
  generalDataSetQuery "uk postcodes", "uk postcodes", "postcode"
End Sub

You can find more projects that have been migrated this way here.

Bruce McPherson profile | twitter

Bruce McPherson is a contributor to Excel Liberation website and blog, and advocate for open data.

Trick and Tips