Month: October 2013

• Translated from: Chinese Version

Some in the software industry and the computer science academia are obsessed with functional programming, thinking that imperative programming is an evolution dead end for software quality. Maybe functional programming can demonstrate its value in some ways (like functional in small, OO in large), but being obsessed usually doesn’t result in anything good, let alone being skepticism about everything else on the basis of that obsession.

Why is functional programming in such a contradictory situation as that some people are obsessed with it yet it remains marginalized? It’s hard to make a strict argument. However, a recent experience of helping a friend improve his company’s financial reporting tool was enlightening me on this argument. The company’s financial reporting tool was actually very primitive, previously just hand craft a couple of summary sheets in Excel, according to some raw data sheets. My improvement was to write two scripts, in Visual Basic for Application, to automate this process. This improvement was small, but it reduced a two-day monthly task to one taking 20 minutes. The program itself only takes 10 seconds, but because it’s somewhat rudimentary, manual checking and handling special cases take another 20 minutes. (Therefore this probably serves another evidence that Excel might be the greatest computing platform on this planet — except for its debugger that can’t be paused preemptively, forcing user to kill Excel whenever there’s an infinite loop in the executed code.)

Before I volunteered to help, a non-programmer in the company who had been using Excel intensively for many years also tried to automate this process. By the way, his position was Deputy Director of Finance, which made me felt better in that, although the was supposed to be voluntary (I got some gift payment later, but no upfront promise), my help did not seem devalued. A non-programmer Excel expert refers to someone who is very proficient in all Excel features except for VBA. He worked overtime on weekends to create an automation sheet based on and only on Excel formulas. The result was tragic, though. Every time his automation sheet was opened, Excel would freeze for two minutes. Users needed to master complex usage rules, undergo several hours of training, and the spreadsheet was filled with cells not for useful result but storing intermediate results. One could even say that the entire structure of the sheet was designed for machine processing, not for human reading.

I once admired those who were skilled at mastering Excel formulas. Writing VBA for Excel always felt like copying files in batch in Unix by writing C code rather than using a shell script. Isn’t Excel formula a more native Excel method? Well, Excel formula had looked like more native until this time I encountered or obtained:

• Such a complex problem as this monthly sheets summarization;
• The experience of writing a VBA program to tackle a real world issue; and in comparison,
• The observation to the pitiable solution which the non-programmer Excel formula expert tried to work out, and failed.

At this point, it comes to me as an epiphany that VBA vs. formulas is the paradigmatic difference between imperative programming and functional programming. All along, Excel has been a multi-paradigm computing environment. What’s more interesting is that it started as a pure functional environment. Only later, the introduction of VBA make Excel embrace the imperative programming approach that academia often frowns upon, as an advanced feature. That says something about the trend, whether people moving to or away from (pure) functional programming.

Why do some people have illusion for functional programming? Maybe it’s not because of academic obsession, but simply because the functional approach appears to be easier to understand. What kind of a user is who cannot understand Excel formulas? As simple as writing a line like =sum(c1:c10) in a cell! But what is VBA, this daunting programming language (along with its dumb debugger)? Then Excel formulas, seemingly simple and capable tools with infinite combinations, can not scale up along with problem complexity’s growing. The amount of code in a VBA solution grows linearly with the problem’s complexity, while the complexity of a formula-based solution grows exponentially. The VBA solution isn’t perfect, but perhaps it is only short of a better debugger to perfection, so is imperative programming in general.