Start with:

Editing to -

Returning to normal edit mode

Going to formula edit mode:

UNDO is not available. Copy and paste restores back to original state OK. Entering the very same syntax by hand works normally.

Hi,

I think if you turn the Hidden Sheet option off, there should be a warning that says all the comments and formula formatting will be lost. In my case even some of the formulas broke when I turned this option off (and closed and opened Excel), but then I had to upgrade to the latest version of Excel. Then only the comments and formula formatting were lost.

If users rely on the Hidden Sheet, and then it's turned off by accident, it can easily break the whole workbook. Whilst I know you do not consider Hidden Sheet as a long term solution, there should be a clearer warning of what will happen when this option is turned off, so the user properly understands its affect.

I work for an organization whose name includes a numeric character. When I try to create a module with their name, the "New module" dialog works fine right up until the numeric character, when it shows "Name contains unexpected characters".

I need support for numeric characters in module names.

Currently, there is no way to change the order of the function arguments, not a big deal for a few arguments, but if you have several of them, and just decide to change the order, you need to remove all of them and recreate them again with the new order.

In the above example if I want y to be the first argument and x the second I need to remove them an recreate them again.

I tested in Excel for Web.

Thanks,

David

Importing the Gist to an empty workbook module

Gist imported

No formulas inside the tab

I find that Excel Labs Advanced Formula Environment is not compatible with some of my workbooks. Usually, the more chaotic ones with VBA, External Links, etc. It simply shows "Failed to load". I haven't narrowed down exactly what is causing the problem. Is there any guidance or means of debugging?

I was able to run go LAMBDA function which uses timer function. But when I try to call it alone I am getting an #CALC! error:

Thanks,

I have a large library of functions (~100) that I developed for a specific problem domain and I would like to package them as a module to distribute via Gist. It would be nice to be able to create a module from existing names (functions and formulas), but there's doesn't appear to be a way to do that.

The feature should recognize module names, as I have already stored my functions as ModuleName.FunctionName, which would mean stripping off ModuleName when bringing them over from Name Manager.

For now, I don't have much of a problem, as I maintain the functions in a worksheet and use some rudimentary VBA to publish them, so it will be easy for me to export them in module format. It would be nice in the future, though.

I've been doing a lot of copy/pasting from one workbook's AFE to another, and I've found it seems to be possible to break the synchronization, where the only solution is to delete everything and start from scratch. When I'm trying to bring in 10+ namespaces, this can get quite annoying.

The specific error that happens is when I copy code that references a table that doesn't yet exist in the file I'm copying to. This seems to break the synchronization process somehow, and even ensuring the table exists and is named properly doesn't seem to work. It seems like certain functions get flagged as having been copied even if they haven't. I've found that renaming a function, syncing, then renaming it back to what it's supposed to be, and re-syncing, works, but this is incredibly tedious when I have 50+ names that are affected.

I've tried this with both the hidden sheet enabled, and without, and it doesn't seem to make a difference.

If there was a way to just force it to resynchronize all names, I think this would solve the issue.

For example, if you have a function defined via Script Lab Add-ins, then the editor in Grid/Module view doesn't recognize it as a valid function:

Reporting as possible enhancement, because at the end it works, I can execute the function without any issue.

Regards,

David

The gist library support (or any library source including onedrive) would be great for more rapid connections between workbooks.

There seems to be much of the work done, and at least github token api seems like it wouldn't be too far from fully working (although the oauth code seems like it would be fine too just can't test that easily).

The ticket can at least track the feature if there isn't any additional information for now.

Currently using an external app for some synchronization of named formulas across files but would be great here.

I keep both, don't know why, just in case. Eventually AFE disappears, not a big dial to install it again, however bit annoying. My guess that's since both are kept, perhaps I'm wrong.

I guess text is very minimalistic. Instead of the link on MTC Excel discussion I'd give the link on related to AFE blog on MTC. Posts in common feed disapper in few hours, tag doesn't help a much. Plus add proper links on github and LinkedIn. Ideally it is better to submit feedback directly from AFE.

