10

We are an ISV with experience developing and distributing desktop applications written in VB6. We are now developing tools as Excel spreadsheets containing VBA code. These will be downloadable free for various users including local government organisations.

We've rarely distributed spreadsheets outside our own organisation before. What best practises should we follow, and are there pitfalls we should watch out for?

I'm aware of the following.

Cœur
  • 37,241
  • 25
  • 195
  • 267
MarkJ
  • 30,070
  • 5
  • 68
  • 111
  • 1
    If you have a commandbar/menubar linked to your spreadsheet, you will have to change to support Excel 2007 ribbon UI – A9S6 Jan 11 '10 at 09:44
  • From Excel 2007 on, files containing VBA give an annoying warning message, and VBA isn't supported on Mac anymore. – Alexandre C. Jul 22 '10 at 12:44

3 Answers3

2

Two things that come to mind:

  • If for some reason you have a formula or text in a cell, try and refactor it if it is > 255 chars because Excel has no issues truncating it if the sheet is copied. It doesn't even notify you depending how it is done.
  • If you use named ranges, avoid three letter named ranges, or three letters followed by numbers. In Office 2007 BAD1 for example is a valid cell reference.
MarkJ
  • 30,070
  • 5
  • 68
  • 111
Codezy
  • 5,540
  • 7
  • 39
  • 48
2

Rather than developing a spreadsheet, I've found creating an Add Ins easier to distribute. Typically I'd have a single menu with options for documentation and to create any templates that are needed for your functions. By changing your spreadsheets more "app like" means more work but it has several advantages;

  1. It's easier to be consistant with your look and feel
  2. All of your code, your templates etc are in one file (ie: the Add In)
  3. You can easily reuse code (probably the most important bit)

for an example of how this may look, see this demonstation

Also another trick i always add is some version control and usage logging. Each major function would hit a webserver whenever run and log it's use. This isn't appopriate for every situation but in a enterprise environment it was very handy to know who was running what Excel mini-apps and how often.

Mark Nold
  • 5,638
  • 7
  • 31
  • 33
  • We were planning to put all code and templates into one file. The spreadsheets are going to be a free download to help with specific calculations - we need to wheedle the users into using it. I am a bit concerned that users might be more suspicious of an add-in than a spreadsheet and find it harder to get started. For this app, I think they need to be getting interesting results in a couple of minutes after the download, otherwise they might give up. +1 though, interesting advice – MarkJ Jan 11 '10 at 09:33
1

Thing I have found useful when creating spreadsheets for a wide range of non technical users. This applies in excel 2007 and earlier (not sure if it still is a problem in later versions) where the user can corrupt your formulas in other sheets even though everything is protected.

  1. Using named ranges that way they can be resized or moved easily when someone changes their mind.
  2. Using array formulas as when people cut and paste cells they will modify any formulas that depend on these cells, if you use arrays they have to cut the entire array to destroy your formula
  3. Make your array so that one row at the top and bottom, and one column on the left and right are protected and can't be changed by the user, this way they can't corrupt your formulas.

Have never worked out how to ensure the formatting remains uncorrupted.

tarriel
  • 25
  • 1
  • 7