Search this site

Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson

Automatic VBA to GAS 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 GAS (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.

generated skeleton from VBA

Classes

In Google Apps Script, we can create psuedo classes. VBA classes are converted as below.

generated skeleton from VBA

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

generated skeleton from VBA


Writing to a file

So far we've used the clipboard to transfer between VBA and GAS. I find this much more convienient, 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.

VBA code to generate GAS skeletons

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 . Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.



Comments