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.

5 Responses to “Excel industrialisation”

  1. John Greenan Says:

    Hi John.

    The whole series of posts can be accessed using this link to the tag http://blog.alignment-systems.com/search/label/Excel%20Industrialisation

    The move to take code from the desktop to the server is an interesting development – in another field there is https://github.com/kripken/emscripten

    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.

  2. etrading Says:

    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.

  3. John Greenan Says:

    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 am not a fan of Excel-DNA, but I respect what they have done.

    I am not sure if it’s just me, but I had written a number of C# RTD servers (clients of middleware running over the network) to get data into Excel in the past. As time goes on I knew the theory but had forgotten how to implement one. Writing that code at http://blog.alignment-systems.com/2014/05/excel-rtd-server-implementation-in.html and http://blog.alignment-systems.com/2014/10/excel-rtd-server-implementation-in.html with source at https://github.com/JohnGreenan/ExcelRTDSimple was a real eye opener in terms of the amount of work you have to do to get C# to play nicely in a COM world.

    In an earlier post I covered the future of VBA http://blog.alignment-systems.com/2014/11/excel-vba-does-it-have-future.html.

    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.

  4. etrading Says:

    Added a link on the Excel page to a Wilmott discussion thread on DDE vs RTD. Thanks to JG for this…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s