5G functions for Excel Workbook Information

BXL LAMBDA Workbook Info

/*  FUNCTION NAME:  Aboutλ
    DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/*  REVISIONS:      Date            Developer       Description  
                    Dec 14 2023     Craig Hatmaker  Original Development
*/

Aboutλ = TRIM(TEXTSPLIT(
    "About:                         →BXL's Workbook Information module. Suggested module name: BXW¶" &
    "Version:                       →Jun 12 2023¶" &
    "Gist URL:                      →https://gist.github.com/CHatmaker/3ff1b5ce97344c2dfc3f1623a656676a ¶" &
    "Website:                       →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
    "Notes:                         →These functions have no arguments/parameters¶" &
    "→¶" &
    "Function                       →Description¶" & 
    "Aboutλ	                        →Produces this table¶" & 
    "Pathλ	                        →Retrieves the current saved workbook's path¶" & 
    "Workbookλ	                    →Retrieves the current saved workbook's file name (no path)¶" & 
    "SheetTitleλ	                  →Retrieves the current saved workbook's file name and sheet name ¶" & 
    "Sheetλ	                        →Retrieves the current saved workbook and worksheet's name¶" & 
    "IsBetweenλ	                    →Determine if a value is between a lower and upper limit.¶" & 
    "IsInListλ	                    →Determine if a value is one of a list of items.¶" &
    "RangeToDAλ                     →Convert a static range to a dyanmic array.", 
    "→","¶"
    )
);
    

/*  FUNCTION NAME:  Pathλ
    DESCRIPTION:*/  /**Returns the workbook's Path. Workbook must be saved first*/
/*  REVISIONS:      Date            Developer       Description  
                    Sep 30 2023     Craig Hatmaker  Copyright
*/
Pathλ = 
    LET(FileName,   CELL("filename"),
        Path,   TEXTBEFORE( FileName, "["),
        Path
    );   

/*  FUNCTION NAME:  Workbookλ
    DESCRIPTION:*/  /**Returns the workbook's name. Workbook must be saved first*/
/*  REVISIONS:      Date            Developer       Description  
                    Sep 30 2023     Craig Hatmaker  Copyright
*/
Workbookλ = 
    LET(FileName,   CELL("filename"),
        Workbook,   TEXTBEFORE( TEXTAFTER( FileName, "["), "."),
        Workbook
    );


/*  FUNCTION NAME:  SheetTitleλ
    DESCRIPTION:*/  /**Returns the workbook's name, a dash, and the worksheet's name. 
Workbook must be saved first*/
/*  REVISIONS:      Date            Developer       Description  
                    Sep 30 2023     Craig Hatmaker  Copyright
*/
SheetTitleλ = 
    LET(FileName,   CELL("filename"),
        Worksheet,  TEXTAFTER( FileName, "]"),
        Workbook,   TEXTBEFORE( TEXTAFTER( FileName, "["), "."),
        Result,     Workbook  & "-" &  Worksheet,
        Result
    );


/*  FUNCTION NAME:  Sheetλ
    DESCRIPTION:*/  /**Returns the worksheet's name. 
Workbook must be saved first*/
/*  REVISIONS:      Date            Developer       Description  
                    Sep 30 2023     Craig Hatmaker  Copyright
*/
Sheetλ = 
    LET(FileName,   CELL("filename"),
        Worksheet,  TEXTAFTER( FileName, "]"),
        Worksheet
    );



