Taming The Beast

(stackoverflow rep: 7963, Project Euler 83/261 complete – slacker!)

It’s a little while back now (I’m a slow thinker) but Simon Murphy, developing on a topic found in a post by Dick Moffat, discussed some observations of Excel usage within organisations. He concluded that general user skill levels are largely unchanged but that the uses to which Excel is being put are becoming more complex and at the same time training quality is declining. As a result, it’s increasingly commonplace that such organisations are seeking to reduce or limit the degree to which user-written spreadsheets are used.

The way the future was

It’s a tricky one. We have this enabling technology that makes it possible for end-users to manipulate information in ways unheard-of before 1979 (the year Visicalc was launched). Since the introduction of the computer spreadsheet, the power and complexity (yes, ease of use too, but see below) of the products available has increased steadily, necessarily, if you think about it: if Multiplan, Lotus 1-2-3, Quattro Pro, Wingz, Excel et al didn’t offer something more then we’d still be using Visicalc.

Putting a spreadsheet program on every desk-top computer, as is commonplace nowadays, due to Microsoft’s bundling policy: most will need Word and Outlook, they’ll probably expect Powerpoint, God help us, so Excel is almost certainly going to come along for the ride. Once it’s there, all but the most dedicated non-fiddlers will find it, even if it’s only because they’re drawn in by their peers.

I’d say a not-untypical progression starts with using the electronic squared paper to make lists or tables, moving on to the occasional diagram and then one day there’s the discovery of arithmetic, at which point Pandora (which wouldn’t be a bad name for a spreadsheet program) has opened the box and we’re heading downhill on a slippery slope into the land of overused metaphors, not to mention end-user spreadsheets.

There, I’ve said it: “end-user spreadsheets”. Three words (or two, depending on how you count hyphens) to strike fear into the giblets of any spreadsheet professional. Why? Here’s a selection of risks to which an organisation may be exposed:

• the spreadsheet uses the wrong function(s): anyone know the difference between STDEV() and STDEVP(), for example? Which should you use and when? What happens when the wrong one is still not accurate enough? What other areas might be dangerous? How about the entire “Financial” category?
• dynamically-growing data ranges and fixed-size references: not all data is included in calculations
• the user spends more time playing with his spreadsheet creation than he¹ should – losing focus on the job for which s/he is actually being paid
• the spreadsheet becomes so useful that users other than the creator start to use it: the creator is now part of application support. Was that what you had in mind?
• because protecting spreadsheets can be tricky, and changing cell contents to perform “what-if” calculations is easy, formulae may be overwritten with new un-noticed and un-wanted constants.
• the spreadsheet acquires a noxious sludge of user-written VBA (shudder). You know, pages of global variables, no classes, 1500-line Subs, that sort of joy.

That’s the 5-minute, off-the-top-of-my-head list. This is the sort of stuff that has IT people waking up screaming in the middle of the night and contract support people (been there, done that) reassured of continuing employment for the remainder of their working lives.

How to address the problem without flushing both baby and bathwater?

First of all, if no-one is prepared to accept that there may be some real risk here, then then whole question is moot and you’ve already irrevocably lost a few minutes of your life getting this far. Sorry about that.

A review process needs to be put in place: what XLS files are being stored on the network? Can you see who is referencing them? Reading or writing? Are there patterns that might indicate that any of the problems above are being experienced?

Can you write a “spider” that looks around the user spreadsheet “corpus“  for frequent updates, evidence of VBA, all-round horribleness? Remember you’re not spying, you’re auditing.

As candidate files are identified, review them with the relevant user representatives to determine if the workbook is an signal for action to be taken. Such actions may include

• doing nothing – proactively chossing to do nothing is a perfectly reasonable option;
• adding the workbook to the portfolio of maintained and supported spreadsheet applications;
• setting up a project to address the business need in a more rigorous way;
• a bit of business process re-engineering to address any procedural deficiency that may have driven the need a an ad hoc spreadsheet.

Again, that’s the five-minute list. What other courses of action might one take to mitigate the risks of the enthusiastic amateur running wild with a spreadsheet program in one’s business?

 

---

