You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download
Case
Generally 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 functions
Where 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
function LCase(s) {
return toLowerCase(s);
}
See GAS hacks for VBA for more of these.
Normalizing behavior
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
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';
}
eNums
These 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})
and we can access them like this eSort.eSortNone;
Excel built in objects
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.
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.
Classes
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
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 methods
if 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.
- Always access a class property through a method. This allows me to use prototyping and also to later allow processing of a property before returning it without needing to retroactively change from a property to a method.
- Minimize the use of private properties to those that I really want to keep private and protected.
- Use a standard naming convention. ie. this.xProperty is exposed through this.property()
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.
Transitioning is covered more comprehensively in my my book, Going Gas – from VBA to Apps script, available All formats are available now from O’Reilly, Amazon and all good bookshops. You can also read a preview on O’Reilly.