Put Excel-VBA code in module or sheet

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

User · Answer

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

User · Answer

I would suggest separating your code based on the functionality and purpose specific to each sheet or module   In this manner  you would only put code relative to a sheet s UI inside the sheet s module and only put code related to modules in respective modules   Also  use separate modules to encapsulate code that is shared or reused among several different sheets   For example  let s say you multiple sheets that are responsible for displaying data from a database in a special way   What kinds of functionality do we have in this situation   We have functionality related to each specific sheet  tasks related to getting data from the database  and tasks related to populating a sheet with data   In this case  I might start with a module for the data access  a module for populating a sheet with data  and within each sheet I d have code for accessing code in those modules   It might be laid out like this   Module  DataAccess   Function GetData strTableName As String  strCondition1 As String  As Recordset      Code Related to getting data from the database  End Function   Module  PopulateSheet   Sub PopulateASheet wsSheet As Worksheet  rs As Recordset       Code to populate a worksheet   End Function   Sheet  Sheet1 Code   Sub GetDataAndPopulate        Sample Code       Dim rs As New Recordset      Dim ws As Worksheet      Dim strParam As String      Set ws   ActiveSheet      strParam   ws Range  A1   Value       Set rs   GetData  Orders  strParam        PopulateASheet ws  rs End Sub  Sub Button1 Click       Call GetDataAndPopulate End Sub

User · Answer

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

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

Examples related to vba

Examples related to excel