microsoft
Improve linter
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!
By the way, not sure that's a good idea to replace nested IF with IFS...
By the way, not sure that's a good idea to replace nested IF with IFS...
Why not? (Honest, non-retorical Q.)
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.
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.
With last argument as TRUE we meet at least one condition. The question is in different behaviour if no one condition is met.
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?
(admittedly not the cleanest example but it goes to your point about no conditions met)
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.
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.
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.
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.