Using the modules from Automatic documentation, here’s how to create a skeleton Google Apps Script Module directly from VBA modules or classes. It will also create basic JSDOC markup for documenting your new GAS module. You’ll find all you need in the cDataSet.xlsm workbook in the classSerializer module. For automatic updating, use the same manifest as automatic documentation.
How does it work?
First you have to set up your Excel workbook so that the code can be read by VBA, as described in Automatic documentation. A small module like the one below, will then create a GAS skeleton module in the clipboard, ready for pasting into the GAS IDE. In this case we are taking one class called “cStringChunker”. You can do many modules/classes at once separated by commas
Public Sub gasToClip() ' this will create a google apps script skeletong for selected modules toClipBoard toGas(projectsToJobject(Array("cStringChunker"))) MsgBox ("GAS skeleton is in the clipboard") End Sub
You can then paste the generated code into a new apps script (or JavaScript) module. All the JavaScript code you see here has been automatically created from VBA.
What does the generated code look like?
Here’s a small module with the starter skeleton pasted in.
//module testSankey skeleton created by excelLiberation@ramblings.mcpher.com at 02/04/2013 23:04:41 /** * Sub testSan * return {void} */ function testSan () { } /** * Sub testThisPartition * return {void} */ function testThisPartition () { } /** * Sub makeD3Partition * @param {string} params * @param {string} [optSn= vbNullString] * @param {string} [optBanner= vbNullString] * return {void} */ function makeD3Partition (params,optSn,optBanner) { var sn = (typeof optSn == 'undefined' ? '' : optSn ); var banner = (typeof optBanner == 'undefined' ? '' : optBanner ); } /** * Function makeAndOpen * @param {string} fn * @param {string} content * @param {boolean} [optComplain= True] * return {boolean} */ function makeAndOpen (fn,content,optComplain) { var complain = (typeof optComplain == 'undefined' ? true : optComplain ); } /** * Sub testD3Tree * return {void} */ function testD3Tree () { } /** * Sub testThisD3Tree * return {void} */ function testThisD3Tree () { } /** * Function getTreeAsJson * @param {string} params * @param {string} data * @param {cDataSet} dsOptions * @param {cDataSet} dsParam * @param {string} item * @param {string} options * return {string} */ function getTreeAsJson (params,data,dsOptions,dsParam,item,options) { } /** * Sub makeD3Tree * @param {string} params * @param {string} data * @param {string} [optBanner= vbNullString] * return {void} */ function makeD3Tree (params,data,optBanner) { var banner = (typeof optBanner == 'undefined' ? '' : optBanner ); }
Classes
In Google Apps Script, we can create constructor functions which behave a little like VBA classes. VBA classes are converted as below.
//module cStringChunker skeleton created by excelLiberation@ramblings.mcpher.com at 02/04/2013 21:59:01 /** * @class cStringChunker */ function cStringChunker () { return this; } /** * Get size * return {number} */ cStringChunker.prototype.size = function() { }; /** * Get content * return {string} */ cStringChunker.prototype.content = function() { }; /** * Get getLeft * @param {number} howMany * return {string} */ cStringChunker.prototype.getLeft = function(howMany) { }; /** * Get getRight * @param {number} howMany * return {string} */ cStringChunker.prototype.getRight = function(howMany) { }; /** * Get getMid * @param {number} startPos * @param {number} [optHowMany= -1] * return {string} */ cStringChunker.prototype.getMid = function(startPos,optHowMany) { var howMany = (typeof optHowMany == 'undefined' ? -1 : optHowMany ); }; /** * Get self * return {cStringChunker} */ cStringChunker.prototype.self = function() { }; /** * Function clear * return {cStringChunker} */ cStringChunker.prototype.clear = function() { }; /** * Function add * @param {string} addString * return {cStringChunker} */ cStringChunker.prototype.add = function(addString) { }; /** * Function addLine * @param {string} addString * return {cStringChunker} */ cStringChunker.prototype.addLine = function(addString) { }; /** * Function insert * @param {string} [optInsertString= " "] * @param {number} [optInsertBefore= 1] * return {cStringChunker} */ cStringChunker.prototype.insert = function(optInsertString,optInsertBefore) { var insertString = (typeof optInsertString == 'undefined' ? " " : optInsertString ); var insertBefore = (typeof optInsertBefore == 'undefined' ? 1 : optInsertBefore ); }; /** * Function overWrite * @param {string} [optOverWriteString= " "] * @param {number} [optOverWriteAt= 1] * return {cStringChunker} */ cStringChunker.prototype.overWrite = function(optOverWriteString,optOverWriteAt) { var overWriteString = (typeof optOverWriteString == 'undefined' ? " " : optOverWriteString ); var overWriteAt = (typeof optOverWriteAt == 'undefined' ? 1 : optOverWriteAt ); }; /** * Function shift * @param {number} [optStartPos= 1] * @param {number} [optHowManyChars= 0] * @param {string} [optReplaceWith= vbNullString] * return {cStringChunker} */ cStringChunker.prototype.shift = function(optStartPos,optHowManyChars,optReplaceWith) { var startPos = (typeof optStartPos == 'undefined' ? 1 : optStartPos ); var howManyChars = (typeof optHowManyChars == 'undefined' ? 0 : optHowManyChars ); var replaceWith = (typeof optReplaceWith == 'undefined' ? vbNullString : optReplaceWith ); }; /** * Function chop * @param {number} [optN= 1] * return {cStringChunker} */ cStringChunker.prototype.chop = function(optN) { var n = (typeof optN == 'undefined' ? 1 : optN ); }; /** * Function chopIf * @param {string} t * return {cStringChunker} */ cStringChunker.prototype.chopIf = function(t) { }; /** * Function chopWhile * @param {string} t * return {cStringChunker} */ cStringChunker.prototype.chopWhile = function(t) { }; /** * Function maxNumber * @param {number} a * @param {number} b * return {number} */ cStringChunker.prototype.maxNumber = function(a,b) { }; /** * Function minNumber * @param {number} a * @param {number} b * return {number} */ cStringChunker.prototype.minNumber = function(a,b) { }; /** * Function adjustSize * @param {number} needMore * return {cStringChunker} */ cStringChunker.prototype.adjustSize = function(needMore) { }; /** * Sub class_initialize * return {void} */ cStringChunker.prototype.class_initialize = function() { };
Optional and default arguments
Optional arguments have their name changed, with an opt… prefix. A small piece of code is generated to populate the argument with their default values if they are not given. In other words the construct optional arg as type = "default"
is simulated as per the example below.
/** * Function shift * @param {number} [optStartPos= 1] * @param {number} [optHowManyChars= 0] * @param {string} [optReplaceWith= vbNullString] * return {cStringChunker} */ cStringChunker.prototype.shift = function(optStartPos,optHowManyChars,optReplaceWith) { var startPos = (typeof optStartPos == 'undefined' ? 1 : optStartPos ); var howManyChars = (typeof optHowManyChars == 'undefined' ? 0 : optHowManyChars ); var replaceWith = (typeof optReplaceWith == 'undefined' ? '' : optReplaceWith ); };
Getting the code
When translating to GAS, it’s useful to have the original code as a block comment. Aside from being useful to refer to it in the GAS code, it often needs minimal editing (especially when using Google Apps Script VBA equivalents library). You can ask for the original code to be included as code block, like this – the True means to include the code.
This gives the rather more verbose
Writing to a file
So far we’ve used the clipboard to transfer between VBA and GAS. I find this much more convenient, but you may want to create a file instead. A small tweak will write the content to a file instead
Public Sub gasToFile() ' this will create a google apps script skeletong for selected modules to a file Dim module As String, fn As String module = "cStringChunker" fn = module & ".html" If openNewHtml(fn, toGas(projectsToJobject(Array(module)), True)) Then MsgBox ("GAS skeleton is in the file " & fn) Else MsgBox ("failed to create " & fn) End If End Sub
The code
You’ll find all you need in the cDataSet.xlsm workbook in the classSerializer module. For automatic updating, use the same manifest as automatic documentation.
Here’s the main code for doing this – or you can find it in the gist library noted below.
Public Sub gasToClip() ' this will create a google apps script skeletong for selected modules to the clipboard toClipBoard toGas(projectsToJobject(Array("mashUp")), True) MsgBox ("GAS skeleton is in the clipboard") End Sub
Private Function jsType(n As String) As String Select Case n Case "String" jsType = "string" Case "Double", "Single", "Long", "Integer" jsType = "number" Case "Boolean" jsType = "boolean" Case "Variant" jsType = "*" Case Else jsType = n End Select End Function
Private Function optFix(n As String, v As Variant, t As String) As String Dim a As String, d As String, o As String o = optName(n) d = v d = Trim(d) If t = "string" Then 'special stuff If d = "vbNullString" Then d = "''" End If ElseIf t = "boolean" Then d = LCase(d) Else If d = "Nothing" Then d = "null" End If End If If (d = vbNullString) Then d = "undefined" a = " var " & n & " = (typeof " & o & " == 'undefined' ? " & d & " : " & o & " );" optFix = a End Function
Next Steps
Translating code is beyond the scope of what I’m trying to achieve here, but this gives good start for the boring parts. I’ll see how else this can be enhanced over time.
For more like this, see From VBA to Google Apps Script .