App or workbook Level 2

I totally accept the arguments put forward that the print thing I released as a worksheet, could/should have been an app level add-in instead.

And I totally agree that centralising code and minimising duplication are excellent ideas, and I certainly try to do that where appropriate.

On the other hand I believe one of the key reasons spreadsheets have become so critical to most orgs is that they are not centralised – they devolve power, and decentralise control. If centralising code etc was so popular with users, why are they continuing to use massively duplicated spreadsheets instead of dumb clients to super smart central servers?

So I’m trying to weigh up these 2 opposite arguments, centralising code is good, but spreadsheet users so far seem to prefer decentralised.

Is the world moving to a more centralised style but just slowly, or are there real benefits to keeping things decentralised that spreadsheet users recognise and software devs ignore?

I worked on a project once to replace a complex spreadsheet with a web app (VB6 ASP, SQL Server etc). We did it, it totally worked, passed all testing, dev team v. happy. The users never once used it, they continued using the same old spreadsheet(s), with all that bad duplication stuff.

Do you think now those same users could be persuaded to use a centralised app? I think the technology is much better now than then, and things like VSTO with click once deployment, on-line/off-line working, simple updating seem to make it more appealing, or should that be palatable?

To me there seems to be a cut off somewhere when decentralised becomes too unwieldy and a more centralised approach would have been better. Key words being ‘would have been’. By the time you know how widely the thing will be distributed its too late to decide app level or workbook level.

Maybe its:

  • optimist (everyone is going to use this – best make it centralised and easy to manage) v
  • pessimist (this will get used only a handful of times, better make it simple)

In the case of these little utilities I reckon that translates as:

  • if few users use it in lots of wbs – then app level best
  • if loads of users use it in just a couple of s/s (ie multi user s/s) then probably wb level is best

What do you think?



