search

microsoft / advanced-formula-environment

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

Improve linter #81

Open santiago-afonso opened 2 months ago

santiago-afonso commented 2 months ago

Hello. I've come across an add-in which uses a similar VS Code like editor to AFE, and it has a much better linter (https://pages.store.office.com/addinsinstallpage.aspx?assetid=WA200001584&rs=es-ES&correlationId=2ca63351-445b-37ae-91ce-b498fcc38dea), even in the free non-AI version. For example it suggests replacing nested IF with IFS. There seems to be a lot of room in the AFE to improve on things like that.

Thanks!

SergeiStPete commented 2 months ago

By the way, not sure that's a good idea to replace nested IF with IFS...

santiago-afonso commented 2 weeks ago

By the way, not sure that's a good idea to replace nested IF with IFS...

Why not? (Honest, non-retorical Q.)

SergeiStPete commented 2 weeks ago

IFS evaluates all conditions before returns the result. Nested IF stops on first TRUE. From performance point of the the latest is more preferrable. Another point, IFS returns #N/A if no one condition met. IF returns FALSE. What is better depends on concrete scenario.

santiago-afonso commented 2 weeks ago

I wasn't aware of the last point, and both are valid. But the latter can be addressed by the refactoring algo simply adding TRUE,FALSE as the last two IFS arguments. Then it'd behave as the nested IF, except for performance.

SergeiStPete commented 2 weeks ago

With last argument as TRUE we meet at least one condition. The question is in different behaviour if no one condition is met.

santiago-afonso commented 2 weeks ago

With last argument as TRUE we meet at least one condition. The question is in different behavior if no one condition is met.

Are these two not equivalent? image

(admittedly not the cleanest example but it goes to your point about no conditions met)

SergeiStPete commented 2 weeks ago

Okay, they return the same. My point was - with many conditions nested IF works faster, depends on priority of conditions. In general, my impression AFE is died, as many other Garage projects. Perhaps it could be integrated into main Excel due to relatively high demand. Perhaps it will continue to survive with very limited or no support. Will see.

jack-williams commented 2 weeks ago

much better linter

Aside from the suggested formula rewrites, are there lint rules that stand out as being particularly useful? In AFE we do have comprehensive error and syntax checking.

In general, my impression AFE is died, as many other Garage projects.

We released our debugger in December! AFE is not dead, and Nick has be continually fixing reported bugs. However we do have limited bandwidth for new features based on current team priorities.

santiago-afonso commented 2 weeks ago

Aside from the suggested formula rewrites, are there lint rules that stand out as being particularly useful? In AFE we do have comprehensive error and syntax checking.

Take a look at these optimization rules. https://www.10studio.tech/docs/spreadsheet-optimizer#optimization-rules I'd probably also suggest flagging INDIRECT and volatile functions due to their slowness, and suggest alternatives. There's probably room for SWITCH statements and other optimizations. But even automatically flattening IF statements would be a great help!

Also, do check out that guy's (several) addins, you'll find them very interesting, even if needing a few more minutes in the oven.

We released our debugger in December! AFE is not dead, and Nick has be continually fixing reported bugs. However we do have limited bandwidth for new features based on current team priorities.

It's great that you're continuing development, and I hope you get the resources needed to push forward.

santiago-afonso commented 2 weeks ago

This probably goes beyond a linter, but I wrote a macro that refactors formulas as LETs. Ie it takes each references and gives it a name in a LET. Then I F2-rename each. Super useful for reverse-engineering monster formulas.