Automatic VBA to apps script skeleton

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.

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

This gives the rather more verbose

//module mashUp skeleton created by excelLiberation@ramblings.mcpher.com at 03/04/2013 11:31:40
/**
 * Sub mashForceTweetSentiments
 * return {void}
 */
function mashForceTweetSentiments () {

/*--the code--
Public Sub mashForceTweetSentiments()
    Dim nodesLinks As cNodesLinks, dsParam As New cDataSet, dsOptions As New cDataSet
    Const mashOut = "mashOut"
    Const params = "d3allParameters"
   ' get parameters
    dsParam.populateData wholeSheet(params), , , True, "force"
    dsOptions.populateData wholeSheet(params), , , True, "mash options"
    'do the rest query series and populate a sheet
    mashRest mashOut
    'create the nodelink structure
    Set nodesLinks = mashNodesLinks(mashOut, dsOptions)
    ' generate a d3.js force chart
    mashD3Force dsParam, nodesLinks.jObject
End Sub
*/

}
/**
 * Sub mashRest
 * @param {string} mashOut
 * return {void}
 */
function mashRest (mashOut) {

/*--the code--
Public Sub mashRest(mashOut As String)

    ' get what queries we're going to do
    Dim dsMashList As New cDataSet, cr As cRest, dr As cDataRow, _
        dsMashout As New cDataSet
    
    With dsMashList.populateData(wholeSheet("mashqueries"))
    ' do each query against tweetsentiments and append results
        For Each dr In .rows
            Set cr = restQuery(mashOut, dr.cell("rest entry").value, _
                dr.cell("query").value, , , , , , , , , , , , dr.row <> 1, "query")
        Next dr
    End With
    
End Sub
*/

}
/**
 * Function mashNodesLinks
 * @param {string} mashOut
 * @param {cDataSet} [optDsOptions=]
 * return {cNodesLinks}
 */
function mashNodesLinks (mashOut,optDsOptions) {
    var dsOptions = (typeof optDsOptions == 'undefined' ? undefined : optDsOptions );

/*--the code--
Public Function mashNodesLinks(mashOut As String, Optional dsOptions As cDataSet) As cNodesLinks

    ' get what queries we're going to do
    Dim dr As cDataRow, _
        dsMashout As New cDataSet, ctag As cTagCloud, a As Variant, _
        cj As cJobject, nodesLink As cNodesLinks, i As Long, s As String, _
        sentiment As String, j As Long, sArray() As String, sQuery As String, group As Long, cc As cCell

    ' we'll use the noise clearing abilities of ctagcloud for this part
    Set ctag = New cTagCloud
    ctag.init
    
    Set nodesLink = New cNodesLinks
    nodesLink.init dsOptions
    ' we'll create an object that goes
    ' tag,count,row,sentiment
    With dsMashout.populateData(wholeSheet(mashOut))
        ' lets add nodes for each query
        nodesLink.addNode getSentimentText(-1), getSentimentGroup(-1)
        nodesLink.addNode getSentimentText(1), getSentimentGroup(1)
        group = 3
        For Each cc In .column("query").uniqueValues()
            nodesLink.addNode cc.toString, group
            group = group + 1
        Next cc

        
        For Each dr In .rows
            ' lets ignore neutral sentiments
            If dr.cell("sentiment").value <> 0 Then

            ' set up the sentiment text &amp; query
                sentiment = getSentimentText(dr.cell("sentiment").value)
                sQuery = dr.cell("query").toString
                
                ' inc. counts for these
                nodesLink.addNode sentiment
                nodesLink.addNode sQuery
                nodesLink.addLink sQuery, sentiment

                ' clean up the sentence and add the required nodes
                a = Split(rxReplace("singlespace", dr.cell("text").toString, "$1"), " ")
                If (LBound(a) <= UBound(a)) Then
                    ReDim sArray(LBound(a) To UBound(a))
                    For i = LBound(sArray) To UBound(sArray)
                        sArray(i) = ctag.cleanNoise(CStr(a(i)))
                        If (sArray(i) <> vbNullString) Then
                            nodesLink.addNode sArray(i), getSentimentGroup(dr.cell("sentiment").value)
                        End If
                    Next i

                    For i = LBound(sArray) To UBound(sArray)
                        If (sArray(i) <> vbNullString) Then
                            ' link them
                            nodesLink.addLink sArray(i), sentiment
                            nodesLink.addLink sArray(i), sQuery
                        End If
                    Next i
                End If
            End If
        Next dr
    End With
    Set mashNodesLinks = nodesLink
End Function

Private Function getSentimentText(n As Long) As String
    If n = -1 Then
*/

}
/**
 * Function getSentimentText
 * @param {number} n
 * return {string}
 */
function getSentimentText (n) {

/*--the code--
Private Function getSentimentText(n As Long) As String
    If n = -1 Then
        getSentimentText = "negative sentiment"
    ElseIf n = 1 Then
        getSentimentText = "positive sentiment"
    ElseIf n = 0 Then
        getSentimentText = "neutral sentiment"
    Else
        MsgBox ("Unknown sentiment value:" &amp; n)
        getSentimentText = "neutral sentiment"
    End If
End Function

*/

}
/**
 * Function getSentimentGroup
 * @param {number} n
 * return {number}
 */
