Excel VBA development best practises

Question

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.

• Digitally sign the spreadsheets with Authenticode. Any advice about that?
• I'm worried IT departments might impose security rules that will prevent the users downloading spreadsheets containing macros even if they are signed. Does that happen?
• Making sure code works on different versions of Excel (2000, 2002, 2003, 2007). Any advice?
• Password-protect (lock) the spreadsheets. Weak protection is better than nothing, and it's not a disaster if someone gets the source code anyway.
• Version control.
• ...maybe I should just read the definitive book?

Thanks in advance! Mark

Answer 1

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.

Answer 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.