Prior to knowing AFE existed, I created several functions that used a similar concept of a namespace, but after the period, I used a number followed by a letter. On its own, a name can't begin with a number, but both Name Manager and LAMBDA handle my names fine.

One example is "TLU.3D"--a three-dimensional table lookup function.

It appears that AFE is recognizing "TLU" as the namespace, but when it sees "3D" it doesn't import it because it starts with a number. It does this even though "TLU.3D" meets name definition requirements.

For such named functions, AFE won't import them from name manager, nor can I import them by pasting the text. AFE doesn't give the "Incorrect name syntax" message either. Is this a feature or a bug?

https://word.tips.net/T000964_Changing_the_Default_File_Name.html, Word 95 can do it, Excel 365 still can't.

See also:
BOOK.XLTX, SHEET.XLTX, PERSONAL.XLSB, Ctrl+N, SemVerDoc.org, MS Word Cover Pages, #6, #9

I mean, I WANT to put an end to e-mail as horrendous version control for Microsoft Excel documents but you guys make it really hard.

BUT ALSO, THANK YOU SO MUCH already for all the hard work you've put in to implement all this. I can smell the stale air leaving.
😘😘😘

#RecalcOrDie

P.S.:
After you've finished all up on Excel & while on the topic of feature parity making life easier for everyone, please could someone add a good ending to this Computer Science list continuation & transclusion nightmare from hell next:
http://www.addbalance.com/word/MasterDocHudson.htm (the meta-workaround for which consists of http://www.gmayor.com/Boiler.htm)

Also, the Nightmare™ of the Microsoft Word Wizard .WIZ file format. And bring back InfoTip localisation packs for crying out loud whoever cut that feature needs to be forced to work multilingual L1 support for a decade. Think of all the guys over in #1, #2, #3, they have to Google Bing™ formulas in English all day but use localised Excel to communicate with end-users. How do you think that works? (sorry, yes, this is off-topic, but I feel bitter. See also: Double-clicking comma-separated .csv files with localized MS Excel installed. Like, seriously, it's 2022? ☹️ And don't get me started on using named ranges inside conditional formatting & data validation.)

hi everyone,

I've been seeing this error message in most workbooks recently: "Failed to load. Unable to validate date order test"

Some workbooks work, but most don't.

Anyone know what's going on with this?