/*  FUNCTION NAME:      IsBetweenλ
    DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/
/*  REVISIONS:          Date            Developer       Description  
                        Jun 01 2022     Craig Hatmaker  Original Development
                        Apr 12 2023     Craig Hatmaker  Added Help
*/
IsBetweenλ = LAMBDA(
//  Parameter Declarations
    [Value],
    [Low],
    [Hi],
    [Inclusive],
    
//  Help
    LET(Help,           TRIM(TEXTSPLIT( 
                            "DESCRIPTION:   →Determine if a value is between a lower and upper limit.¶" & 
                            "PARAMETERS:    →¶" & 
                            "Value          →(Required) A value or array of values to compare.¶" & 
                            "Lo             →(Required) The lower limit that the value must be greater than¶" &
                            "Hi             →(Required) The lower limit that the value must be less than¶" &
                            "Inclusive      →(Optional) If set to TRUE (default) the value can be equal to Lo and/or Hi¶" &
                            "→¶" &
                            "EXAMPLES       :→¶" & 
                            "Result         →Formula¶" &
                            "FALSE          →=IsBetweenλ(1, 2, 4)¶" & 
                            "TRUE           →=IsBetweenλ(2, 2, 4)¶" & 
                            "FALSE          →=IsBetweenλ(2, 2, 4, FALSE)", 
                            "→", "¶"
                            )
                        ),
    //  Validate Parameters
        Value,          IF(OR(ISOMITTED(Value), VALUE = ""), #VALUE!, Value),
        Low,            IF(OR(ISOMITTED(Low), Low = ""), #VALUE!, Low),
        Hi,             IF(OR(ISOMITTED(Hi), Hi  = ""), #VALUE!, Hi),
        Inclusive,      IF(OR(ISOMITTED(Inclusive), Inclusive = ""),TRUE, Inclusive),
        
    //  Procedure
        Result,         N(
                            IF( Inclusive, 
                                (Value >= Low) * (Value <= Hi) = 1, 
                                (Value > Low) * (Value < Hi) = 1
                            )
                        ),

    //  Handle Error
        Error,          OR( ISERROR( Result)) + 1,

    //  Return Result
        CHOOSE(Error, Result, Help)
    )
);



/*  FUNCTION NAME:      IsInListλ
    DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/
/*  REVISIONS:          Date            Developer       Description  
                        Jun 18 2023     Craig Hatmaker  Original Development
*/
IsInListλ = LAMBDA(
//  Parameter Declarations
    [Value],
    [LIST],
    
//  Help
    LET(Help,           TRIM(TEXTSPLIT( 
                            "DESCRIPTION:   →Determine if a value is one of a list of items.¶" & 
                            "PARAMETERS:    →¶" & 
                            "Value          →(Required) A value or array of values to compare.¶" & 
                            "List           →(Required) A list of values to check¶" &
                            "→¶" &
                            "EXAMPLES       :→¶" & 
                            "Result         →Formula¶" &
                            "FALSE          →=IsInListλ(1, {2,3,4})¶" & 
                            "TRUE           →=IsInListλ(1, {1,3,5})¶" & 
                            "→", "¶"
                            )
                        ),
    //  Validate Parameters
        Value,          IF(OR(ISOMITTED(Value), Value = ""), #VALUE!, Value),
        List,           IF(OR(ISOMITTED(List), List = ""), #VALUE!, List),
        
    //  Procedure
        Result,         N(  NOT(ISNA(
                                    MATCH(Value, List, 0)
                                )
                            )
                        ),

    //  Handle Error
        Error,          OR( ISERROR( Result)) + 1,
        
    //  Return Result
        CHOOSE(Error, Result, Help)
    )
);



/*  FUNCTION NAME:  RangeToDAλ
    DESCRIPTION:*//**Convert a range reference to a dynamic array reference*/
/*  REVISIONS:      Date            Developer       Description  
                    Aug 19,2023     Craig Hatmaker  Copyright
*/
RangeToDAλ = LAMBDA(
//  Parameter Declarations
    [Range],
    [Rows],
    [Columns],
    [Diagnostics],
    
    LET(
    //  Help
        Help,           TRIM(TEXTSPLIT(
                            "FUNCTION:          →RangeToDAλ(Range, [Rows], [Columns])¶" &
                            "DESCRIPTION:       →Convert a range reference to a dynamic array reference.¶" & 
                            "WEBSITE:           →Coming soon¶" &
                            "PARAMETERS:        →¶" & 
                            "Range              →(Required) A reference to the range to be converted.¶" &
                            "Rows               →(Optional) Number of rows for the dynamic array. Defaults to 1.¶" &
                            "Columns            →(Optional) Number of columns for the dynamic array. Defaults to 1.¶" &
                            "Diagnostics        →(Optional) TRUE: Show error messages. Default is FALSE¶" &
                            "→¶" &
                            "EXAMPLES:          →=RangeToDAλ(A1, , ModelPeriodCount)",
                            "→", "¶"
                            )
                        ),  

    //  Check inputs - Omitted required arguments
        OmittedArgs,    VSTACK( AND( ISOMITTED( Range))),
        Help?,          AND( OmittedArgs),

    //  Check inputs - Set optional arguments' defaults
        Rows,           IF( ISNUMBER( Rows), Rows, 1),
        Columns,        IF( ISNUMBER( Columns), Columns, 1),
        Diagnostics,    IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE),

    //  Check inputs - Errors in argument values
        ErrorsInArgs,   VSTACK( 
                            OR( ISERROR( Range)),
                            OR( ISERROR( Rows)),
                            OR( ISERROR( Columns))
                        ),

    //  Check inputs - Data validation errors
        DVRange,        NOT( ISREF( Range)),
        DVRows,         Rows < 1,
        DVColumns,      Columns < 1,
        DVErrors,       VSTACK(DVRange, DVRows, DVColumns),
        
    //  Any input errors detected?
        AnyErrors?,     OR(OmittedArgs, ErrorsInArgs, DVErrors),
        
    //  Assemble Error Messages Array (if errors found)
        Errors2Show,    VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), 
        ErrMsgs,        {"Range must be a valid cell reference.";
                         "Range contains errors. Remove errors first.";
                         "Rows contains errors. Remove errors first.";
                         "Columns contains errors. Remove errors first.";
                         "Range must be cell address or name.";
                         "Rows must be a whole number greater than 0. Default is 1.";
                         "Columns must be a whole number greater than 0. Default is 1."},
        Messages,       FILTER(ErrMsgs, Errors2Show, ""),        
                       
    //  Procedure
        Result,         OFFSET( Range, 0, 0, Rows, Columns), 
        
    //  Handle Error
        Return,         IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))),
        
    //  Return Result
        CHOOSE(Return, Result, Help, Messages)
    )
);