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 Microsoft's 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.
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. ).
CStr()
Mid()
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.
Mid(x, 1, 2)
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.
forEach()
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); } );
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())
mcpher.CStr()
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();
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). Here's 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.