function getSentimentGroup (n) {

/*--the code--
Private Function getSentimentGroup(n As Long) As Long
     If n = -1 Then
        getSentimentGroup = 0
    ElseIf n = 1 Then
        getSentimentGroup = 1
    ElseIf n = 0 Then
        getSentimentGroup = 2
    Else
        MsgBox ("Unknown sentiment value:" &amp; n)
        getSentimentGroup = 1
    End If
End Function

*/

}

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 &amp; ".html"
    
    If openNewHtml(fn, toGas(projectsToJobject(Array(module)), True)) Then
        MsgBox ("GAS skeleton is in the file " &amp; fn)
    Else
        MsgBox ("failed to create " &amp; 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
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 &amp; ".html"
    
    If openNewHtml(fn, toGas(projectsToJobject(Array(module)), True)) Then
        MsgBox ("GAS skeleton is in the file " &amp; fn)
    Else
        MsgBox ("failed to create " &amp; fn)
    End If
End Sub
Public Function toGas(job As cJobject, Optional theCodeAsWell As Boolean = False) As String
    Dim jProject As cJobject, jModule As cJobject, jProcedure As cJobject, jargument As cJobject
    Dim theScript As cStringChunker, t As cStringChunker, a As cStringChunker, o As cStringChunker
    Dim doingClass As Boolean, className As String, theCode As Collection, p As cVBAProject
    Set theScript = New cStringChunker
    Set t = New cStringChunker
    Set a = New cStringChunker
    Set o = New cStringChunker
    
    ' we may need the codebase as well
    If theCodeAsWell Then
        Set theCode = getClasses()
    End If
    
    ' convert to apps script skeleton

    For Each jProject In job.child("projects").children
        For Each jModule In jProject.child("project.modules").children
            className = jModule.toString("module.name")
            theScript.add("//module ") _
                .add(className) _
                .add(" skeleton created by excelLiberation@ramblings.mcpher.com at ") _
                .addLine (Now())
            doingClass = jModule.child("module").toString("kind") = "ClassModule"

            If doingClass Then
                ' add a constructor
                theScript.addLine("/**").add(" * @class ") _
                    .addLine(jModule.toString("module.name")) _
                    .addLine (" */")
                theScript.add("function ").add(className) _
                    .addLine(" () {").addLine("    return this;").addLine ("}")
            End If
            For Each jProcedure In jModule.child("module.procedures").children
                With jProcedure.child("procedure")
                    ' the docs
                    theScript.addLine("/**") _
                        .add(" * ").add(.toString("kind")).add(" ") _
                        .addLine (.toString("name"))

                    ' the code
                    t.clear
                    a.clear
                    o.clear
               
                    If doingClass Then
                        t.add(className).add(".prototype.").add(.toString("name")) _
                            .add (" = function(")
                    Else
                        t.add("function ").add(.toString("name")).add (" (")
                    End If
                    For Each jargument In .child("arguments").children
                        With jargument.child("argument")
                            theScript.add(" * @param {").add(jsType(.toString("argtype"))).add ("} ")
                            If (.child("optional").value) Then
                                theScript.add("[").add (optName(.toString("name")))
                                o.addLine optFix(.toString("name"), .child("default").value, jsType(.toString("argtype")))
                                If (Not IsEmpty(.child("default").value)) Then
                                    theScript.add("=").add(.toString("default")).addLine ("]")
                                End If
                                a.add(optName(.toString("name"))).add (",")
                            Else
                                With .child("name")
                                    theScript.addLine (.toString)
                                    a.add(.toString).add (",")
                                End With
                            End If
                            
                        End With
                    Next jargument

                    t.add(a.chopWhile(",").content).addLine(") {").add (o.content)
                    If theCodeAsWell Then
                        t.addLine("").addLine("/*--the code--") _
                            .addLine(getTheCode(jProcedure, theCode)) _
                            .addLine("*/").addLine ("")
                    End If
                    t.add ("}")
                    If doingClass Then t.add (";")
                    t.addLine ("")
                    theScript.add(" * return {").add(jsType(.toString("returns"))) _
                        .addLine("}").addLine(" */").add (t.content)
                End With
            Next jProcedure

        Next jModule
    Next jProject
    tearDownProjects theCode
    Set theCode = Nothing
    toGas = theScript.content

End Function
Public Function getTheCode(job As cJobject, theCode As Collection) As String
    ' given a job object, find the corresponding code in the procedure.
    Dim jo As cJobject, jModule As cJobject, jProject As cJobject
    Set jModule = job.parent.parent
    Set jProject = jModule.parent.parent.parent
    
    getTheCode = theCode(jProject.toString("name")) _
        .modules(jModule.toString("name")) _
        .procedures(job.toString("procedure.name") &amp; "-" &amp; job.toString("procedure.kind")) _
        .getTheCode

End Function
Private Function optName(a As String) As String
    Dim b As String
    b = UCase(left(a, 1))
    If Len(a) > 1 Then b = b &amp; Mid(a, 2, Len(a) - 1)
    optName = "opt" &amp; b
End Functio
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 " &amp; n &amp; " = (typeof " &amp; o &amp; " == 'undefined' ? " &amp; d &amp; " : " &amp; o &amp; " );"
    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 .