VBA to Google Apps Script Roadmapper project downloadYou can find the code (its a work in progress) and test data in the
To minimize the amount of work in converting from VBA to Google Apps Script, we need some rules. Here are the guidelines I'm going to follow
The exception will be for emulation of VBA built in functions, where we will use the standard case, for example LCase(s)
See GAS hacks for VBA for more of these.
Hacks to normalize behavior, provide missing capabilities will be defined in the gaHacks script. Here's and example of a hack to help with Dealing with optional arguments
These don't exist in js, so we'll simulate all enums in the enumHacks script, Here's an example
and we can access them like this eSort.eSortNone;
Initially my thought was that this would be simulated by new classes, with mirror properties and methods and would be stored in the excelObjectHacks script. However, there are some insurmountable problems here, since some google apps script objects have the same name as Excel - for example Range(). I looked into extending the Range.protoype to match the properties and methods of the Excel object but firstly there could be a match somewhere, and secondly, the Google Apps Range constructor does not seem to be exposed.
So for example, this causes a compile error of Cannot read property "prototype" from undefined.
This means that I will have to manually convert all Excel Objects to their equivalents, and some properties will need to be treated as methods. In the end this is probably a good thing as it forces the abstraction of provider specific objects.
Although strictly speaking, i should retain the readonly nature of some of the properties in each class, it would mean more conversion of properties to functions.
Consider this VBA example, which keeps a private copy of pParent as a readonly property
I could preserve the readonly nature like this
and define read/write properties like this
I would also need to define the entire class and all its methods inside a single function, rather than use prototypes, if I wanted to have access to the private version of the variables.
However this would mean that the code for each function would be duplicated for each instance of the class. With the prototype approach, there is only one copy of the code shared by each instance. This might not matter too much in the case where you only have a few class instances, but this project can generate a class instance for each cell in the spreadsheet. Using a mixed mode would mean that I would generate coding errors all over the place so I decided on these simple rules, which the example given illustrates.
For more like this, see From VBA to Google Apps Script . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > From VBA to Google Apps Script >