[Details: I'm running my office apps via a Business Standard 365 licence and am using the Insider current preview channel.]

I've written several functions using the Names>Functions tab, but now I'd like to share these to other workbooks or other users. I hoped that the functions that I wrote would appear in the Workbook module so that I could just copy the whole thing to a gist, but it appears that the Workbook module is separate from the Names tab.

Is there a simple way to copy these functions into a gist? It looks to me like I would have to individually copy each function definition into a gist/workspace, which is complicated by the fact that the Names>Functions tab hides the LAMBDA part of the definition.

Using Excel Desktop installation get the following message
"This add-in won't run in your version of Office. Please upgrade to either one-time purchase Office 2021 or to a Microsoft 365 account."
I already have a Microsoft 365 account.
Tried Excel Online (office.com) and I do not get this message.
I am not only one getting this, see comment section of
https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/bc-p/3216298#M2608

Am I missing something or is this a bug?

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.

Today we report errors, and do not give suggestions, for functions in Beta like HSTACK [1]. We do this because not all users have these functions.

This issue is to track support for Beta functions either by

1. Providing settings to indicate that a user has these functions.
2. Dynamically detect whether these functions are enabled.

[1] https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/bc-p/3293043#M2788

on non-english languages, where used comma for decimal point, the array notation is a bit different

english row-separator is , and column-separator is ;
non-english row-separator is ; and column-separator is \

MIght be usefull when syntax validator recognish the difference. Actually the column-separated (horizontal) arrays are treated as error

Doesn't matter it was sinle- or multiline formula in formula bar, it always is returned as single-line formula. Not sure when it appeared, not a long ago.
Did I miss something?

Subj. + https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439

What is the purpose of allowing comments in the Grid tab? They are allowed (in some cases), but Excel doesn't render them properly, and when you put a comment the function doesn't get invoked. For example:

It treats the function invocation as a text. I would suggest to use comments both block and inline comments, in a similar way a compiler will treat them in any other language, something that doesn't produce any output. It can serve the following purposes:

1. Comment something in the grid, for clarification purposes
2. Debug your code, you can add comments to remove a specific portion of your code during the invocation

For example:

We have a function, but we would like to know the intermediate calculation of a specific variable. In this case, it produces an error.

We want to test or debug different possible calculations, in a single cell:

The above code doesn't produce any error, but it doesn't generate any output

Thanks in advance,

David

Hi, thanks for developing AFE, enjoying using it so far.

An improvement suggestion is the ability to define an enum-typed argument, with the same intellisense support that many built-in functions have. As far as I can tell, this functionality doesn't exist at present.

I realized that you cannot use the dot (.) character in the name of the function in any other Name different than the Workbook:

no error, but:

I would say the naming convention should be the same, and follow Excel naming conventions.

How to reproduce:

1. Open file with a pre-existing AFE module (the one shown has 42 lines).
2. Test saving module. Save operations performs normally.
3. Make any change in the module (src shows adding a new line #43)
4. Save operation fails.

Reproduced in several files.

Love it! Is there intent to add functionality that adds existing and traditionally created named ranges to the Editor/fully sync with name manager? It looks like the data only flows in one direction right now.

Plus, an option to add any AFE comments to the traditional "Comments" block in name manager to be seen by people without the add-in would be very useful.

When I load the Add-in and start using it, there is no way to minimize or hide it. If I close it (click on x) I need to add the Add-in again:

The ping/upping functionality is not clear how works at least in Excel Web. If I close the excel file and open it again I need to install the Add-ins again.

I think it is a good idea, to have a way to minimize it, without the need to install it again, the Add-in uses a significant portion of the screen, so it would be nice to minimize it when you want to focus on the content of the excel file itself.

There are some names which we can't use in AFE (Table, Group, Consolidate, etc.) but documentation says nothing about that, at least I didn't find. It's worth to mention, if not list of such names when the rule.

I re-read documentation, my understanding it assumes that formula in currently selected cell is shown in formatted way in grid view. On practice it happens only if close and open AFE staying on the cell with formula.
Also, found no way to keep such formatting and format formulae in Modules and Names->Formulas blocks.

Did I miss something?

Suppose I have a named table defined. Name Manager sees it.

Expected behavior: table name can be referenced as typed.

Actual behavior: it does not. Named functions are seen alright, though.

Importing a recursive LAMBDA function into a new namespace breaks its self-reference. The same is true if the function references another being imported.

I wrote a LAMBDA function to translate a string into Morse code:

Morse.Translate = LAMBDA(str, [idx],
    LET(
        idx, MAX(1, idx),
        IF(
            idx > LEN(str),
            "",
            CONCAT(
                IFERROR(
                    XLOOKUP(MID(str, idx, 1), Morse.Letters, Morse.Code),
                    "?"
                ),
                IF(idx = LEN(str), "", " "),
                Morse.Translate(str, idx + 1)
            )
        )
    )
);

If I import this function from Gist and assign it to a new namespace, say TestName, it becomes TestName.Morse.Translate, and the references to Morse.Letters, Morse.Code, and itself (Morse.Translate) are all broken, resulting in a #NAME? error.

Morse.Letters is an array of A-Z and 0-9 and Morse.Code is the corresponding translation. Morse.Translate uses recursion to loop through each character of the input string to perform the conversion.

Currently, if you want to import again a workbook from gist, you need to enter the URL every time, even if you want to do an import to a workbook whose content was imported already. To avoid duplication name, you need to remove the content of the workbook. It should not be necessary, AFE should resolve duplicates by replacing the old content with the new content for existing functions. Then add again the URL and import it. I understand if you click on +New, that the URL won't be preserved, but for an existing workbook that was loaded before using a given URL keep that information, so the user doesn't need to enter the same URL again.

Thanks in advance,

David

Shared or External Modules might be an extremely powerful way for organizations to share user-defined Lambdas in their Excel sheets/applications.

Currently, Modules can only be manually imported from GitHub, which poses two concerns:

1. In larger organisations, there might be procedural hurdles against putting company code on GitHub
2. It requires manual resync every time there may or may not be an update

Hence, I'd love to see if it were possible to
a) Save modules into an external file (could be a .txt for all that matters)
b) Import modules from an external file (filesystem or HTTP)
c) Link to an external module, or at least a regular synchronization with it (at least import)