¹ Let’s face it, folks, it’s usually a “he”

 

Shippity-doo-dah

(stackoverflow rep: 7576, Project Euler 83/257 complete)

In my band days we called it "Gaffer"

Reading Joel’s¹ Duct-Tape Programmer article this morning (in the interests of full disclosure I should admit without additional prevarication that I have a large roll of “Duck” tape in the second drawer of my desk as I type) one sentence smacked me metaphorically between the eyes:

“Shipping is a feature”

I was transported back a couple of decades to the time when the bank for whom I was then working discovered that it was building not one but two settlement systems (the things that ensure that what traders agree should happen actually does) in two locations: London and Zurich. In London we were targeting our DEC VAX/Oracle platform, while the Swiss were designing with their local Tandem Non-Stop installation. And we’d both have gotten away with it if it hadn’t been for that meddling CEO…

It was decreed that The Wise Men (external auditors) be appointed to review the two projects and pronounce which should live and which should consign its members to the dole queue.

The Wise Ones duly decamped to Zurich to spend a few weeks working through the cabinets of meticulously-detailed standards-compliant design documentation that had been lovingly crafted over the past several months, with coding about to start. Then they came to see us. It didn’t look so good.

dried-up and crusty now...

What documentation we had was months old (from a previous, aborted start of the waterfall) and coated in Tipp-Ex. Remember the white error-correction fluid we used all the time back in the 20th Century? When we still wrote “memos”? After a week of vagueness and frustration a set of presentations were scheduled for the Friday, at which we proposed to try to fill in the gaps.

Ing-er-land!

London won.

Yay us, but how? On most objective measurements we were deficient when compared with our continental rivals, even we agreed on that. But on that Friday afternoon, I got to stand up to summarise the differences, positive and negative between the two projects, as seen by the London team. I think what may have swung it was the part where I got to say “our system has been settling trades since 3 o’clock this morning”.

In about nine months, one team had done everything by the Book (don’t know the title, but I bet it had “Structured” in it) and had reached the point where they had, well, a book. Lots of books, in fact – they’d worked really hard. In the same time, we built a system and even better, shipped it. I don’t think anyone had written any Agile books by then – even if they had, we hadn’t read them.

Our team hadn’t done an awful job by any means, you understand: there’d been a few weeks of up-front requirement-gathering/scoping.  We had a massive data model that we Tipp-Exed down to the minimum needed. We had an outline architecture that, through luck or judgement, proved to be appropriate. Probably best of all, though, we sat with our users while we built their system. Better, as we built different features we moved around so we were always within speaking distance of our domain expert (I don’t think we’d done the whole “domain” thing then – we just called them “users”). So  we seldom got very far off track while stuff got built, and we were, with hindsight, feature-driven and relatively lowly-coupled/highly cohesive at the component level, all Good Things. Mostly written in COBOL, too.

Looking back, we were lucky: we didn’t manage to repeat the magic and fell back into time and cost overruns with the next couple of large projects. At least we were still being paid, unlike our erstwhile colleagues in Switzerland.

---

¹ I call him by his first name because we share so much; we’re only a few slots apart on page 13 of StackOverflow as I write this. Page-mates, don’t you know.

The Hard Way

(stackoverflow rep: 7284, Project Euler 83/252 complete)

My main work PC was upgraded to IE7 yesterday. That’s one less IE6-infected machine to worry about. Unrelated to that (I suppose) is that the Aventail VPN product that I have to use each day decided it wanted to upgrade. I’m still trying to figure out how to make that work on IE7 but fortunately I also have an older machine that seems to have been immune to the upgrade, so I switched to that.

After some back-and-forth, I saw the happy news that this was happening:

All going according to plan?

While this was cogitating, a message popped up, partially obscured by the progress dialog. So I moved it. The dialog, that is, not the message. And I saw this:

Dude, where's my progress bar?

How confused must the developer of this part of the installer have been to have built the progress bar as an entirely separate window? And how much more difficult must it have been to do it that way? I amused myself dragging the main dialog all over my desktop while the progress bar stayed resolutely where it was until the install completed.

Nothing Like A Name

