search

microsoft / advanced-formula-environment

Create, edit, and reuse formulas in Excel
https://aka.ms/get-afe
MIT License
113 stars 11 forks source link

Support Excel Beta functions #17

Closed jack-williams closed 2 years ago

jack-williams commented 2 years ago

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

SergeiStPete commented 2 years ago

Of course #2 is more preferable. If supports all platforms where add-in could be installed. And in case of really dynamic detecting, not saving the setting as it is on file creation. Here is jumping from desktop on web, co-authoring with people on different versions - al shall work. Otherwise manual setting is enough. The only don't hide it deep into the menu, with file opening on another version/platform it shall be easily available to change the setting.

jack-williams commented 2 years ago

Looks like the Office JS API provides the build number of the app running so it should be possible to enable support for the functions automatically.

SergeiStPete commented 2 years ago

That will be great. I any case that doesn't take flighting into account, however better than nothing.

FastExcel commented 2 years ago

Why not just add them to AFE anyway? If the user has them then everything is fine. If the user does not currently they can still create Lambdas that use them - they will just get #Name when executed, but can share them via Gist or view other Lambdas that contain them.

mitchcapper commented 2 years ago

As @FastExcel mentioned there doesn't seem like a lot of downside to support the syntax. One can add some manual definitions with local overrides as the other VSTACK code is there from calc.ts. They do have ids assigned for signature.WorksheetFuncId.VSTACK for them just not the outer definitions.

Otherwise from a detection POV could one not just do as done for the others? Or even just calling it in excel would throw an error if not exist.

jack-williams commented 2 years ago

The next release of AFE (currently in private preview) will have the new text and array functions.