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 .