(stackoverflow rep: 6622, Project Euler 82/252 complete)

I was suddenly plunged head-first back into the world of Excel/VBA this week. After a year or so of mostly Ruby coding, I was struck by how, well, wordy VBA can be by comparison. There’s a definite lower level of expressiveness, too, although that can be mitigated to some extent with extra (mostly mental) effort. While I was rapidly back up to speed and having a good time refactoring to make room for some new functions, I couldn’t help feeling that I’d moved on and was practising what had become a legacy skill. That has happened before: I left COBOL behind (without a second glance, it must be said) in 1990 and have not written a line in it since.

Anyway, I was thinking about named ranges in excel, and how they aren’t. Named ranges, I mean. Well they are, but they’re not. A name can refer to a range, but it’s really referring to a formula that references a range.

And by formula, I mean anything (pretty much) that can be input into a cell after an equals sign. The result need not be a range. Want to define a named constant, Faraday’s Constant (96485.3399) say? Just define a name with “=96485.3399” as the “Refers to” value.

In older Excels, you’d get this:

Named constant - in a Name

I’m a huge fan of using names to define OFFSET range for charting:

SwimDateCount, in turn, is =COUNT(SwimStats!$A$2:$A$500)

The excellent Professional Excel Development (I have the older edition; I’m sure the new one will be just as good when I finally start working with 2007/2010 in anger) covers this, I’m sure. I’d check, but it’s been borrowed by a colleague. Because it’s that good.

The only slightly less excellent Excel Hacks definitely does cover it. (Also borrowed, but I managed to find it). Hack #52 in my copy, if you’re interested.

A Long Time Dead

(stackoverflow rep: 6602, Project Euler 78/252 complete)

I realised just now that Visual Basic¹ went from 1.0 to 6.0² in about 7 years and stopped progressing 11 years ago. Even so, if I needed to do some COM automation work in an EXE, it’s still the tool I’d reach for, assuming I could locate my old Visual Studio 98 CD-ROMS, that is.

So by now VB’s pretty much been end-of-life longer than it’s been, er, life. Golly. It really is time Microsoft came up with a viable alternative. What I can figure out about VSTA doesn’t fill me with excitement, that’s for sure. VSTO, for the power-user at least (emphasis on “user”) still looks even worse. Neither offers, as far as I can see, any route for legacy VBA code, which is, I suppose, why VBA is still expected to be around for the foreseeable future.

Still, for the little it’s probably worth, we now have Make Office Better, within which we can rant about such importances. The buzz following its appearance seems to suggest that Microsoft still have some way to go. I am looking forward to sparklines in Excel 2010 though…

Mmmmm, sparklines...

¹ (the “classic” one, not the impossibly confusing – to a still-active VBA user)
² did we ever get a point release? I can’t remember any, although some of the ActiveX stuff did get service-packed.

Johnny 99

Sometimes I write legacy code. There, I’ve said it. The secret’s out, the dirty laundry’s aired and the cat’s out of Pandora’s box.

I don’t think it makes me a bad person. I understand the value of tests, especially in the highly incremental development world I currently inhabit, and I strive to use tests to drive my code. Sometimes it doesn’t happen, for many reasons, none of which I’m proud of, such as my Excel test framework being a little clumsy, there not being anyone around me to nag, the “quick” change that turns into just a little more uncovered code than I expected (but it works and the user needs it Right Now).

I understand that all that code sitting in the wild in an uncovered state is Legacy and represents an accumulation of Technical Debt that will have to be repaid. By me.

It’s not that code without tests is necessarily bad. I mean, heck, even Kent Beck sometimes flies without a safety net. If it’s write-once-and-throw-away code, then there’s an argument for just getting it done. But honestly, how often does that code get resurrected months, maybe years later? It’s when code needs to be changed, often requiring some refactoring in the process, that the absence of that warm, covered-by-tests feeling starts to be felt.

Still there, still doing a job, but just try changing it...

The trouble with beginning to repay technical debt is that interest tends to accrue, compounded, at some arbitrary (but almost always positive) rate. So no matter how trivial the original untest-covered change seems to have been, the longer you leave it, the more unpleasantness tends to have accreted around it by the time you come back to it. I suppose there’s always the possibility that you’ll come back to discover a glistening pearl, but in my programming life I’ve never returned to anything other than a thick coating of rust.

Worse than that, getting the encrusted nodule of code under test usually turns out to be painful: it’s seldom structured as it would have been had tests been used in the first place, so unwanted and hard-to-separate interdependencies are rife and the whole thing becomes, well, a bit tricky.

I’d been meaning to buy Michael Feathers‘ “Working Effectively with Legacy Code” for years and recently got around to buying it. Scott Hanselman’s interview last week with Mr Feathers was a serendipitous bonus. The book, even for non-Java or C++ programmers, is excellent, full of practical advice on how to break things up in a way that can let you get tests around the locus of change. In fact, when you see how just plain nasty working with C++ code can be, working with code in any other language starts to look like a breeze.

I wish I’d read the book earlier.

The Glove Puppet Programmer

While I prevaricate over the four (!) drafts sitting at various stages of incompletion in my “Posts” view, I’ll take lunchtime out to reminisce about a non-contemporaneous* colleague and his unique status as probably the most expensive programmer I’ve ever had to deal with.

This particular individual, who was either French or French Canadian, had the singular identifying characteristic of a name that was a literal translation of a stuffed children’s TV character from my childhood. This in itself should have been a warning. He had created the Credit Risk reporting system that a team of ten expensive contractors, myself including, were labouring feverishly to replace. On his own. As a series of Excel spreadsheets. With extensive VBA macros, coming from no discernible programming background. If you’re wincing, I’m not surprised.

Why am I asserting he was peerless in the cost stakes? Because the application he’d built actually worked. Badly, slowly, requiring regular manual intervention, occasionally obviously inaccurate and more frequently rather less so, but it worked. And we were trying to reverse-engineer it**, distinguish between the right and wrong parts and deliver something that was everything the old “system” was not. Well, almost: we did need our system to work.

This is one of the gems I discovered in the VBA:

    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 0).value = bv_sSTRATEGY
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 1).value = bv_sTRADENAME
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 2).value = bv_sTRADESTATUS
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 3).value = bv_sStructType
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 4).value = bv_sSTRUCTMODEL
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 5).value = bv_sTRANCHENAME
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 6).value = bv_iTRANCHENUMBER
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 7).value = bv_lTRANCHENOTIONAL

    ... are you getting the picture? We continue incrementing the offset for a while, until:

    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 29).value = bv_sCOMMENTDEFAULT
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 30).value = bv_sNEWTRANSID
    br_OutputWB.Worksheets(bv_sOutPutWSName).Range("A1").offset(br_lCount + 1, 31).value = bv_sCOMMENT

In case you were wondering, the “bv_” prefix signifies “By Value.” And yes, that does mean that each of those values appeared as parameter to the routine. I counted 32 in total. That’s thirty-two input parameters. You really should be wincing by now.

Several other routines also had the same (or 99% the same) code blocks. Our friend the cut-and-paste programmer must have really earned his money that day…

Oh yes, you should see an example of how our chum called his parameter-heavy subroutines. Consider acquiring welding goggles before looking at this, by the way.

