Excel LAMBDA: parameter checks and function validation

It turns out that if we pass an Excel lambda function as a parameter to another Excel lambda function, we can’t then test that the function passed into that parameter is from a list of allowed functions.

Calling the wrapper lambda with any of the other three functions defined below as its sole parameter will cause a #VALUE! error. This is because we can’t use a lambda in the SWITCH function in this way and we can’t use a lambda as an operand with the equals operator.

this_function = LAMBDA("This function"); 
that_function = LAMBDA("That function"); 
another_function = LAMBDA("Another function"); 

wrapper =LAMBDA(fn, 
    LET( 
        check1 , OR( fn=this_function , fn = that_function , fn = another_function ), 
        check2 , SWITCH( fn , this_function , TRUE , that_function , TRUE , another_function , TRUE ), 
        IF(AND(check1,check2),fn(),NA())
    ) 
);

Because of this, if we want to validate which function was passed as a parameter, we need some way of determining the name of the function from the function itself.

If you’d like to get a better understanding of the above, please watch this video (8m):

To handle this going forward, I’ve created a simple namespace called META:

DATA = LAMBDA(name, description, author, 
    HSTACK(
        META.TITLES, 
        VSTACK(
            name,
            description,
            author))
    );

TITLES = {"name";"description";"author"};

As you can see, it’s incredibly simple. It contains:

• A named array TITLES containing three row-headers
• A function called DATA which horizontally stacks the META.TITLES with the vertically stacked parameters passed into the function:
  • name – the name of some object (usually a function)
  • description – the description of the object
  • author – the author of the object

In the example shown in the video, we can use META.DATA like this:

//gets a list of 'periods' months, starting at 'start'
month_list = LAMBDA(periods,start,[md],
        LET(
            _md , IF(ISOMITTED(md),FALSE,md) ,
            _metadata , META.DATA("month_list","Get a list of months","Owen Price") ,
            IF(_md, _metadata , DATE(YEAR(start),SEQUENCE(INT(periods)+1),DAY(start)) )
        )
        
);

In the example above, if we pass TRUE to the third argument on month_list, the function will return a two-column, three-row array. Like this:

So regardless of the values passed to parameters 1 and 2, if we pass TRUE to parameter 3, the function will return the specified metadata. Further, we can then easily retrieve the function name with INDEX. 

This is of course just one way of solving for this issue. I like this way because rather than only including the function name, it allows me to extend the fields returned by metadata quite easily in case I need to in the future.

After adding the META namespace as defined above, the example at the top of this post can be changed so that the validation in the wrapper function produces the desired result.

author = "Owen Price";
desc = "It's a function";

this_function = LAMBDA([md],
    LET(
        _md,IF(ISOMITTED(md),false,md),
        _metadata,META.DATA("this_function",desc,author),
        IF(_md,_metadata,"This function")
    )
); 
that_function = LAMBDA([md],
    LET(
        _md,IF(ISOMITTED(md),false,md),
        _metadata,META.DATA("that_function",desc,author),
        IF(_md,_metadata,"That function")
    )
); 
another_function = LAMBDA([md],
    LET(
        _md,IF(ISOMITTED(md),false,md),
        _metadata,META.DATA("another_function",desc,author),
        IF(_md,_metadata,"Another function")
    )
); 

wrapper =LAMBDA(fn, 
    LET( 
        fn_name , INDEX(fn(TRUE),1,2) ,
        check1 , OR( fn_name = "this_function" , fn_name = "that_function" , fn_name = "another_function" ), 
        check2 , SWITCH( fn_name , "this_function" , TRUE , "that_function" , TRUE , "another_function" , TRUE ), 
        IF(AND(check1,check2),fn(),NA())
    ) 
);

If this is useful to you, please feel free to take the code for the META namespace from above and use it as you see fit.

If you have any questions or suggestions for alternate ways of validating function parameters, please drop me a comment below and let’s chat about it.

Cheers!