Passing complex arguments with default values in VBA – like jQuery $.extend()

In passing arguments in VBA using jSON I covered how you could use javaScript Object syntax to pass complex arguments to VBA procedures. Today I’ll cover how to pass complex arguments where there are optional values.

Named arguments in VBA
Some built in functions allow you to use named arguments, and avoid this kind of thing where you have to count commas in examples like this
   foo (a,b,,,,,,,,,,,d)

by using named arguments like this
   foo (arg1:=a, arg2:=b,lastarg:=d)

This is all good stuff, but I’m not aware how you can set that up for your own functions. Maybe you can but I don’t know how. The other problem is that if you want to write code that will be easily translatable to Google Apps Script (and maybe future versions of office), you want to minimize the syntactical differences

Default options in VBA
It’s very straight forward to provide a default value in VBA, but since the default values are buried in the functions themselves it’s hard to set up a framework of default values – for example for some kind of template.
   foo (a as string,b as string,optional x as long = 23)

Learning from jQuery
Those of you who use jQuery will be familiar with the $.extend() function. This is a way to combine default arguments with given arguments to create an object with all the options intended for a given function. Here’s an implementation of that for VBA

This will extend out some given options with their default value, then display the finally selected options


width          90 
height         30 
color         blue

Since the arguments have to be decoded, this is definitely not as efficient as a regular function call in VBA. Neither can it be as strongly typed. However, these kind of functions with lots of potential arguments are generally only called once to set up options to be used for later processing – just like in javaScript. For example here is the call stack for the excel/GAS rest library which could certainly do with this kind of treatment.

How does it work ?
It uses the cJobject ability to deserialize jSon into a structured object to contain the options. The code is very straightforward  (included in cDataSet.xlsm downloadable from the ramblings site)

I haven’t implemented the ability to pass arrays, or to specify options below the root level yet (for example)

But this is a relatively straightforward enhancement which I may add later if there is sufficient interest or I have the need for it myself.

For more stuff like this, visit the ramblings site or the associated blog. If you have suggestion for particular topic or contact me on our forum


About brucemcp 223 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.

1 Comment

Leave a Reply

Your email address will not be published.


5 × five =