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.
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
40
gender
male
job
carpenter
name
fred
age argument is
40
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);
args.children().forEach(
function(arg){
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”]