Call SS_BuildMTMByTrade(br_OutputWB:=OutputWB, br_lCount:=lMMcount_SS, bv_dbFXrate:=GetFXRate(vMM_FXRates, RgCursor.offset(lOffset, Range("TRANCHECCY").Column - 1).value), _
bv_sSourceFile:=m_sSS_MM_Source & IIf(bv_sTradeNamePrefix <> "", bv_sTradeNamePrefix & RgCursor.offset(lOffset, Range("TRADENAME").Column - 1).value, RgCursor.offset(lOffset, Range("TRADENAME").Column - 1).value) & "_" & "MM" & "_" & RgCursor.offset(lOffset, Range("MM_CSB").Column - 1).value & "_" & RgCursor.offset(lOffset, Range("MM_R").Column - 1).value & "_" & IIf(RgCursor.offset(lOffset, Range("MM_PW").Column - 1).value, "T", "F") & "_" & IIf(RgCursor.offset(lOffset, Range("MM_SCS").Column - 1).value, "T", "F") & "_" & Format(m_dSS_MM_Asof, "yyyymmdd") & ".xls", bv_dbParticipationFactor:=RgCursor.offset(lOffset, Range("DEALNOTIONAL").Column - 1).value / RgCursor.offset(lOffset, Range("TRANCHENOTIONAL").Column - 1).value, bv_iShift:=RgCursor.offset(lOffset, Range("MM_R").Column - 1).value, bv_bIgnoreFirstCprty:=CBool(RgCursor.offset(lOffset, Range("IGNORECPTY").Column - 1).value), _
bv_dbSpread:=RgCursor.offset(lOffset, Range("DEALSPREADBP").Column - 1).value, _
bv_bUsePVFormula:=RgCursor.offset(lOffset, Range("USEPVFORMULA").Column - 1).value, _
bv_sSTRATEGY:=RgCursor.offset(lOffset, Range("STRATEGY").Column - 1).value, _
bv_sTRADENAME:=IIf(bv_sTradeNamePrefix <> "", bv_sTradeNamePrefix & RgCursor.offset(lOffset, Range("TRADENAME").Column - 1).value, RgCursor.offset(lOffset, Range("TRADENAME").Column - 1).value), _
bv_sTRADESTATUS:=RgCursor.offset(lOffset, Range("TRADESTATUS").Column - 1).value, _
bv_sStructType:=RgCursor.offset(lOffset, Range("STRUCTTYPE").Column - 1).value, bv_sSTRUCTMODEL:=RgCursor.offset(lOffset, Range("STRUCTMODEL").Column - 1).value, _
bv_sTRANCHENAME:=RgCursor.offset(lOffset, Range("TRANCHENAME").Column - 1).value, bv_iTRANCHENUMBER:=RgCursor.offset(lOffset, Range("TRANCHENUMBER").Column - 1).value, bv_lTRANCHENOTIONAL:=RgCursor.offset(lOffset, Range("TRANCHENOTIONAL").Column - 1).value, bv_sTRANCHESUBORDINATION:=RgCursor.offset(lOffset, Range("TRANCHESUBORDINATION").Column - 1).value, bv_sTRANCHECCY:=RgCursor.offset(lOffset, Range("TRANCHECCY").Column - 1).value, bv_dtTRANCHEMATURITY:=RgCursor.offset(lOffset, Range("TRANCHEMATURITY").Column - 1).value, _
bv_sBUYSELL:=RgCursor.offset(lOffset, Range("BUYSELL").Column - 1).value, _
bv_lDEALNOTIONAL:=RgCursor.offset(lOffset, Range("DEALNOTIONAL").Column - 1).value, _
bv_iDEALSPREADBP:=RgCursor.offset(lOffset, Range("DEALSPREADBP").Column - 1).value, _
bv_sUPFRONTFEE:=RgCursor.offset(lOffset, Range("UPFRONTFEE").Column - 1).value, _
bv_sCOUNTERPARTY:=RgCursor.offset(lOffset, Range("COUNTERPARTY").Column - 1).value, _
bv_dtTRADEDATE:=RgCursor.offset(lOffset, Range("TRADEDATE").Column - 1).value, _
bv_dtSETTLMTDATE:=RgCursor.offset(lOffset, Range("SETTLMTDATE").Column - 1).value, _
bv_sREPACKVEHICLE:=RgCursor.offset(lOffset, Range("REPACKVEHICLE").Column - 1).value, _
bv_sCOMMENT:=RgCursor.offset(lOffset, Range("COMMENT").Column - 1).value, _
bv_lADRNOTIONAL:=RgCursor.offset(lOffset, Range("ADRN").Column - 1).value, _
bv_sCOMMENTDEFAULT:=RgCursor.offset(lOffset, Range("COMMENTDEFAULT").Column - 1).value, _
bv_sNEWTRANSID:=RgCursor.offset(lOffset, Range("NEWTRANSID").Column - 1).value, _
bv_sOutPutWSName:=MMTEMPSHEETNAME, _
bv_BookName:=RgCursor.offset(lOffset, Range("BOOKNAME").Column - 1).value, bv_UniqueID:=RgCursor.offset(lOffset, Range("UNIQUEID").Column - 1).value, bv_KMVCorr:=RgCursor.offset(lOffset, Range("KMVCORR").Column - 1).value, bv_MarketCorr:=RgCursor.offset(lOffset, Range("MARKETCORR").Column - 1).value, bv_RunFlag:=RgCursor.offset(lOffset, Range("RUNLISTFLAG").Column - 1).value, bv_DataSource:=RgCursor.offset(lOffset, Range("SOURCETYPE").Column - 1).value)

