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.

generated skeleton from VBA

Classes

In Google Apps Script, we can create constructor functions which behave a little like VBA 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.

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