Excel function to construct an array from values without VBA

Question

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.

Answer 1

For Completeness, this is now possible with HSTACK and VSTACK which were not released at the time the post was published. However to define your own variadic function you would need to follow one of the approaches above.

e.g. =printf("{1} {2}!","Hello","World") taking any number of subsequent args is defined like this https://codereview.stackexchange.com/questions/268446/printf1-2-in-excel-without-vba-lambda-functions-with-variable

=LAMBDA(
    mask,
    _0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],
    LET(
        tokensArray,
        FLATARRAY(
            _0, _1, _2, _3, _4, _5, _6, _7, _8, _9, _10, _11, _12, _13, _14, _15, _16, _17, _18, _19, _20, _21, _22, _23, _24, _25, _26, _27, _28, _29, _30, _31, _32, _33, _34, _35, _36, _37, _38, _39, _40, _41, _42, _43, _44, _45, _46, _47, _48, _49, _50, _51, _52, _53, _54, _55, _56, _57, _58, _59, _60, _61, _62, _63, _64, _65, _66, _67, _68, _69, _70, _71, _72, _73, _74, _75, _76, _77, _78, _79, _80, _81, _82, _83, _84, _85, _86, _87, _88, _89, _90, _91, _92, _93, _94, _95, _96, _97, _98, _99, _100, _101, _102, _103, _104, _105, _106, _107, _108, _109, _110, _111, _112, _113, _114, _115, _116, _117, _118
        ),
        escapedResult,
        _ReplaceRecursive(
            mask,
            tokensArray,
            1,
            ROWS(tokensArray)
        ),
        SUBSTITUTE(escapedResult,"\}","}")
    )
)

Answer 2

VSTACK now exists, which fulfils this role.

E.g.,

Answer 3

Well as I alluded to in the question, I've found a way to use a binary search to evaluate how many arguments are missing. It does involve a lot of copy-paste though. So here's code to evaluate the number of args passed by finding the first which is missing:

Tag	Value
Name	ARGSCOUNT
Scope	Workbook
Comment	Use a hardcoded binary search to find the first omitted argument, in chunks of up to 63
Refers To	=LAMBDA([p_1],[p_2],[p_3],[p_4],[p_5],[p_6],[p_7],[p_8],[p_9],[p_10],[p_11],[p_12],[p_13],[p_14],[p_15],[p_16],[p_17],[p_18],[p_19],[p_20],[p_21],[p_22],[p_23],[p_24],[p_25],[p_26],[p_27],[p_28],[p_29],[p_30],[p_31],[p_32],[p_33],[p_34],[p_35],[p_36],[p_37],[p_38],[p_39],[p_40],[p_41],[p_42],[p_43],[p_44],[p_45],[p_46],[p_47],[p_48],[p_49],[p_50],[p_51],[p_52],[p_53],[p_54],[p_55],[p_56],[p_57],[p_58],[p_59],[p_60],[p_61],[p_62],[p_63],IF(ISOMITTED(p_32),IF(ISOMITTED(p_16),IF(ISOMITTED(p_8),IF(ISOMITTED(p_4),IF(ISOMITTED(p_2),IF(ISOMITTED(p_1),0,1),IF(ISOMITTED(p_3),2,3)),IF(ISOMITTED(p_6),IF(ISOMITTED(p_5),4,5),IF(ISOMITTED(p_7),6,7))),IF(ISOMITTED(p_12),IF(ISOMITTED(p_10),IF(ISOMITTED(p_9),8,9),IF(ISOMITTED(p_11),10,11)),IF(ISOMITTED(p_14),IF(ISOMITTED(p_13),12,13),IF(ISOMITTED(p_15),14,15)))),IF(ISOMITTED(p_24),IF(ISOMITTED(p_20),IF(ISOMITTED(p_18),IF(ISOMITTED(p_17),16,17),IF(ISOMITTED(p_19),18,19)),IF(ISOMITTED(p_22),IF(ISOMITTED(p_21),20,21),IF(ISOMITTED(p_23),22,23))),IF(ISOMITTED(p_28),IF(ISOMITTED(p_26),IF(ISOMITTED(p_25),24,25),IF(ISOMITTED(p_27),26,27)),IF(ISOMITTED(p_30),IF(ISOMITTED(p_29),28,29),IF(ISOMITTED(p_31),30,31))))),IF(ISOMITTED(p_48),IF(ISOMITTED(p_40),IF(ISOMITTED(p_36),IF(ISOMITTED(p_34),IF(ISOMITTED(p_33),32,33),IF(ISOMITTED(p_35),34,35)),IF(ISOMITTED(p_38),IF(ISOMITTED(p_37),36,37),IF(ISOMITTED(p_39),38,39))),IF(ISOMITTED(p_44),IF(ISOMITTED(p_42),IF(ISOMITTED(p_41),40,41),IF(ISOMITTED(p_43),42,43)),IF(ISOMITTED(p_46),IF(ISOMITTED(p_45),44,45),IF(ISOMITTED(p_47),46,47)))),IF(ISOMITTED(p_56),IF(ISOMITTED(p_52),IF(ISOMITTED(p_50),IF(ISOMITTED(p_49),48,49),IF(ISOMITTED(p_51),50,51)),IF(ISOMITTED(p_54),IF(ISOMITTED(p_53),52,53),IF(ISOMITTED(p_55),54,55))),IF(ISOMITTED(p_60),IF(ISOMITTED(p_58),IF(ISOMITTED(p_57),56,57),IF(ISOMITTED(p_59),58,59)),IF(ISOMITTED(p_62),IF(ISOMITTED(p_61),60,61),IF(ISOMITTED(p_63),62,63)))))))