Then organisations could publish standard modules containing vetted lambdas for their users to integrate and then use in their tools, while retaining a single source of code which can be maintained.

Following up on #30,

Have there been any developments to having the hidden sheets generated by AFE being removed automatically after use?

Also, in its current form, will hidden sheets maintain the same name across different workbooks? - This would help us build a macro that finds and deletes any hidden sheets.

Thanks!

It hasn't been stated anywhere - at least I couldn't find it - but seems to be English only.
Can some one add that to the documentation before a lot of people might waste their time or hamper there systems.

This formula environment is really helpful. Thanks!

I'll see the following error message frequently when I sync code changes from the editor (using the metadata sheets). If I clear the message and sync again, the change is accepted successfully. I see this behavior frequently.

Sync names with Excel name manager. failed: The argument is invalid or missing or has an incorrect format.

using Mac Version 16.61

I'd be great to be able to use the AFE features (e.g. indentation, highlighting, intellisense etc.) while editing an unnamed formula in the active cell.

Rationale: There's a whole lot of Excel-sheets out there that use rather complex / long formulas that are only used once and are therefore not immediate candidates for naming / lambda. However, editing / maintaining such formulas could benefit greatly from the AFE features mentioned above.

The most straightforward way would be to place an additional card "Active Cell Formula" at the top of the list in the "Manager" Tab. It would make sense to create some visual distinction from the named formulas, e.g. by using a visual separator or having the formula be a slightly different colour.

Perhaps it was discussed, didn't find. AFE disappears from the ribbon from time to time. Can't say if same happens with other add-ins, have only AFE of such kind. Perhaps it's since I'm shifting from VPN to not VPN connection periodically, don't know. At least I can't reproduce that intentionally.
Not a big job to add AFE again from My Add-Ins, but better to fix.

When you create a new file and start adding modules and formulas to it, a very hidden sheet is created. This contains some functions and data that seems to be without purpose.

After adding a few modules with some definitions, I see that these seem to be stored in an embedded XML document, but nothing changes in the hidden sheet. Deleting the very hidden sheet does not seem to have an impact on anything.

What is the purpose of the very hidden sheet? Is it related to an older version of AFE that is no longer in use, or does it have some purpose

It was alert that comments / formatting are supported with hidden sheet only, can't see it now. If it's still so perhaps it's better permanently to have such text near setting.

This issue tracks an enhancement request for improving the error message when saving from AFE fails. Currently, we only display the default error message from the Office JavaScript API.

Related #31

I opened an existing workbook with a number of valid lambda functions, but when opened in the advanced formula environment, the formula is not there, it shows an error where the first semicolon (argument separator in my locale) appears.

I wanted to format just a specific portion of my script, but instead, it formats the entire script. Maybe this is how Monaco Editor is supposed to work, but I don't know:

Here is the result:

It formats all the formulas in the script, and the large comment doesn't get formatted.

For example, the command Join lines works for the selected area, but Format doesn't

Thanks,

David

Currently, the tab length in the Module tab is set to 4 spaces. The suggestion is to let the user specify the tab length in terms of the number of spaces.

This is a request for clarification or tutorial, not a bug report.

what steps must be followed to create and load an external library from Github (or for that matter, locally)?

I've just tried the new named function creator. It looks great, but I can't get it to work.

It just puts ="=" in the name, instead of =LAMBDA(...).

Currently we are able to assign a name to a range. However, the default scope for that range is within the workbook, which is the same as changing the name in the native name box.
It would be more completed if you add a dropdown list to change the scope of the range, just as in the name manager dialog.

This issue is used to track the popularity of adding multiple locale support. Feel free to provide relevant feedback, or indicate support with a 👍. We can make no guarantees about supporting such a feature, but your feedback is greatly appreciated, and will help prioritize any work we undertake.