VBA to Google Apps Script Roadmapper project download You 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 CaseGenerally speaking, the VBA will already be conforming to the javaScript case convention, see Learning javaScript, since that is what I tend to use.
The exception will be for emulation of VBA built in functions, where we will use the standard case, for example LCase(s)
VBA built in functionsWhere possible, VBA built in functions will be mimicked in javaScript. Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.
Here is an example of this, and it also shows that the orignal VBA case will be preserved to minimize vba-javaScript code changes
Normalizing behaviorHacks 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
function fixOptional (arg, defaultValue) { if (isUndefined(arg) ){ if (isUndefined(defaultValue) ) MsgBox ('programming error: no default value for missing argument'); return defaultValue; } else return arg; } function isUndefined ( arg) { return typeof arg == 'undefined'; }
eNumsThese don't exist in js, so we'll simulate all enums in the enumHacks script, Here's an example
var eSort = Object.freeze({'eSortNone':0, 'eSortAscending':1, 'eSortDescending':2}) Excel built in objectsInitially 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.
Range.prototype.value 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.
ClassesAlthough 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
Private pParent As cDataRow ' cDataRow to which this belongs Public Property Get parent() As cDataRow Set parent = pParent End Property I could preserve the readonly nature like this
cCell = function() { var pParent = 0; this.parent = function() { return pParent; } } and define read/write properties like this cCell = function() { this.value = 0; } The drawback would be that in the first case, things that used to be properties will need to be referenced like methods. For more discussion on this see javaScript functions cc.parent() rather than cc.parent 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.
Final approach for properties versus methodsif we define all the functions inside the class constructor, it means that we could duplicate the VBA method of having private variables that are exposed through public properties (or in the case of javaScript, methods).
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.
cCell.prototype.create = function(par, colNum , rCell, keepFresh, v ) { this.xColumn = colNum; this.column= function() { return this.xColumn; }; this.xParent = par; this.parent= function() { return this.xParent; }; this.xWhere = rCell; this.where= function() { return this.xWhere; }; this.row = function () { this.xParent.row(); }; if (isUndefined(v)) this.refresh(); else this.xValue = v ; return this; }; 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 >