10 Responses to “App or workbook Level 2”

  1. Harlan Grove Says:

    I think you need 3 classes of spreadsheet user/developer. Class 1: those who mostly develop spreadsheet models other people use. Class 2: those who develop spreadsheet models they and their co-workers use as well as using models produced by Class 1. Class 3: those who are just users, not developers, who might occasionally enter a half dozen formulas in a workbook.

    Class 1 should worry about where the code should go, but classes 2 and 3 won’t spend any time considering this because their IT departments won’t put any centralized solutions they might produce on a centralized server. Spreadsheet ‘development’ INSIDE most non-IT departments has evolved as militantly decentralized because many attempts to centralize have been squashed by IT. At least that’s been my experience.

    As for your web app being shunned and users continuing to use existing spreadsheets, “We did it, it totally worked, passed all testing, dev team v. happy. The users never once used it…” Was the web app’s UI radically different than the spreadsheet? If not, was it slower? Was it harder to import data into it? If so, were any of these mere users involved in the web app’s design? Were any users involved in reviewing drafts of the user guide? Was there a user guide?

    In my experience, unless NEW is much easier to use, much quicker or provides many more features than OLD, OLD will thump NEW every time if users are given the choice.

  2. Simon Says:

    All good points Harlan, especially the ‘militant’ part, that is so true.
    That web app was a disaster in every way, the most ill concieved system I ever worked with (excluding maybe an Oracle OFA implementation). The only good thing about it was as career development for the designers, analysts and us devs.
    It was a classic IS depatment say ‘you have to use this now’, users say ‘err.. no actually our current spreadsheets are better’, which they were in every way that the users cared about.
    I went in at the tail end to convert some unpopular asp pages to workbooks – too little too late I’d say.
    I agree NEW has to really offer something the customers care about to overcome inertia (ribbon anyone?).

  3. Marcus Says:

    “reason…spreadsheets have become so critical to most orgs is that they are not centralised…”
    Correct, but only to the extent that the typical business user wants flexibility in being able to manipulate, present and transmit their data. I feel that one of the primary issues business has with centralisation is that it infers the IT Dept will control their data and place a nice, thick layer of red tape and complexity over getting to their data.

    “…why are they continuing to use massively duplicated spreadsheets…”
    Because they don’t no any better. It’s rare to find an accountant self trained in VBA who will whip up an XLA and the distribute to their colleagues. It’s mostly organically grown macros. Sometimes the spreadsheets are used as they don’t know how to extract just the data they need effectively and efficiently from Access or another database. How many times have we all see a spreadsheet with hundreds or even thousands of VLOOKUP’s which could have been replaced with a single SQL statement?

    As Jon alluded in the previous post, it’s important to separate the data from the business logic. Sometimes the business logic is so intrinsically entwined with the data that it’s impossible to do. Sometimes the business logic it generic enough in it’s application that it could be applied to almost any set of data, as in this case.

    I’ve worked on similar projects which succeeded but essentially failed as the users continued to use their cobbled together spreadsheets. It’s also important to ask the users why. In this case it came back to control – the business didn’t want to relinquish control to the It dept who they perceived as being too slow to respond to system enhancements.

    There’s another option to the usage of the little utilities: lots of users use them in lots of workbooks.

    All the best – Marcus

  4. Marcus Says:

    P.S. Any thoughts on the add-in mock-up I sent through?

  5. gobansaor Says:

    I was for most of my career a humble excel “user”, although I worked in IT, I , like most of my business user colleagues, never received training in “advanced” Excel features – or any Excel training for that matter.

    I didn’t use add-ins because I didn’t know how and for the tasks I was using Excel for, I couldn’t justify the time necessary to find out. Later, mainly through necessity (moving to work with locked down ERP systems i.e. Excel became the only dev. tool I had) and opportunity (the internet is a great teacher) I started to use more sophisticated features that allowed me to create micro applications, applications that might in the past have be developed and deployed using database technology front-ended by Oracle Forms or VB (or nowadays by a Web app).

    As a professional developer I’m firmly in the centralised control camp but as a user I like my spreadsheets to be self-contained documents, Excel like Word/Powerpoint are document centric technologies, and documents are something that people in general are very comfortable with.

    As for separating code from data, like Mum and apple pie, we’re all for it; but, in the vast majority of spreadsheets the code (i.e the business logic) is in the form of formulae and is an intrinsic part of the document. By all means develop utility add-ins but let’s now forget that the essence of a spreadsheet is data and logic combined in single document. If we, as developers, find ourselves straying too far from that model, perhaps it’s a sign that we should look for a different backend development/deployment technology while perhaps continuing to use Excel as the front-end (this is the approach many BI projects use).


  6. Rob Bruce Says:

    Let’s not forget the .xlt template model.
    I’ve developed many little apps that are based on the idea of a central template that the user creates a new workbook from, adds his/her own data and/or parameters, works with for anything between a day and a month, before archiving it and starting afresh.
    Here it’s easy to support upgrades and bug fixes simply by replacing the central xlt template, so the integration of data and logic works perfectly well.
    I’m always surprised how little xlt templates are used in comparison to, say, Word templates.
    You can also use an auto open to find out what ‘mode’ the workbook was opened in:
    Private Sub Workbook_Open()

    If Len(ThisWorkbook.Path) = 0 Then
    ' New workbook created from template...
    ElseIf ThisWorkbook.FileFormat = xlTemplate Then
    ' Opened in development mode
    Application.VBE.MainWindow.Visible = True
    ' Opened in normal usage mode
    End If

    End Sub

  7. Marcus Says:

    Hi Simon,

    I sent that add-in mock up through again. Let me know if you got it.

    Cheers – Marcus

  8. Simon Says:

    Got it Marcus
    I’ll email you privately.
    Crappy ISP I hate to imagine how much useful email they delete before it gets to me.

  9. sam Says:

    I never understood the difference / advantage of a Read only file v/s a template…


  10. Marcus Says:

    Hi Sam,

    There’s a couple of benefits. One is that if a user double clicks a template they automatically get a copy of the template. If they click Save they are automatically prompted to Save As. As an unsaved file doesn’t posses a path (in the file name) it’s also easy to implement code which depends on a saved file path. But if you prefer or are more comfortable simply making a file read-only there’s no real reason not to.

    Cheers – Marcus

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: