Spreadsheet modes

That was an interesting debate about using formatting info in functions we had the other day. Clearly both sides have valid views and to a large extent it comes down to what you are doing.

Building a complex mission critical system that has to withstand 3-5 years of hamfisted use is quite different from quickly needing to clean up some data to get an estimate out before a pressing deadline (Barclays I’m thinking of you).

In VB.net you have the option to use strict, indeed in VBA we can choose to not use Option Explicit for quick hacking around or turn it on for stuff thats a bit more involved or when more rigour is required. In VS too you can set the level of warnings.

Maybe we need this ‘mode’ functionality in the grid?

Hacking around mode: anything goes, mega formulas, 60 levels of nested ifs, functions depending on formats, scattered dependency trees, functions with side effects in other cells?, perhaps even access the the command table from UDFs? etc etc. multi million rows?

Production mode: much more rigid ‘best practice’ style, perhaps with the option to bypass a rule. lets say no more than 3 levels of nesting, but as a dev you can use your professional judgement.

Then  when ever a workbook opens, instead of ‘Active content has been disabled’ it could say, this workbook conforms to Production mode guidance, or not.

What do you think?

What sort of things would you put in each category?

If this were an add-in would you contribute code? would you buy it?

cheers

Simon

Advertisements

4 Responses to “Spreadsheet modes”

  1. Rob Bruce Says:

    Looking at this from the other end, an addin like this would fit nicely into a larger suite of tools to check for best-practice compliance on any given spreadsheet project.

    On the other hand, it would need to be configurable in order to fit into any number of paradigms of ‘best practice’ and this always has the possibility of making the tool too complex for all but a tiny minority of people (those of us who come here, basically ;-)

    Yes I would contribute code. And I might buy such a thing. Not both, though!

  2. Ross MIE Says:

    Yeah very good idea, excellent idea in fact! I think the details would be a really git to sort out, but I think the principle is really good, it’s not unlike marking a SS against a list of criteria and giving it a score in many ways – would it run in to VBA as well?

    I’m not sold on the idea that it would work as an addin. I think it might have to be part of Excel, but maybe we could get it going as an addin to start off with.

    Would I contribute code? Would anyone want my code! Maybe!!! to both :)

  3. Harlan Grove Says:

    No more than 3 levels of nesting?

    Dunno ’bout the rest of you, but most of the howlers I’ve seen in spreadsheets come from simple referencing errors, INDEX 2nd and 3rd arguments 1 off, deleted rows/columns, ranges including hidden rows/columns, MATCH/LOOKUP/[VH]LOOKUP assuming sorted ranges which aren’t. Not sure any strict mode would make any of that less likely.

    My impression is people who want to do this only know how to select (colloquially, NOT in the Excel OM sense) particular cells by changing their format. They don’t know how to set up check boxes, and even then without VBA it’s tedious to tie checkbox state to cell ranges. So they’re stuck wanting to count/sum/etc by format. Seems to me what’s needed is a cell-within-range user selection object that’d save user selection state in a separate range, along with a new function that would return the user selection state range when passes a reference to a range within the source range. Then it’d just be =SUMPRODUCT(NEWFCN(foo)*foo). This’d also require a dialog – select the range, run a command that displays the dialog which asks where the top-left corner of the state range should be (and in a radical departure from way too much Excel ‘functionality’, it should allow the state range to be in a different worksheet) and how selected cells should be formatted differently from other cells within the source range.

    While I’m at it, it wouldn’t hurt to have another function that would return which conditional formatting rule a particular cell satisfied (aw shucks, it’d only work with discrete conditional formatting rules) and be able to return an array when passed a single area multiple cell array.

    And while I’m in a cranky mood, the single greatest boon to NEW Excel developers would be deprecating merged cells.

  4. Bob Phillips Says:

    While I’m at it, it wouldn’t hurt to have another function that would return which conditional formatting rule a particular cell satisfied (aw shucks, it’d only work with discrete conditional formatting rules) and be able to return an array when passed a single area multiple cell array.

    Get’s damn near impossible in 2007.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: