Using jSon to pass arguments in VBA- just like javaScript

Passing arguments in VBA can be a pain. Consider


Of course in some cases you can use named arguments, but the called function needs to know how to do that – it doesn’t work with custom functions.

foo (color:=a,size:=b) etc…

In addition, if you are trying to write code that is dual maintainable in both javaScript (Google Apps Script) and Excel (as in this VBA/GAS conversion program on the ramblings site), you really don’t want to be introducing compiler busting syntax like that.

Thinking about javaScript, where the norm is to pass complicated arguments an object, it occurred to me that I could apply the same thing to VBA using a jSon parsing capability, and since I have implemented the same cJobject class in Google Apps script, the whole thing becomes portable between the 2 environments

Here’s an example in VBA

Private Sub testjSonArgs()
   testjSonArgsSub “{‘age’:40,’gender’:’male’,’job’:’carpenter’,’name’:’fred’}”
End Sub
Private Sub testjSonArgsSub(options As String)
    Dim args As cJobject, arg As cJobject
    Set args = jSonArgs(options)
    For Each arg In args.Children
        Debug.Print arg.key, arg.Value
    Next arg
    Debug.Print “age argument is”, args.child(“age”).Value
End Sub

And here’s the procedure to decode the arguments from jSon

Public Function jSonArgs(options As String) As cJobject
    Dim job As New cJobject
    Set jSonArgs = job.init(Nothing, “jSonArgs”).deSerialize(options)
End Function 

That gives these results

age argument is

The same thing in Google Apps Script, with these libraries implemented gives this result

age|40gender|malejob|carpentername|fredage argument is|40

function testjSonArgsSub(options) {
    var args =
(“age argument is”, args.child(“age”).value());
function testjSonArgs() {

             function jSonArgs(options) {
                return new
cJobject().init(null, “jSonArgs”).deSerialize(options);

When would you use this?
Naturally this introduces an overhead when calling functions, so it’s probably only good when you call a function once or a few times, and then only when there are loads of potential arguments and you only want to pass a few. This requires the cJobject libraries, so it’s probably only useful if you are using these elsewhere in the same project. However it is an interesting idea when considering portability between javaScript and Excel and other platforms and will almost certainlyc lean this up and use it in the rest-Excel libraries where there is lots of optionalism. Why not take a look at the ramblings site for more stuff like this,  contact me on our forum or get involved by commenting on this blog.

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *