AFE doesn't preserve the format/indentation defined by the user

[dlealv]

I realized that when I copy the following formula:

=LET(set, A2:B13, IDs, INDEX(set,,1), dates, INDEX(set,,2),
  HREDUCE, LAMBDA(id, arr, REDUCE(id, arr, LAMBDA(acc, x, HSTACK(acc, x)))),
  output, DROP(REDUCE("", UNIQUE(IDs), LAMBDA(ac, id, VSTACK(ac, LET(
    idDates, FILTER(dates, ISNUMBER(XMATCH(IDs, id))), HREDUCE(id, idDates)
  )))),1), IFERROR(VSTACK(HSTACK("ID", "Dates"), output), "")
)  

to the Grid editor, when I save it, it changes the way I originally formatted or indented the formula, to something that makes the formula hard to read and unnecessarily large:

=LET(
    set, A2:B13,
    IDs, INDEX(set, , 1),
    dates, INDEX(set, , 2),
    HREDUCE, LAMBDA(id, arr, REDUCE(id, arr, LAMBDA(acc, x, HSTACK(acc, x)))),
    output, DROP(
        REDUCE(
            "",
            UNIQUE(IDs),
            LAMBDA(ac, id,
                VSTACK(
                    ac,
                    LET(
                        idDates, FILTER(dates, ISNUMBER(XMATCH(IDs, id))),
                        HREDUCE(id, idDates)
                    )
                )
            )
        ),
        1
    ),
    IFERROR(VSTACK(HSTACK("ID", "Dates"), output), "")
)

Is there a way to prevent this behavior? I would say AFE should preserve the user's intention in regard to the format or indentation policy the user wants to have.

I would suggest in the Setting tab to allow the user to define the format or indentation policy or keep the user indentation, or define some standard predefined format/indentation flavors: compact, extended, etc.

[jack-williams]

I did notice a bug in AFE where we are not writing the grid formula out on a single line, and instead we are writing the formatted version. This will be fixed.

Still, this does not solve your problem of wanting to preserve your format. I think adding a setting to disable auto-formatting is a reasonable enhancement.

[dlealv]

it would be great to disable auto-formatting. This is the main reason I am not using the Grid from AFE. I was trying to use the Module section which doesn't change the formatting, but then you cannot use cell references, it is just for defining names or functions. Thanks

[jack-williams]

We've just updated AFE to include a new setting that preserves the formatting of the formula. By default, the setting in on.

[dlealv]

@jack-williams I noticed that it works for the Grid, but not for the Module**. Would you revisit this request? The setting should apply for the entire Add-ins, not just for a specific tab, at least in the way it was defined in the settings. Thanks

[jack-williams]

I'll re-open this issue to track feedback, but I'll target it to the "Names" tab.

The true representation of the code from the Modules tab is in the files that you see in AFE and that are saved in the workbook parts. We use the name manager to save out the definitions, but the form of the formulas that are saved can be different from what you see in AFE. For example, today we currently:

• remove comments
• add module prefixes to some definitions

In future we may make optimizations to the formulas, rename parameters that are only referenced within the name, minify the code, etc.

Because of this, I do not think it makes sense to allow users to control the format of these names in the manager in the same way as the grid.

The Names tab is similar to the grid, and is designed to be a reflection of what is already in Excel. The true representation is whatever lives in the grid or name manager. Controlling the formatting seems reasonable and consistent with the grid.

[jack-williams]

Re-opening to track the enhancement of:

• Adding control over formula formatting for formulas in the Names tab. Likely, in the same manner as the Grid formulas.

[dlealv]

@jack-williams I see your point, but the Names cannot be exported, it belongs to a specific Excel file. Modules you can import and export to a different excel file. My suggestion is to preserve the look and feels in the format the user wants in AFE for all tabs, and then when you sync with Name Manager convert it to your internal format, always with the Module/Name tabs, etc. as the source. If the user makes some changes from the Name Manager, then it will overwrite the format the user has in the Name/Module tabs, because AFE doesn't have a way to keep the original user format after changes. This ensures sync in both directions, but if you make any change from the Name Manager you lose your format, which is reasonable, but this use case is less frequent. The most frequent use case is that your source will be the Name/Module tabs.

The fact that you can document your code in Module is really great, as a user, you have more flexibility. The Name tab is more cumbersome, not exportable, and you cannot change the order or the input argument, you need to remove them and enter them again.

In case you want to consider my thoughts, it makes it really hard to read the Modele information with the indentation policy you have. A simple two-line formula like this:

NEXT_SET=LAMBDA(x,m,n,i, IF(i=0, x, LET(s, SEQUENCE(,n), y, x+1, idx, XMATCH(m, IF(i<n, IF(s>i,m+1,y),y),-1,-1),NEXT_SET(IF(s=i,INDEX(y,idx),x),m,n,IF(idx=i,0,i-1)))));

it is converted into something like this:

Thanks

[deletemeafter]

@jack-williams I see your point, but the Names cannot be exported, it belongs to a specific Excel file. Modules you can import and export to a different excel file. My suggestion is to preserve the look and feels in the format the user wants in AFE for all tabs, and then when you sync with Name Manager convert it to your internal format, always with the Module/Name tabs, etc. as the source. If the user makes some changes from the Name Manager, then it will overwrite the format the user has in the Name/Module tabs, because AFE doesn't have a way to keep the original user format after changes. This ensures sync in both directions, but if you make any change from the Name Manager you lose your format, which is reasonable, but this use case is less frequent. The most frequent use case is that your source will be the Name/Module tabs.

The fact that you can document your code in Module is really great, as a user, you have more flexibility. The Name tab is more cumbersome, not exportable, and you cannot change the order or the input argument, you need to remove them and enter them again.

In case you want to consider my thoughts, it makes it really hard to read the Modele information with the indentation policy you have. A simple two-line formula like this:

NEXT_SET=LAMBDA(x,m,n,i, IF(i=0, x, LET(s, SEQUENCE(,n), y, x+1, idx, XMATCH(m, IF(i<n, IF(s>i,m+1,y),y),-1,-1),NEXT_SET(IF(s=i,INDEX(y,idx),x),m,n,IF(idx=i,0,i-1)))));

it is converted into something like this:

Thanks

I wanted to tag along this one on this, because I also feel the modules tab should always preserve the user's formatting and not autoformat to whatever arbitrary rules it is currently using, which most of the times makes the formulas look worse, occupy more space, or inversely, it shrinks them to the point to one line. An option to format selection or all code could be introduced, after all, vscode has something like that.

e.g: I had a huge formula that was basically a manual equivalent to javascript's .bind on functions, but for lambdas, and it went from ~140 ish lines to like 350 lines with the autoformatting that can't be controlled. It added a bunch of newlines, extra newlines, split lines. Also defined constants, the autoformatter decided to add an extra new line in between each definition. The Excel formula system and variable declaration is already horrible the second you move further than 2 nested levels. AFE with the modules system makes this experience 100% better just by the editor itself, but then it starts to arbitrarily change formatting.

The modules tab is literally the only place where you can go all out and create nice functions, constants, etc the easiest and most exportable way possible as if you were in a code editor, and right so, it ships with a vscode subset. It should keep user formatting, with postprocessing happening when they get exported to Excel's name manager. What is the editor for if not for working and maintaining the code defined in it?

I'm probably misinterpreting Jack's comment about it, but hopefully the modules editor becomes the place to edit these and not a viewport for arbitrary minified lambdas after the module is saved and reopened. What else do we use per workbook to edit like this? is there a toolchain for writing AFE excel modules with highlighting and intellisense outside that editor?

[jack-williams]

I agree that the modules tab should probably preserve the formatting as applied in the modules tab. That is, if I write code in the modules tab, close AFE, and then reopen it, the formatting should be the same.

We can't do this in general because we also support locale changes, but if you are a single user then we should try and retain formatting.

There are three things to consider:

1. The formatting of the code within the modules tab, and how it is preserved across open/close.
2. The formatting of the code within the modules tab when it is saved into the name manager.
3. The formatting of the formulas in the "Names" tab when they are saved into the manager.

Points 1. and 3. are more important and will look into adding these on our backlog.

I want to make sure that 2. and 3. are treated separately. In 2. the formulas should be view as "compiled code", whereas in 3. the formulas are what the user has written.

[dlealv]

Thanks, @jack-williams for your feedback. Just a quick question what do you mean by "single user" what kind of user is it? I was thinking as a workaround in the meantime once I know the formula is working, to restore my format I can do the following:

1. Update the code in gists
2. Clear the workbook
3. Copy the URL from gists
4. Import and save it.

It requires a lot of manual processes, but I least I have a way to keep my format back if I want to update an existing formula already defined.

[jack-williams]

By single user I really mean "single locale". We will not support the following:

• Define and save modules in workbook using locale 1 (with ,).
• Open workbook with different locale (with ;). We will format the module code to update the separator, but it will not preserve exact formatting.

It will work in the following:

• User A defines and saves modules in workbook using locale 1. Closes WB.
• User B opens same workbook and edits the modules, also using locale 1. The formatting will be preserved as written.

Note: we do not support live coauthoring in AFE.