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.
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
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). 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.
4 comments :
Hi there,
I'm a blogspot blogger who just upgraded to a custom domain. I've been on the blogger forum, google forums and have posted my concerns everywhere I can. NO ONE WANTS TO HELP. Your product is BROKEN!!!!! It sure seems like you don't know and/or don't care. 48 hours of known issues is NOT confidence building. Would you stop ignoring us and post an update on the known issues blog???? HOW LONG till we get what we paid for? Respect goes a long way in customer service. I most definitely do NOT feel respected.
Hi Karen,
I'm sorry to hear you are having issues and aren't getting the help you need. Please continue trying to get support from the Blogger team. I just pinged a mailing list on your behalf, but there's not much more we can do in the context of this blog without going off our dedicated topics in the Apps realm.
Regards,
Eric
Hello Karen,
Thanks for your note. We posted a message about this issue to the Known Issues blog on Sunday September 9:
http://knownissues.blogspot.com.au/
Brett from our Support team also posted a message on the user forum here:
https://productforums.google.com/forum/#!category-topic/blogger/something-is-broken/kB6YzKk7VvE
We're actively working on the issue and will post more information shortly.
Thanks for your patience.
Bruce
Post a Comment