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

unction testjSonArgsSub(options) {
    var args = jSonArgs(options);
        DebugPrint (arg.key(),arg.value());
    DebugPrint ("age argument is", args.child("age").value());
function testjSonArgs() {
 testjSonArgsSub ('{"age":40,"gender":"male","job":"carpenter","name":"fred"}');
             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 certainly 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.[tweet_button tweet][likebtn style=”github”]

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.