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. Using the modules from 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.
ClassesIn Google Apps Script, we can create constructor functions which behave a little like VBA classes. VBA classes are converted as below. Optional and default argumentsOptional 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 codeWhen 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 Writing to a fileSo 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 codeYou'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. Next StepsTranslating 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. Transitioning is covered more comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > From VBA to Google Apps Script >