Excel VBA development best practices [closed]

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?

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.

Answer 3

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.