The second line is about 900 characters long. Nine. Hundred. If you’re starting to want to make it go away, perhaps by clawing out your eyeballs, I don’t blame you.

Finally, for dessert, if you will, or maybe like one of those hot steamy towels presented at the end of some Indian meals, here’s our friend’s take on copying an array:

Public Sub CopyArray(ByVal bv_FromArray As Variant, ByRef br_ToArray() As Variant)
    Dim l As Long
    Dim i As Integer

    For l = LBound(bv_FromArray, 2) To UBound(bv_FromArray, 2)
        ReDim Preserve br_ToArray(1, l)
        For i = LBound(bv_FromArray, 1) To UBound(bv_FromArray, 1)
        Debug.Print bv_FromArray(i, l)

            br_ToArray(i, l) = bv_FromArray(i, l)
        Next
    Next

End Sub

From the blank line following the “Debug.Print” line I surmise that some operation may, in the distant past, have been occurring that made the routine more than a criminal waste of electricity. I hope the use of lower-case “L” is a soothing balm to your eyes, as is the continuous “ReDim Preserve” of the outer loop…

I don’t know the final cost of the project that replaced this, I elected not to renew my contract after nine months. I’d guess it didn’t exceed $10 million, not counting the possible losses incurred from trading on inaccurate information in the several years the “system” was live.

I’d call that dangerous.

---

*we worked at the same place but not at the same time, if that makes any sense

** specs? Don’t be ridiculous.

harrylillis.com would probably have been cheaper

Putting 2 and 2 together, Jeff Atwood appears to have paid* a fairly large (to me) sum to acquire the superuser.com domain. I wonder how much Microsoft paid for bing.com?

I switched my default search engine to Microsoft’s new beta search engine yesterday. Today I switched back to Google. Not that bing was all that bad – to be honest I couldn’t see much difference between what it gave me and what I see from Google. The background picture, which I guessed was of some Greek island yesterday (it’s somewhere different, but similarly attractive today) was certainly pleasant.

The killer was that after Firefox (3.0.10) reported that the page load was “Done” (and the results certainly seemed to be present) there was a delay – during which time FF froze completely – of about 12 seconds, after which my browser shook itself and woke up.

I don't need to search to find Microsoft being annoying...

Bing could be the best search engine in the world ever and I’d still not use it if that delay were present. I can’t believe it occurs for all users for all browsers but I’m only me and I prefer FireFox. It could be some interaction with one or more of my – fairly standard – plugins. Maybe I’ll try it again when the beta is done.

Your mileage, of course, may vary.

* Unless of course he was tweeting about another one, which is entirely possible.

Version control for Excel workbooks, Part 2

(stackoverflow rep: 4824, Project Euler 72/241 complete)

Not a month ago (about seven , actually) I started to gibber about source code version control and Excel. Then Stuff got In The Way and I was derailed. Well, Ruby-on-Railed, really – I’ve been almost totally immersed in intranet development and related matters and mostly having lots of fun.

Then along came Internal Audit (shudder). Some degree of consternation appeared to exist about the potential (albeit slight) vulnerability of our Source Control solutions (SourceSafe for Visual Studio and Excel stuff, subversion for the webby bits). Combined with the newly-available – and rather sexy – new enterprise SourceForge that had materialised, we bit the bullet and set out to put everything into the Company-Approved Solution. Which dragged me seven months into the past to ask again – how does one diff an XLS?

That’s not a rhetorical question, I do have a sort of answer. I’m thinking along the following lines:

• Some VBA component-extraction code in an add-in
• Perform the export on a Save or Close
• Save VBA references as a separate text file

I’m not going to get into dumping worksheet content, although there are arguments for doing so.

Excel exposes the handy WorkbookBeforeClose and WorkbookBeforeSave events on the Application object. We’ll need an Application object and it’ll have to be declared WithEvents, so we need a class. I’m going to call it WorkbookDecomposer:

Option Explicit

Public wkbk As Workbook
Public WithEvents xlapp As Application

Private Sub xlapp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Set wkbk = Wb
    Decompose
End Sub

(We’ll have the same code for the Close event). Each component can be exported, and the references can be dealt with something like this:

Public Sub ExportReferences()

Dim ref As Reference

    With New FileSystemObject
        With .OpenTextFile(.BuildPath(wkbk.path, wkbk.Name & ".references"), ForWriting, True)
            For Each ref In wkbk.VBProject.References
                .WriteLine RefToString(ref)
            Next
        End With
    End With

End Sub

To start the thing, we can have a module with a little Sub such as:

Option Explicit
Option Private Module

Public decomposer As WorkbookDecomposer

Public Sub StartDecomposer()
        Set decomposer = New WorkbookDecomposer
        Set decomposer.xlapp = Application
End Sub

Now, as long as we don’t lose state, running this code once will give us an object that will automatically export copies of our code for storing separately in our code control system. So we can diff. The decomposer can be extended to shell out useful source control instructions such as commit, perhaps automatically, perhaps not.

I don’t like that “as long as we don’t lose state” thing. When we’re coding in VBA we lose state all the time. The best I can come up with is to use an OnTime macro to keep trying to restart the decomposer object:

    If decomposer Is Nothing Then
        Set decomposer = New WorkbookDecomposer
        Set decomposer.xlapp = Application
        Debug.Print "Decomposer (re)started @ " & Now
    End If
    Application.OnTime Now + TimeSerial(0, 0, 10), "StartDecomposer"

It sort of works, but it’s potentially intrusive when we have incomplete/invalid code (hardly unlikely in the scenario under consideration) and the OnTime macro may fail to fire for many reasons.

What are the alternatives? Custom external scripts? An external process that monitors changes on XLS files and auto-exports?

Fifty Candles (each)

(stackoverflow rep: 4675, Project Euler 72/240 complete)

The first programming language I learned was a peculiar version of BASIC, running on the ICL mainframe installed during my abbreviated university career. I seem to recall it used a magnetic drum as its primary storage device. My second programming language, and the first I was ever paid real money for working with, was COBOL. It was the lingua franca of business computing, had been around forever and I started to learn it early in 1979. It turns out that “ancient” old COBOL had at the time only been around for about 20 years. As indeed had I, and this year we both turn 50.

Ah, the fun we had!

When the only tool you have is a hammer, everything looks like a nail. (I remember vividly a school “handyman”, inexplicably nicknamed “Sausage”, who would repair desks by applying a large hammer to drive screws). I used COBOL for things to which it really wasn’t suited. For a couple of years, that didn’t just mean writing peculiar code, it meant punching cards on an IBM 029 punch machine.

Programming for work was done on paper coding sheets, converted into machine-readable format (80-column punched cards) two evenings a week by Hazel the Punch Girl. Times change.

Once upon a time all code was written on these

It wasn’t as bad as you might think: we only got to compile or run our code twice a day anyway, the rest of the time involved pencils and paper. Lots of paper. Some things change less than others.

Anyway, designing, coding, compiling and testing/debugging a program was a mammoth task: it took months. Lots of months. I think in the three years I was a programmer in my first job I wrote about eight complete programs.

Virgin input to the 029...

In all, I was primarily a COBOL programmer for about 12 years, although there were secondary activities in PL/1, Fortran and C in the same period. I haven’t written a line since some time in early 1990. Can’t say that I miss it, not even now that it has object-orientation, a scary concept for a language that didn’t even use to have data scoping below “global”.

So happy birthday to COBOL, whenever it falls during the year. I can’t say I miss you but you paid the bills for over a decade, and for that I’ll always be grateful.