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)

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

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

Public Sub testOptionsExtend()
    Dim cj As cJobject, jExtend As cJobject
    Const defaultOptions = "{'width':20,'height':30,'color':'red'}"
    Set jExtend = optionsExtend _
        ("{'width':90,'color':'blue'}", defaultOptions)
    
    For Each cj In jExtend.children
        Debug.Print cj.key, cj.value
    Next cj


End Sub

Result

width          90 
height         30 
color         blue

Usage
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.

Public Function init(Optional rData As String = "responsedata.results", _
                     Optional et As erRestType = erQueryPerRow, _
                     Optional hc As cCell = Nothing, _
                     Optional rq As String = vbNullString, _
                     Optional ds As cDataSet = Nothing, _
                     Optional pop As Boolean = True, _
                     Optional purl As String = vbNullString, _
                     Optional clearmissing As Boolean = True, _
                     Optional treesearch As Boolean = False, _
                     Optional complain As Boolean = True, _
                     Optional sIgnore As String = vbNullString, _
                     Optional user As String = vbNullString, _
                     Optional pass As String = vbNullString, _
                     Optional append As Boolean = False, _
                     Optional stampQuery As cCell = Nothing, _
                     Optional appendQuery As String = vbNullString) As cRest

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)

Public Function optionsExtend(givenOptions As String, _
            Optional defaultOptions As String = vbNullString) As cJobject
    Dim jGiven As cJobject, jDefault As cJobject, _
        jExtended As cJobject, cj As cJobject
    ' this works like $.extend in jQuery.
    ' given and default options arrive as a json string
    ' example -
    ' optionsExtend ("{'width':90,'color':'blue'}", "{'width':20,'height':30,'color':'red'}")
    ' would return a cJobject which serializes to
    ' "{width:90,height:30,color:blue}"
    Set jGiven = jSonArgs(givenOptions)
       
    If defaultOptions <> vbNullString Then
        Set jDefault = jSonArgs(defaultOptions)
    End If
    
    ' now we combine them
    Set jExtended = New cJobject
    jExtended.init Nothing
    
    With jExtended
        ' first copy over the top level from the defaults
        If Not jDefault Is Nothing Then
            For Each cj In jDefault.children
                .add cj.key, cj.value
            Next cj
        End If
        ' the .add method has the useful characteristic of
        ' either adding or changing the value if it exists already
        For Each cj In jGiven.children
            .add cj.key, cj.value
        Next cj
    End With
    Set optionsExtend = jExtended
End Function


Public Function jSonArgs(options As String) As cJobject
    ' takes a javaScript like options paramte and converts it to cJobject
    ' it can be accessed as job.child('argName').value or job.find('argName') etc.
    Dim job As New cJobject
    Set jSonArgs = job.init(Nothing, "jSonArgs").deSerialize(options)
End Function

Limitations
I haven’t implemented the ability to pass arrays, or to specify options below the root level yet (for example)
{‘colors’:[‘red’,’green’],dimensions:{‘height’:20,’width’:30}}

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