Complex formulas are complicated to deal with. To make the most of your time, you need to ensure you are using the right tools. If you are writing code, you use a code editor... if you are writing formulas, you use this formula editor!
What it does
Formula editor integrates a fully-fledged code editor into Microsoft Excel. You can write your formulas with all the features of modern source code applications, including auto formatting, auto completion, syntax highlighting, caching, keyboard shortcuts, and more.
How we built it
Challenges we ran into
Since excel formulas do not follow any standard coding syntax of modern programming languages (that we know of), we had to create our own code formatting rules and ran into a significant number of edge cases.
We were limited by the lack of two-way data flow in the add-in model. We came up with some clever and unique solutions to make sure that when changes were made in the Excel client, our add-in was aware of these changes and could automatically update the appropriate features dependent on these changes. (Microsoft, please add some event listeners for us!!!)
As with any application, performance was at the top of our priority list. While the installable version of Excel performed well, optimizing our app so we had equally good experience in the browser, had us coming up with some creative solutions.
We would have loved to build some end to end testing into the app, but we couldn’t find accessible libraries to help us. Since the app is required to run in an office environment, we couldn’t find a way to simulate this to automate our end to end testing.
If we missed something, please comment below, we would love to know!
Accomplishments that we're proud of
Integrating two different systems, Microsoft Excel and Ace/Brace editor to communicate seamlessly with each other.
Without the predefined structure of a framework, we were able to explore how best to approach this app. By not being restricted to framework guidelines, we kept our app lean and improved the functionality and readability of our code with each pass. If nothing else, we came out better programmers in the end.
What we learned
There is no such thing as a small project. This app started off as a one-week project for us, and as we finished functionality, we kept coming up with features and multiple ways to improve user experience. This was a true test of agile development for us. As we completed functionality, we continually added more, while upgrading our architecture to be more extensible to new user requests.
We thought we were Excel experts, but as we got people to test, we found new and interesting ways people would break the editor. This was challenging, but also a great learning experience in seeing how all people use Excel and how we can make their experience better.
What's next for Formula Editor
- Submit to Microsoft Office Store.
- Integrating with OneDrive, so you can save/download existing or previous editor files to quickly recreate formulas and functionalities you reuse often.
- Adding opt-in features for setting editor preferences, for themes, highlighting, auto format rules, and more.
- Bolding the help text parameters as you click or arrow through the function code.
- Adding web workers for a faster experience.
- Get enough downloads so Excel builds this natively and gives us all the credit!
Burn Dev, LLC, based in Hoboken, NJ. Questions, comments, concerns, or suggestions? Hit us up at firstname.lastname@example.org.