I haven't tried this yet, but if it works well, the implications towards rapid Business Intelligence development are great. In the past, when a user has needed some 15-year-old, macro-infused spreedsheet turned into a more streamline, business-wide process, any data entry components would require roping in webdevs to get the job done, as BI developers don't usually come with a web skillset. The idea that BI developers could just use the spreadsheet to generate a web app not only cuts down the expertise needed, but also solves the problem of training old-timers to use the new process.
Excel and its open source variants achieved total market dominance by providing a seemingly future proof technology platform and a generally maintainable (usually by a single human) solution for business. The biggest hurdle with converting these programmable documents into applications has always been long term the maintainability of the replacement solution. Even Microsoft had it's fair share of failures developing simple programmable database solutions (Access, Visual Fox Pro) to meet the needs of businesses and governments that "just needed a little more than what Excel could provide". It's a marketing pitch that never fails because it underplays the cost of maintenance of the new solution and the real value of the spreadsheet.
I welcome solutions that serve this need for "better than excel" without succumbing to its pitfalls but I'm not holding my breath. Even this article just outlines a solution that could be implemented in Excel with a little extra knowledge from the user, making it "no better than excel" for the specified use case.
Interestingly, Excel et al. achieved that status without the "no-code/low-code" mantra that is the rage in sales these days, where "citizen developers" are expected to relish in GUI editors severely limited in the logic they can accomplish.
On a turing-completeness spectrum, they live on the opposite half of where VBA+formulas stand proudly. Therefore, it is no surprise that for anything beyond "out-of-the-box", it is expected that an experienced IT developer/consultant will be needed to figure something out.
A decent reason to disallow custom code is that these platform are to be upgraded regularly, yet every major version seems to bring its lot of breaking changes while my decade-old macros still run like a champ. Oh the irony.
What you describe here is exactly what we are doing with a tool called EASA. It enables us to very quickly create web apps using an existing spreadsheet as the logic module. You don't need to be a dev to do this - our BIs find it straightforward - the closest you get to code is a bit of SQL.
It works well - we have successfully applied this to about 20 really critical spreadsheets for functions such as pricing, deal valuation, risk analysis, and even for engineering analysis. Users simply use a web app (that can look like the underlying spreadsheet - or not). The system is also integrated with SAML, so authentication is there, and you can also integrate the apps you create with 3rd party systems - we are looking at launching Excel-based pricing apps directly from SF.com, for example.
Anyway, the site is https://easasoftware.com/ - there are some vids there which probably do a better job of explaining it than I have done here. It's not just about putting an app on top of a spreadsheet, but really putting an app on top of processes that involve spreadsheets. And you can use things other than Excel as the logic module, like Matlab, R, Python - in fact we are dabbling with using EASA to deploy ML models created in Python.
I don't know if a BI Developer with a web skillset would solve this problem. I worked as a BI Developer and was solely hired because I had mucked around with the COGNOS and its API's and written a few analytics based web apps. The reality was that the rest of the team didn't want to support anything outside COGNOS/MicroStrategy nor did the project managers foresee web apps as a solution to any problem.
Companies like Celigo created an industry out of allowing Excel users to shoehorn pretty much anything into a spreadsheet and then have it interact with their major accounting/ERP systems.
I thought it was clear in my original comment that it would require more than one developer before - one for the BI process and at least one for the data-entry component. Given that most BI teams don't have a web developer, that also spreads the process over multiple teams, which can slow down maintenence. And I'm not sure why you think it'd take a whole other developer to manage this tool. BI developers are used to picking up new tools and languages (my BI job requires that I know how to use SQL, python, MDX, and C#, and numerous third-party applications).
And when I'm referring to "old timers", I'm referring to users, not the developers. Why would a user need to understand that snippet? Are we even talking about the same thing?
I can see exactly what that code is doing on a first read. It's pretty procedural. I don't like the range reference.
> "C6:G6"
I think the bigger problem is it's not going to be a developer maintaining this. It's going to be a pretty literate VBA user and nothing will go wrong. Inevitably they will move on and pass over the code, the 'user application' to someone who will feel reassured they can read it too, but then when it comes time to make a change, they won't be able to. And then it'll be passed over again and become a black box.
A developer (if there's funding) will not like picking up something undocumented, then it becomes a project (less funding). The process, a magical black box that may or may not have stopped working also loses documentation. Governance is lost. Particularly but not exclusively in financial applications, potential for misuse and loss, either accidentally or fraudulently, has been introduced.
So, the above looks (mainly) OK to me. But only because I know to insist on maintaining a manual backup or otherwise maintaining knowledge of how the black box works.
The problem usually isn't the webdevs or the BI developers but the fact that it's logical thinking and engineering skills that make a supportable application. Without those skills, bad software gets made, and bad Excel spreadsheets can be just as bad as bad webapps.
