5g: Self Contained

Description
5g functions must be self contained so they can work in any workbook (as long as the workbook is running Excel 365 or Excel for the Web)

Learning Objectives
At the end of this course, learners will know:

Prerequisites

Discussion

Self contained means that everything the function needs is contained within the function except the values brought in through the function's arguments. This means the function cannot contain any cell references, named references, table references, VBA references, etc.

Functions Arguments: 

Values, names, and cell references can be passed to the component only through function arguments. 

Below are two examples of a LAMBDA. The one of the left is non-compliant because it references cell $B$2 inside the LAMBDA's calculation. The one on the right is compliant because the cell reference has been moved to the component's function arguments.

Here are two more examples, this time, using a LAMBDA helper function: BYROW(). The top example is non-compliant because the help function contains the range reference: $B$32:$F$10. The bottom example is compliant because it wraps the LAMBDA helper function inside a LAMBDA and the LAMBDA passes the range reference from its function arguments to the LAMBDA helper function.

Lab
Make this LAMBDA "Self Contained"

=LAMBDA(Columns,
    LET(Array, SEQUENCE(ROWS( tblItems), Columns),
         SCAN(0,Array,LAMBDA( Acc, Ele, Acc + Ele))
    )
 )(2)

Lab Solution
Moved tblItems to the function's argument inputs.

=LAMBDA(Table, Columns,

    LET(Array, SEQUENCE(ROWS( Table), Columns),

        SCAN(0,Array,LAMBDA(Acc, Ele, Acc + Ele))

    )

 )(tblItems, 2)

Summary

Keep all named ranges, cell references, table references, and user defined functions out of the LAMBDA calculation and internal argument sections.

Navigation