Forum Discussion

joelb95's avatar
joelb95
Brass Contributor
May 22, 2024

Why does this formula break excel?

I have a formula that somehow causes excel to crash and am wondering if anyone knows why.  This formula returns the value of (let's say) 1.  It is a one and can be used as a one in some cases.  However, as soon as I put the "grp" value into an index function or do math on it within the module editor, excel crashes (it actually crashes on save in the module editor).  

 

groups.getGroupID =
lambda(grps, group_name,
    let(
        grps_names, unstuff(grps(1)),
        grp, match(group_name, grps_names, 0),
        grp
    )
);
 
For example, this returns the value of 2:
=INDEX(LET(grps,groups.create(groups.createArgs(tbl_colors,2)),groups.getGroupID(grps, "red")),1,1)
 
This returns a value of 3:
 
=INDEX(LET(grps,groups.create(groups.createArgs(tbl_colors,2)),groups.getGroupID(grps, "red")),1,1)+1

This crashes excel:
groups.getGroupID =
lambda(grps, group_name,
    let(
        grps_names, unstuff(grps(1)),
        grp, index(match(group_name, grps_names, 0),1,1),
        grp
    )
);
 
and this crashes excel:
 
groups.getGroupID =
lambda(grps, group_name,
    let(
        grps_names, unstuff(grps(1)),
        grp, match(group_name, grps_names, 0) + 1,
        grp
    )
);
 
excel
Formulas and Functions
  • joelb95's avatar
    joelb95
    Brass Contributor
    May 22, 2024
    I found a workaround, but it is inexplicable to me. I ended up directly defining the formula in name manager (rather than the module editor in excel labs) and everything seems to be ok. Why would name manager work but not the Excel Labs module?

    In name manager under the name "groups.GetGroupIndex"
    =lambda(grps, group_name, LET(grps_names, unstuff(grps(1)), grp, MATCH(group_name, grps_names, 0), grp))

    Wrapping context:

    groups.getGroup =
    lambda(grps, group_num_or_name, [name_1_controlIds_2_mask_3_default_2], [use_name_bol_df_true],
    let(
    grps_members, encap.unstuffSecondElement(grps),
    group_attr, if(isomitted(name_1_controlIds_2_mask_3_default_2),2,name_1_controlIds_2_mask_3_default_2),
    use_name, if(isomitted(use_name_bol_df_true),TRUE,use_name_bol_df_true),
    index_no, if(use_name, groups.getGroupIndex(grps, group_num_or_name), group_num_or_name),
    grp, index(grps_members,index_no,1),
    grp
    )
    );

    And for the sake of context, these series of formulas allow me to turn an array of data into a groups "object" where I can then retrieve individual group (and then access their information) or access metadata about the groups (such as the name of each group).

Resources