Put Excel-VBA code in module or sheet? [closed]

Question

What is good practice and good code hygiene? Putting code in Modules or Sheets?

I have this Excel Workbook, with user interfaces in each sheet. Each sheet within the workbook does a different part of some overall task. Should I place the code relevant to each sheet inside the Sheet objects, or in Modules? Group into one module, or separate modules?

I'm using Excel 2003.

Answer 1

Definitely in Modules.

• Sheets can be deleted, copied and moved with surprising results.
• You can't call code in sheet "code-behind" from other modules without fully qualifying the reference. This will lead to coupling of the sheet and the code in other modules/sheets.
• Modules can be exported and imported into other workbooks, and put under version control
• Code in split logically into modules (data access, utilities, spreadsheet formatting etc.) can be reused as units, and are easier to manage if your macros get large.

Since the tooling is so poor in primitive systems such as Excel VBA, best practices, obsessive code hygiene and religious following of conventions are important, especially if you're trying to do anything remotely complex with it.

This article explains the intended usages of different types of code containers. It doesn't qualify why these distinctions should be made, but I believe most developers trying to develop serious applications on the Excel platform follow them.

There's also a list of VBA coding conventions I've found helpful, although they're not directly related to Excel VBA. Please ignore the crazy naming conventions they have on that site, it's all crazy hungarian.

Answer 2

In my experience it's best to put as much code as you can into well-named modules, and only put as much code as you need to into the actual worksheet objects.

Example: Any code that uses worksheet events like Worksheet_SelectionChange or Worksheet_Calculate.