NB This question refers to some features like optional LAMBDA arguments and the ISOMITTED function which are only available (at the time of writing) in the Beta channel (more info here)
I am trying to mimic VBA's paramarray in an Excel LAMBDA function, so would like a function:
=ARRAY(arg_1, [arg_2], [arg_3], ...)
...which returns the array {arg_1, arg_2, arg_3, ...} dynamically sized according to however many args were passed.
If I know the number of args, I can just use the choose function like this:
=CHOOSE(SEQUENCE(number_of_args), arg_1, arg_2, arg_3, ...))
But I don't want to pass the number of arguments as a parameter, I want that to be dynamic. One idea was to do a binary search with ISOMITTED(arg_n) to find the first missing argument. But that still hardcodes an upper limit on number of args to my ARRAY function, not to mention creating a nasty binary tree hardcoded.
This is of course easy with vba:
Public Function ARRAYFROMARGS(ParamArray args()) As Variant
ARRAYFROMARGS = args
End Function
Although this can only accept values which can be coerced into Variants so not lambdas or linked data types for example. This makes the non-VBA version more flexible.
={1,2,3}works. I want={A1,A5,B4,"foo",Bar"}type of thing. I was hoping to avoid manually parsing a stringified version because that would require escaping all the arguments, and some datatypes can't be faithfully stringified=PRINTF("Some text {1}, more text {2}", ARRAY("foo", A1))whereARRAYtakes a variable number of arguments and wraps them into an array to pass to PRINTF which expects only two args; a string mask, and a dynamic array of tokens like this example function does. Does that clarify?O(log n)rather thanO(n/2)complexity, so it runs pretty fast. Now you can make an ARRAY out of anything:) But hopefully someone can see an improvement somewhere