_{Easier to copy versions here}

It is called like this =ARGSCOUNT(arg_1, arg_2, ..., arg_63) from an enclosing LAMBDA. Note it takes up to 63 optional arguments because my binary tree is symmetrical so must be a power of 2 (minus 1 for the case of 0 arguments) and named references have a low character limit around 2000. However you can call it more than once from a parent function and sum the result e.g. ARGSCOUNT(arg_1, ..., arg_63) + ARGSCOUNT(arg_64, ..., arg_126)

Then that count can be used in a second LAMBDA function to build the array:

Tag	Value
Name	ARRAY
Scope	Workbook
Comment	Create an array from comma separated arguments, up to 130
Refers To	=LAMBDA(_0,[_1],[_2],[_3],[_4],[_5],[_6],[_7],[_8],[_9],[_10],[_11],[_12],[_13],[_14],[_15],[_16],[_17],[_18],[_19],[_20],[_21],[_22],[_23],[_24],[_25],[_26],[_27],[_28],[_29],[_30],[_31],[_32],[_33],[_34],[_35],[_36],[_37],[_38],[_39],[_40],[_41],[_42],[_43],[_44],[_45],[_46],[_47],[_48],[_49],[_50],[_51],[_52],[_53],[_54],[_55],[_56],[_57],[_58],[_59],[_60],[_61],[_62],[_63],[_64],[_65],[_66],[_67],[_68],[_69],[_70],[_71],[_72],[_73],[_74],[_75],[_76],[_77],[_78],[_79],[_80],[_81],[_82],[_83],[_84],[_85],[_86],[_87],[_88],[_89],[_90],[_91],[_92],[_93],[_94],[_95],[_96],[_97],[_98],[_99],[_100],[_101],[_102],[_103],[_104],[_105],[_106],[_107],[_108],[_109],[_110],[_111],[_112],[_113],[_114],[_115],[_116],[_117],[_118],[_119],[_120],[_121],[_122],[_123],[_124],[_125],[_126],[_127],[_128],[_129],CHOOSE(SEQUENCE(ARGSCOUNT(_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63)+ARGSCOUNT(_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126)+ARGSCOUNT(_127,_128,_129)+1),_0,_1,_2,_3,_4,_5,_6,_7,_8,_9,_10,_11,_12,_13,_14,_15,_16,_17,_18,_19,_20,_21,_22,_23,_24,_25,_26,_27,_28,_29,_30,_31,_32,_33,_34,_35,_36,_37,_38,_39,_40,_41,_42,_43,_44,_45,_46,_47,_48,_49,_50,_51,_52,_53,_54,_55,_56,_57,_58,_59,_60,_61,_62,_63,_64,_65,_66,_67,_68,_69,_70,_71,_72,_73,_74,_75,_76,_77,_78,_79,_80,_81,_82,_83,_84,_85,_86,_87,_88,_89,_90,_91,_92,_93,_94,_95,_96,_97,_98,_99,_100,_101,_102,_103,_104,_105,_106,_107,_108,_109,_110,_111,_112,_113,_114,_115,_116,_117,_118,_119,_120,_121,_122,_123,_124,_125,_126,_127,_128,_129))

---

Nasty I know, but that gives you access to a surprisingly simple function:

=ARRAY(A1, A2, 3, "foo", "bar") // array of anything, dynamically sized

---

BONUS:

You can even create an array of LAMBDAS to pass to something like map:

=MAP(ARRAY(LAMBDA(x, x^2), LAMBDA(y, y+1)),LAMBDA(f, f(3)) // -> {9,4} i.e. 3^2, 3+1

... and the vba ARRAYFROMARGS func can't take LAMBDAS as arguments.

Answer 4

This isn't exactly what you're looking for, but maybe it will give some ideas? If we can take values one at a time as opposed to a list, then rather than having a limit on parameters, we can have an escape character (\) to let the formula know it shouldn't expect anymore values, and make the formula recursive, and leverage MAKEARRAY to extend prior created array by each new entry. Note that for references, the first reference must not cause an error, so needs to be non-blank.

ARRAY
=LAMBDA(val_1,
    LAMBDA(val_2,
        IF(
            TYPE(val_2)=16,val_1,
            ARRAY(
                MAKEARRAY(ROWS(val_1)+1,COLUMNS(val_1),
                    LAMBDA(i,j,
                        IFERROR(INDEX(val_1,i,j),val_2)
                    )
                )
            )
        )
    )
)

Answer 5

If Array1 is defined as:

=CHOOSE(SEQUENCE(number_of_args), arg_1, arg_2, arg_3, ..., arg_n))

where number_of_args is such that number_of_args >= n, then your desired result is derived via:

=INDEX(Array1,SEQUENCE(SUM(1-ISERR(Arry1))))