Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project

[Chris_Gross]

November 17th 2022 Update

The advanced formula environment has been updated to version 1.1. See the change log for more information.

We are excited to announce that LAMBDA and LAMBDA helper functions are now generally available to anyone using Production: Current Channel builds of Excel. In conjunction with LAMBDA going to production, we are also announcing the release of a new add-in, the advanced formula environment, sponsored by the Microsoft Garage and Microsoft Research, which allows easy import/export and authoring of named LAMBDAs.

Thanks to all of our Insiders for using the LAMBDA functions and giving us feedback! As a result we've also made a few changes that we'll outline below in addition to talking about the advanced formula authoring environment, a Microsoft Garage project.

Let’s start with a quick example.

IFBLANK

A task I regularly encounter is replacing certain values in a dataset, such as errors or blanks cells. Excel provides IFERROR to replace error values, but there is no function to replace blank cells. Fortunately, with LAMBDA, we can define our own function, IFBLANK.

Instead of authoring this in the grid and then importing to the name manager, I will instead author this in the new formula environment and then sync it to the workbook to make use of it in the grid.

In today’s scenario I am trying to create a quick count of the meals that have been requested for a dinner party I am hosting.

Without IFBLANK, Excel, by default returns a blank which gets coerced to 0 when placed in the cell. I could work around this but I also need to get a count of orders which I will do using MAP and REDUCE to author one formula so I can get updated counts as I continue to get more responses from guests.

Here’s the formula I used for the counts:

