Excel industrialisation

April 3, 2015

John Greenan has produced an excellent series of posts on Excel VBA Industrialisation on his blog. It’s a topic dear to me, so I figured I’d better respond. In his posts JG presents a series of VB Extensions based techniques to enable the export of embedded VB from a spreadsheet, so it can be version controlled, as well as techniques for error logging and reporting. The code is out there on github, and it’s a valuable addition to the public domain, especially since there are several commercial offerings addressing this space. For instance, spreadgit, ClusterSeven and Finsbury Solutions. JG kicks off his discussion in part one by observing that VBA is in the doldrums, and that the cool kids are using MEAN, Scala, OCaml or Haskell. Sure, the cool kids are never going to use VBA. But that’s not just because other languages are cooler, it’s because VBA and the latest programming languages are aimed at completely different audiences. Scala, OCaml & Haskell are for developers, and Excel is for non developers, end users, business users. The very reason for Excel’s phenomenal success and ubiquity is because it enables end users to create software solutions. Apparently there are eleven million professional software developers in the world. But even those eleven million can’t meet the world’s demand for software, so end users have to generate their own solutions, and they use Excel to do it. The result is, as JG points out in the comments to part six in his series: “In many cases the requirement for Excel Industrialisation is for a firm with an existing portfolio of ‘000s of spreadsheets that cannot all, in a cost-effective manner, be manually rewritten to conform to a coding standard.”

A version control system is an important part of controlling those portfolios of end user developed spreadsheets. However, it solves only part of the problem. Another major underlying factor that causes so many spreadsheet problems is their manual, desktop operation. Since Excel is a desktop application, Excel spreadsheets must be manually operated by their users. Users have to start up Excel, load the sheet, key in unvalidated data, hit F9, and then copy & paste or email the results out. All of that is error prone. And all of this manual operation is a major factor preventing any organised, systematic testing. All of these problems were writ large with the London Whale. All these problems could be resolved if we could decouple Excel as a development environment from Excel as a runtime. It’s great that end users can develop their own solutions in Excel, but it’s burdensome and error prone for this solutions to be operated manually on desktop PCs. Those solutions should be automated, resilient and scalable, and hosted by a server side rumtime. That, of course, is SpreadServe.

The replacement of Excel by “the next big thing” has been predicted for a long time and maybe eventually it will come true. From my view I could imagine that https://ethercalc.net/ or similar could be a contender.

From my view of SpreadServe I think it could be very interesting but the blocking issue for me is the lack of integration with DDE and RTD.

If you can get that running that I would be interested in seeing a demonstration.

John: links adjusted – thanks. I need to update my site as I do now have RTD support, though it’s not complete. Your own series of posts on RTD have been a great help. I’m implementing the other side since SpreadServe emulates Excel in being the RTD client. I’ve been testing with PMStockQuote.xll, an Excel-DNA based C# XLL that pulls quotes from yahoo. Making it work has been an interesting technical journey taking in __stdcall, .Net interop and COM. I’ve discovered new respect for MS’s maintenance of backward compatibility.

Excellent -good to hear you have added RTD support.
Microsoft have always been excellent at backwards compatibility – one of the legends of this has been a gentleman called Raymond Chen (see his blog at http://blogs.msdn.com/b/oldnewthing/). There is a great article on the topic by Joel Spolsky (http://www.joelonsoftware.com/articles/APIWar.html) that covers the degree of work that Microsoft would do to maintain backwards compatibility.

I tried to get some folks from Microsoft to comment but they were not able to do so in a public forum. It’s a shame as the perception is that Excel VBA is on life-support yet it’s probably the most widely used (abused) IDE on the planet.