Excel code

One of the great things about Excel so far has been the ability to embed VBA with the s/s part. With VSTO we have the option of server based code which has some clear advantages around security, IP protection and so on.

I love the single file solution aspect of VBA, and the ease of deployment and distribution that come with that. These I think fit well with my idea of the spreadsheet world. Quick and dirty I guess is not a bad approximation.

VSTO gives us the option of keeping the code on a server somewhere at a cost of some deployment pain. I believe that pain is being rapidly reduced as simple deployment is a key goal for VSTO 2008. This means the IP in the code is protected. And it means, if key parts of the s/s rely on code they will not work outside the organisation, again protecting IP. As the code is ‘compiled’, unauthorised tinkering is reduced/prevented improving security and information security.

If you had to choose between one or the other (and maybe we already are choosing!) which would you go for? (in fairness I think VSTO 2008 and its ease of deployment will be quite different to current versions)

Do you think both options are useful or do you think one is unacceptable/pointless?

Is there another approach that you think would work better?




5 Responses to “Excel code”

  1. dermot Says:

    It sounds to me like you are talking about fairly large “systems” rather than spreadsheets, in which case I’d see the spreadsheet as the UI rather than the app, because s/sheets are too open to make good systems on their own. However, for small apps, nothing beats the simplicity and versatility of a single file solution. So I think it depends…..

    I’ve had a few (smallish) systems where there are multiple s/sheets and the problem is how to maintain them. One obvious solution is a single master s/sheet which loads in the data it needs from a db or other source, ie the s/sheet is the UI. I have found it useful to allow the user to save results in separate output s/sheets, which contain enough info that they can be re-used as inputs later, ie you can reload/recreate/modify previous work.

    Another solution is to have a set of s/sheets which “call home” when they start up, loading a single centralised s/sheet (or other program) which has all the code, and which can modify the satellite s/sheets as required. I prefer the first option, if possible.

  2. Excel code | MS Office Security Watch Says:

    […] the rest here: Excel code code development excel excel code key professional quality risk smurf smurf on spreadsheets […]

  3. Charles Says:

    I think of it as a spectrum of solutions:
    The single file distribution system works well when the solution is a hybrid Excel workbook formulae plus VBA thing.
    Its not so good when you are distributing some more generalised code, or when there is a need for asynchronous updates of the VBA code and the workbooks, but then you can get quite a long way with an XLA on a server driven by a client stub loader, with accompanying templates, DLLs and DB as required.
    As VSTO improves it will presumably increasingly take over the high end of this solution space. How far it goes depends on how much it improves!
    And Excel Services is an alternative approach that will dominate in some circumstances.

  4. Dennis Wallentin Says:

    Compared with previously versions the VSTO team have made an excellent work with the coming version (version 3.0 aka ‘Orca’). But it will only work with Office 2007 and later.

    Kind regards,

  5. Jon Peltier Says:

    Despite the ease of embedding code into the workbook files, I tend to avoid this whenever possible. The code goes in an add-in, and as little code goes into the regular workbooks and templates involved in a project. This makes updating parts of the project easy, without hosing any important data.

    These are not large applications for the most part. The UI is workbook-based, as is the logic, but it’s in separate workbooks.

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 )

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: