Excel Development Best Practices

This document describes the best practices in Excel development.

This document is intended for the bSup development team. If you are interested in Excel services, please contact our Excel Experts.

Please follow the below guidelines while developing VBA code.

• Functions
  • Keep functions shorts
  • Put comments for functions explaining their
    • Purposes
    • Inputs
    • And return values
  • Group functions which perform a specific set of actions, with a prefix.
• Constants
  • Create a module to hold constants
  • Define constants for any name which is repeated in the code for example
    • Sheet names
    • Range names
    • Row and column names
• Commenting
  • A very important practice is to insert comments throughout the code. Explain each block of code with commenting
• Name convection
  • Use a solid name convention in your coding for example,
    • Contents are all in capital
    • Variable names always begin with a lowercase letter
    • Function names begin with an uppercase letter
• Declare the data type of variables. I.e.
  • Use Dim for each variable
  • Use Option Explicit
• Modules
  • The module names begin with “mdl_” prefix
  • Properly name your modules
• Forms
  • The Userform names begin with “frm” prefix
  • The name of form elements begin with a proper prefix. For example, textbox names starts with “txt” and button names start with “btn”.
• Unit Testing
  • Develop unit tests to ensure that functions are working as they’re expected

• Excel Automation Services
• Excel Consulting Solutions
• Related Google Docs (access is required)