=MAP(D4#, LAMBDA(meal, REDUCE(0, IFBLANK(Table14[Meal Preference], B1), LAMBDA(meal_count, preference, IF(meal=preference, meal_count+1, meal_count)))))

Lets go over some improvements and changes we have made on our journey through Insiders to the LAMBDA feature in general.

Changes made to LAMBDA

Function tooltips

We have added support for function tooltips for named LAMBDAs in addition to auto-completing the open parentheses character when calling these functions. In other words, calling a function defined using a LAMBDA is exactly the same as calling a native function.

Recursion limit increase

We have upped the limit of recursion by 16 times its original limit. In this example, the text string is 3200 characters long and would have previously returned a #NUM when called with a LAMBDA that recursively reverses a text string.

LAMBDA helper function outputs

We changed the way that the LAMBDA helper functions handle arrays of references.

Previously when a LAMBDA helper function returned an array and the associated LAMBDA returns a single cell, a #CALC error would be returned. We have changed this to automatically return the cell value as the output of the LAMBDA function.

In this example, Excel would have returned a #CALC but now will return the results.

 

Advanced formula environment, a Microsoft Garage project

Today we are introducing a new tool to aid in the authoring of more complex named formulas. The advanced formula environment is a space where we are hoping to experiment and explore new and different methods for authoring formulas with special functionality designed with LAMBDAs and LET in mind.

Key features of the new tool:

• Advanced formula authoring capabilities found in modern IDEs
  • Intellisense
  • Commenting
  • Inline errors
  • Auto tabulation
  • Code collapse
  • And more...
• Undo/redo of formula edits within the manager
• Namespaces to allow for groups of named functions
• Import and export functionality
  • Text and GitHub Gist import
• Different views to filter your names and edit in a single location

The environment is available on all platforms where Office Add-ins are available (Mac, Windows, Web)

Let's take a look at some of the functionality so you can get started with managing and editing your new and pre-existing named functions!

 

Manager and Editor Views

There are two major views contained within the advanced formula environment, Manager and Editor.

Manager

The Manager is where you will see all of your names with their own individual cards and associated quick actions. Much like the Name Manager but with more functionality.

	Edit the name
	Rename the formula
	Delete the entry
	Export the definition for sharing

Editor

The Editor is where you can go to edit all entries within the workbook or create new namespaces for collections of formulas.

This is where I usually go when I want to dive in to create more complex functions as you get the full version of the editor in this view and can create multiple names sequentially.

The workbook section contains all names which are not attached to a given sheet but instead are saved globally in the workbook.

Some of my favorite pieces of functionality are the ability to easily add new lines, tabulate sections of the formula while also being able to comment out pieces of my formula that I might be working on, or collapse definitions so I can more easily dig into specific areas. Not to mention, if I change my mind later, I can easily undo/redo any changes I am actively working on.

Here's another example I made for a chess game I have been building for fun in Excel.

Importing

The advanced formula environment can import definitions into the manager. You can important individual definitions as well as libraries of definitions via text or through GitHub Gists.

The main entry point for importing can be found by selecting the action in the actions bar. The main entry point defaults to “From URL” but the dropdown reveals the “From text” option.

If you’d like to try out this functionality yourself I am including a gist with some of the examples I created today on top of additional LAMBDAs I have used in prior posts. I think this is a better way to share than having you copy/paste from a blog post, so lets be the first to try it out!

aka.ms/LAMBDAGist (make sure to use the URL you are redirected to as the add-in doesn't let you use non gist paths)

We look forward to the libraries of LAMBDAs the community produces and hearing from you all about what does and doesn’t work in this new environment we have created.

 

Feedback

We are actively looking for feedback on the experience and would invite you to provide feedback either through techcommunity or by going to this github repository.

Accessing LAMBDA functions today

To get access to LAMBDA functions, please make sure you have updated to the latest version of Excel. Specifically versions greater than or equal to:

• Windows: 16.0.14729.20260
• Mac: 16.56 (Build 21121100)
• iOS: 2.56 (Build 21120700)
• Android: 16.0.14729.20176

What version of Office am I using?

 

Accessing the advanced formula environment

You can use this link or manually install if from the app

https://aka.ms/get-afe

To access the advanced formula environment, simply search for the “advanced formula environment” within the built-in add-ins store of Excel and install it like any other Office add-in.

1. Go the Insert Tab
2. Select the Get Add-ins button
3. Search for “advanced formula environment”
4. Click the “Add” button

Once the add-in is installed, you should be able to find it on your home tab. The ribbon button looks like the picture below.

Click here to learn more about Office Add-ins

Learn more

To learn more about LAMBDA and the advanced formula environment, please check out the links below and in the meantime we are excited to hear more about the ways you have used LAMBDA in your own workbooks!

LAMBDA Help

Advanced Formula Environment

 

Availability notes

LAMBDA is now available to Office 365 Subscribers in Production: Current Channel

To stay connected to Excel and its community, read the Excel blog posts and also follow Excel on Facebook and Twitter. 

Do you have an idea to make Excel better? We'd love to hear about it. Go to the Excel forum in Microsoft Feedback to submit your idea or vote for other ideas. You can also click the Feedback button in Excel and choose "I have a suggestion".

A joint collaboration

The last thing I would like to mention is that all of this was done as a joint collaboration between Microsoft Research and Excel Engineering. It’s been a blast building out all the experiences you see today and it wouldn’t have been possible without the brilliant researchers at Microsoft Research Cambridge.

Chris Gross
Program Manager, Excel

Jack Williams

Lead Developer and Researcher, Microsoft Research Cambridge

[cdem55]

@Chris_Gross you made a great development.

One request : to make the plug in compatible with international version of excel. It does not work when formula separator is ";" and not ",".

Christophe

[alberto20]

@Chris_Gross     ....    

One request : to make the plug in compatible with international version for excel. It does not work when formula separator is ";" and not ",".

 YES, thank you

[Levi Bailey]

In case anyone else has been:

• having transient rendering issues that might have started around the time you installed Advanced Formula Environment;
• those rendering issues seem familiar but were previously fixed by disabling hardware graphics acceleration;
• especially if the rendering issues are worse than before, like the occasional blue screen of death; and
• the option to disable hardware graphics acceleration has disappeared completely from the Excel Options dialogue box;

then try uninstalling the add-in. I haven't done any serious checking and could be fooling myself as the problem is transient, but it seems to have worked for me.

[dermotb]

There is no need to clutter workbooks with extra hidden sheets to store info.

Why not create custom properties in the workbook object?

These can store a lot of text and are utterly invisible to the user.

[dms37]

Get a message saying I need to purchase Office 2021 or a 365 account but I am logged in to a Office 365 account.  Fully updated and on Current Channel.  Any help/advice welcomed.  Thanks

[Dbill74]

Getting message in the Advanced Formula Environment Pane that reads

"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 an Office 365 account.

Using Excel installed to my desktop PC and get this message.

Using Office.com Excel and I do not get this.

Am I missing something?

Really looking forward to using this.

[David Hager]

It is clear that the AFE was created with an object model. Why not extend it with some VBA code so you don't have to copy/paste LAMBDAs into it?

[PipFinyx]

unfortunately it appears to be crashing some existing spreadsheets - how do I uninstall it

[PipFinyx]

red herring - was something else!

[DavidMaurel]

Cannot wait to use it. But the add-in won't load and throws an error. And even if it did work, I would get the ; separator problem anyway... 

[Peter Bartholomew]

Don't forget, the functionality that should be tried and evaluated is Lambda functions and the helper functions.  The AFE is useful and, though it forms part of an emerging development environment, it is not an integral part of the solution.

[alberto20]

Yes, but it is equally important that ALL users can use it and contribute to its development.
Users who are not English speakers or who use Excel with the separator ";" instead of "," ... are totally excluded from its use.
Thanks for the attention.

[Peter Bartholomew]

@alberto20 

I fully understand and sympathise with your discontent.  I believe the editor used for the prototype AFE was pre-existing rather than purpose-written functionality,  so comes with its limitations, as described in the documentation.  The point that I was trying to add to the discussion was that the AFE is not essential to the development of Lambda functions.  Despite the fact that I use an English language version of Excel, I only use the AFE about half the time.  Many Lambda function I simply write first as an anonymous functions on the worksheet with test parameters.  Ultimately, it is the Lambda function that is important, not the development environment.

[alberto20]

Thank you @Peter Bartholomew for understanding.

You are certainly correct that LAMBDA development is more important than AFE but you have to consider how frustrating it is to have to use an ULTRA-minimal editor like the Excel environment editor (ALT+ENTER) compared to the BASIC functionality of AFE ... ... even for small and simple Lambda functions   ... 

[AlexC84]

rather than using a hidden worksheet to preserve formatting and comments included in a named lamda function couldn't the custom xml parts be used instead?

[LoganBerries]

When will LAMBDA be available on Excel for the web? It works in my desktop environment & the Excel app on an IPad or IPhone, but when I open the workbook in a browser, using LAMBDA or its helper functions will result in a #NAME error.

[fstorino]

Hey y'all. It's been two months since you launched version 1.0.0.0 of the Advanced Formula Environment and it still doesn't work in over half of the countries of the world, where the comma is used as the decimal separator. Are there any plans to update it? Is there a roadmap? Can't wait to use it! Thanks!

[Sergei Baklan]

@fstorino , you may check here Support multiple locales · Issue #3 · microsoft/advanced-formula-environment · GitHub. In brief "We can make no guarantees about supporting such a feature"

AFE is Microsoft Garage project, aka concept, distributed as add-in. It definitely will be localized when becomes part of the Excel distributed with Excel. I don't think someone could share roadmap now.

[MeniPorat]

Is there a user's guide/manual/link... which explains how to upload (export) LAMBDAs that I've created on my computer to the GITHUB repository?
I've written some very useful LAMBDAS but I can't share them. They are visible only to people who read my posts on my blog or on LINKEDIN.
There are many explanations on how to create/debug/import LAMBDAs but I haven't seen an explanation on how to UPLOAD the LAMBDA.

Thanks in advance.

[Sergei Baklan]

@MeniPorat , first create an account on github.com. With it you may create your first gist from here

or from drop-down menu near avatar go to "your gists"

In general gist is just text file, you may copy/paste into it what you have in AFE.

To upload gist to AFE you need to share URL on it.

[MeniPorat]

Thank you, Sergei.
But this is so primitive. Copy-Paste?
I thought MS has a more elegant method to upload the LAMBDA from your workbook, just as you can download LAMBDAs into your workbook from URL:

[Sergei Baklan]

@MeniPorat , export to GitHub is excluded here. Microsoft Garage project is just testing the water and includes basic functionality. I don't know if Microsoft makes the decision how lambdas will be shared, will it be GitHub or something else. But I believe when AFE go from concept to Excel development stream we will have some mechanism for sharing.

[MeniPorat]

@Sergei Baklan 
I think that MS decision is not a good one. The GitHub platform is only a temporary solution, due its poor functionality and interface. 
If MS is going to find a better platform (as I believe it will) what will happen to all those LAMBDAs already exported/uploaded to GitHub?

BTW, I yesterday tried to upload one of my LAMBDAs, but failed.
The interface is so unfriendly that I gave up in the end.

[Sergei Baklan]

@MeniPorat , I'm not working on Microsoft and have no idea which decision they make before such decisions are announced. That's better to discuss with AFE team here https://github.com/microsoft/advanced-formula-environment/issues. At least what they declare hidden sheets won't be supported in future.

GitHub is quite popular and quite powerful platform, but it's oriented on software developers. Perhaps that's why export to GitHub was excluded from this version. Friendly or not that depends. As with Visual Studio - if you run it first time to do simple task, you lost in bunch of other options it provides. But for people who use it on daily basis it's friendly since due to this bunch of options and they know the logic how to find and select them.

Again, current AFE is just concept. More feedback we give the better future product will be.

[Peter Bartholomew]

@MeniPorat 

"I've written some very useful LAMBDAS"

Hi Meni, I think you are ahead of me there!  I have written some Lambda's that are just work-in-progress that form part of developing every modern Excel solution.  For example

ORλ  = LAMBDA(x,OR(x));
SUMλ = LAMBDA(x,SUM(x));

act as simple wrappers for aggregation function, allowing them to be used within a helper function such as

= FILTER(table, BYROW(operators=selected, ORλ), "Not found")

Such lambdas do not really need an exchange mechanism.  Conversely

rebuiltArrayλ = LAMBDA(dᵥ,
    LET(
        nᵣ, ArrayRef1λ(),
        nᵥ, PRODUCT(IF(dᵥ = 1, nᵣ, 1)),
        nₓ, PRODUCT(IF(dᵥ = 2, nᵣ, 1)),
        n₀, PRODUCT(IF(dᵥ = 0, nᵣ, 1)),
        sᵥ, SCAN(1, IF(dᵥ = 1, nᵣ, 1), LAMBDA(s, n, n * s)) / nᵣ,
        sₓ, SCAN(1, IF(dᵥ = 2, nᵣ, 1), LAMBDA(s, n, n * s)) / nᵣ,
        s₀, SCAN(1, IF(dᵥ = 0, nᵣ, 1), LAMBDA(s, n, n * s)) / nᵣ,
        rowIndices, IF(dᵥ = 1, 1 + MOD(QUOTIENT(SEQUENCE(nᵥ) - 1, +sᵥ), nᵣ), 0),
        columnIndices, IF(
            dᵥ = 2,
            1 + MOD(QUOTIENT(SEQUENCE(nₓ) - 1, +sₓ), nᵣ),
            0
        ),
        reduceIndices, IF(
            dᵥ = 0,
            1 + MOD(QUOTIENT(SEQUENCE(n₀) - 1, +s₀), nᵣ),
            0
        ),
        MAKEARRAY(
            nᵥ,
            nₓ,
            LAMBDA(r_, c_,
                REDUCE(
                    0,
                    SEQUENCE(n₀),
                    LAMBDA(acc, i_,
                        acc +
                            ArrayRef1λ(
                                INDEX(rowIndices, r_, ) +
                                    INDEX(columnIndices, c_, ) +
                                    INDEX(reduceIndices, i_, )
                            )
                    )
                )
            )
        )
    )
);

which pivots a multidimensional array (with each dimension may be displayed vertically, horizontally or summed out) needs for more in the way of description and probably the accompanying workbook to demonstrate its usage.

Something else that might be of interest is an ability to scope Lambda function names to 'user' or 'corporate' templates (extending the present 'cell', 'sheet', 'workbook' scopes for naming) to facilitate local sharing without requiring the use of external platforms.

[MeniPorat]

@Peter Bartholomew 
That's supreme.
KUGW (That's my shortcut for: Keep Up the Good Work).

[Sergei Baklan]

@Peter Bartholomew , I guess on this stage that's not only, and perhaps not mainly, about sharing with other users. That's about re-use for ourselves and not to repeat manually all these ORλ(), THUNK(), TIMER(), etc in each file. Like with OfficeScript - as soon as you create one and save with any file, it's available in any new file if you work with OneDrive. Not always good but requires no additional efforts.

Loading of lambdas collection into each new file practically costs nothing, that's just texts.

After some practice GitHub is easy enough and as any tool has pros and cons. Gist is limited solution, even with export. More complex one most probably won't work for the majority of Excel users.

As a sample AFE is a good compromise. I don't think that many people use all possibilities of Monaco editor which is behind the scene in AFE. We may start working with it right after the simple installation. Some stop on that, some dig more working on more complex tasks. Same with sharing, we need some mechanism which works from the scratch, without any dances around it. Basic functionality shall be in behind with ability to customise for more complex scenarios.

[Peter Bartholomew]

@Sergei Baklan At the moment, the number of lambda functions I really need to take forward to new workbooks is probably pretty limited and could even be done as a template.  Perhaps Accumulateλ() which applies a percentage change and a payment to an initial balance and calculates the array of opening or closing balances.  Built on that is Allocateλ() which which links every output from a FIFO (first in, first out) problem to the input that feeds it.  The following applies the function to calculate the percentage of iron in a delivered batch of ore

Calculating a moving weighted average during Resource Scheduling | Page 3 | Chandoo.org Excel Forums...

Also useful would be

ArrayRefλ 
= LAMBDA(A, nᵣ, dᵣ, sᵣ,
    LAMBDA([iᵣ],
        IF(
            ISOMITTED(iᵣ),
            nᵣ,
            LET(
                r_, 1 + SUM(IF(dᵣ = "down", (iᵣ - 1) * sᵣ, 0)),
                c_, 1 + SUM(IF(dᵣ = "across", (iᵣ - 1) * sᵣ, 0)),
                INDEX(A, r_, c_)
            )
        )
    )
);

which takes an arbitrary multidimensional array, described by steering data, and returns an element of the array as requested (I have only used the function for 3 and 4-dimensional arrays but it is not limited to that).

One thing that does trouble me is that the functions that tend to appear within the context of the AFE look so different from the formulas one would find by inspecting the content of a cell; are they not the same thing?  An example might be the Microsoft Research Gist referenced by @lori_m in a recent post.  The feel of that was full-on specialist programmer territory.

Something I may try is to develop a workbook subject to the rule that all formulas must be placed within lambda functions.  After all, I already treat direct cell references as errors (except, of course, in Name Manger where they are allowed in order to link sheet real-estate to business objects) and I avoid the practice of using relative references.  Why not inflict a little more pain?  Who knows, the outcome could be a coherent development strategy (or an unworkable mess?).

[jack-williams]

Posting here for visibility: https://github.com/microsoft/advanced-formula-environment/issues/3#issuecomment-1100045252

[LeodeSa]

@Chris_Gross @jack-williams  very nice tool and also nice example about the chess.

I think I am going to use that in my art, I believe to be the only digital artist using Microsoft Excel for abstract art.

If you are interested you can check my collection at OpenSea: https://opensea.io/collection/excel-abstracts

Thank you for these inputs again! 

Leo.

[ecovonrein]

I had been looking forward to Lambda, principally for a very banal application:  It is ever so tiresome to code a UDF in VBA such that it will play along with CSE (or modern SPILLs).  Some years ago, I coded a generic proxy that could wrap any function such that it would iterate over a range in place of a single parameter.  (I always felt that Excel should provide this scaffolding - I cannot imagine what your code must look like for those built-in functions that natively support this game.)  Turns out, I created MAP.  Today, I can throw away this code and write instead =MAP(someRange;LAMBDA(aParam;aUDF(...;Param;...))).  Good times. 

What prompted me to post here is another observation altogether.  I have not looked into AFE yet but have instead been pasting Lambdas into the old Name Manager, where I made a surprising discovery.  Many of my Lambdas that happily worked in the cell I tested them, would not work (or were rejected) by Name Manager.  Turns out (from playing around - I could not find a confirmation in the documentation) that formulas within Name Manager are limited to 256 characters.  (The equivalent limit for cells is something like 8000 characters.)  Does AFE extend this functionality of Name Manager?

[Sergei Baklan]

@ecovonrein , as I remember 256 characters limit is on names, not on formulas. Anyway, AFE syncs everything with Name Manger, thus finally limits shall be the same.

[ecovonrein]

My given name was very short.  But Name Manager rejected any assignments longer than 256 chars.  If AFE does not extend that functionality, I am puzzled how your lengthy examples were accepted.

[AlexC84]

@ecovonrein, the formula length limit in the name manager is unchanged (8192) which I ran into a couple of months ago when getting a little too enthusiastic with the AFE

[ecovonrein]

That's good to know.  I do not understand why 8k works for you but not for me.  Mind, I never received the error message you pasted.  I got no error at all.  Half the time Name Manager would not accept my ENTER, the other half it would accept the ENTER but the new Lambda/Name was nowhere to be found.  I ended up taking my Lambda apart into "sub routines".  It was all rather ghastly.

[Peter Bartholomew]

@ecovonrein 

Maybe the limitations of Excel to store long formulas did you a favour.  I would far rather support a hierarchy of well-tested, nested functions than try to work with a monolithic piece of code.  Mind you, 256 characters would seem to be somewhat restrictive!

[cool2021]

Can I identify and flag statistical outliers and anomalies in datasets (eg. a column of data like $ Average Spent) using LAMDA?

[Sergei Baklan]

@cool2021 , I'm not a specialist in statistics, but if people calculate outliers in Excel like here Formula to Identify Outliers in Excel Data Set | Highlight Outliers - YouTube you may easy to wrap similar formulae to lambda or series of lambdas.

[cool2021]

@Sergei Baklan ......why not just have a a custom function that is accessed in the Formulas tab of Excel that could be used in a sheet like SUM, AVERAGE and other functions that could be accessed with =OUTLIER. Every statistics book at every level teaches the basic calculation and application and importance of identifying outliers in datasets. OUTLIER in statistics is like NPV in finance....and I see an NPV (several different ones in fact) function formula in the Excel Formulas tab. Makes no sense at all: Desktop OR Web version.

[Hans Vogelaar]

You can submit a suggestion form within Excel using File > Feedback, or post on the Excel Feedback Portal.

[Peter Bartholomew]

Rather than simply waiting for someone else to feed you with the functions you desire, Lambda provides you with the means to develop your own using standard spreadsheet formulas.

IsOutlierλ = LAMBDA(values,
    LET(
        UQ,     QUARTILE.INC(values, 3),
        LQ,     QUARTILE.INC(values, 1),
        IQR,    UQ - LQ,
        UFence, UQ + 1.5 * IQR,
        Lfence, LQ - 1.5 * IQR,
        MAP(values, LAMBDA(v, MEDIAN(v, UFence, Lfence) <> v))
    )
);

That would provide a list of Booleans that are then used for conditional formatting or filtering

= SORT(
      FILTER(Returns_data, IsOutlierλ(returns)),
  4,-1)

[cool2021]

@Peter Bartholomew ...since I already have VBA code outlier function script that works great as well as the code to auto select and run any one or multiple columns of data automatically through it and score back new columns on the spreadsheet...then what value is your LAMDA code to me? That's what I do not understand.

[Peter Bartholomew]

I simply coded what I thought you had specified.  I have no idea whether it is of any use to you.

I would always turn to native code of the Lambda function as a first choice, in order to achieve performance and deployment benefits. 

I turn to VBA for tasks that do not fit easily into the functional programming paradigm, such as event handling or interacting with shapes, provided the target environment is a windows desktop.  We all have our personal preferences though.

[AlexC84]

@cool2021 

You bluntly say: "what value is your LAMDA code to me? That's what I do not understand."

I refer you back to your original question: "Can I identify and flag statistical outliers and anomalies in datasets (eg. a column of data like $ Average Spent) using LAMDA?"

Your response is what I don't understand? What are you looking for

---

---

[cool2021]

@Peter Bartholomew ...yes, what you say makes sense. Thanks for the feedback.

[cool2021]

@AlexC84 ...question answered. I was trying to figure out whether I really need LAMDA to do what I need to do or if it is easier to automate in VBA. And VBA seems better for my task.

[mtarler]

@cool2021  I want to follow up on @Peter Bartholomew  comment on using Lambda vs VBA because I have a slightly different viewpoint.  I actually question whether Lambda will be more efficient than VBA in all cases and believe it will very much depend on the case.  If the formula needs constant updating based on new entries I definitely bet on Lambda but if it is more of a one-time or very in frequent thing (e.g. you would use a button to activate the VBA)  I think you would have to dig further into the code.  BUT all that set aside I go with Lambda over VBA because of compatibility.  VBA is not allowed in the web version and desktop at least on default will block it.  I just think if it can be done with a Lambda instead of VBA it will save you grief down the line to go that way.

[cool2021]

@mtarler ...thanks. Yeah, you are probably right. And, my application(s) for my outlier and anomaly detection stuff are more one time or very infrequent runs customized to very unique and different looking datasets. That's why I am staying in the VBA world for now because I always must do a lot of data shaping and, in the end, for each individual project, the data might look very, very different. And, in my case, likely reporting would be, at the most, a monthly basis, but, more likely one or two time deep dives on several columns of data (we're talking thousands of columns, not just a few) at one time automatically looking for outliers and anomalies. And, I don't want any formulas at all in the spreadsheet.....just the outputs (numbers).....because Web or Desktop versions, 8000 columns of data by 1000 rows of data means a spreadsheet with 8000*1000 cells with formulas AND data (if I use LAMDA) in them....opening up extra possibility of errors and taking up more computing space and capacity than necessary. Thanks for input though.

[mtarler]

@cool2021  I don't want to hijack this thread so if we have further discussion maybe we take it to a new thread?  That said, I agree that if you have manipulations you need to make and especially if the corresponding Lambda might get rather complicated (especially with the limited commenting abilities) and your comfort with VBA, that it probably makes good sense to stay in VBA.  That said, I hope you also have a good way to document the manipulations/code you used from data set to data set.  I don't know what it will be for but most applications require traceability (scientific, FDA, clinical studies, etc...)

[Patrick2788]

I treat VBA as a last resort.  I reserve code for tasks such as opening workbooks and gathering data and even then I might take a peak at flows available in PowerAutomate.  I believe Excel is at a point where it can handle most anything (w/functions, LAMBDA, and PowerQuery) related to calculations, re-shaping, cleanup, etc.   I much rather write a LAMBDA for a client than script vba. It saves me an explanation about workbook types, security, and such.