A friend of mine recently asked me:
“What is the best way to organize the Modules in my Excel workbook?”
That is to say, what is the best-practice for organizing VBA code?
I’ve searched the web to find a cohesive list of best practices, but I didn’t really find anything more than a few tidbits. Most of the advice out there simply tells you to organize your modules in a way that makes sense for anyone reading the code.
Ok…I got that. Most of us intuitively organize and partition our modules into loosely related procedures. For example, one of our modules may store all procedures pertaining to PivotTable automation, while another module may store all User Defined Functions.
Outside of that concept, I’ll attempt to relay some of the basic ground-rules I’ve always heard (and adhered to).
For me, organizing modules is not just about making it easier for the next poor soul to take over my bloated application. I typically make an attempt (feeble as it may be) to take into account the impact on memory and performance my large multi-procedural modules may have.
Excel (by default) loads code modules only as they’re needed/referenced. This means that when any procedure or variable is referenced in your code the entire module that contains that procedure or variable is loaded into memory.
This is one of the reasons why you’ll see workbooks where, for instance, all global variables are placed in the same module. You don’t want several modules holding global variables because each time a new global variable is referenced, Excel has to load another module into memory. It’s a far better practice to place all global variables into one module so Excel only has to load that one module into memory.
Another thing to remember is that the call tree for a particular procedure will ultimately include any additional procedure it references, plus any subsequent procedures that newly called procedure references. In fact, if your procedures reference numerous nested procedures stored in different modules, you could potentially be loading all of your modules into memory.
Confused? Here’s a simple example.
Let’s say you call a procedure found in module X. Module X is loaded. If that procedure includes code that references procedures in modules Y and Z, those modules will also be loaded. If those subsequent procedures reference procedures in other modules, those will also be loaded; and so on.
In that light, another best-practice is to place frequently used procedures in the same module as your commonly used procedures to prevent a different module from being loaded and compiled.
For example, you may have a module containing three procedures: UpdateCustomerAddress, UpdatedCustomerContacts, and UpdatedCustomerSales. Given that updates to Customer Address and Customer Contacts happen infrequently, but updates to Customer Sales happen all the time, it’s a better practice to place the procedure that updates Customer Sales in a module containing other frequently used procedures. This way, there is no need to load another module into memory. Excel will only need to load a separate module if Customer Address or Contacts is updated.
This may very well go against your instinct to organize your modules into “topics”, but it can help improve performance for larger complex Excel applications.
On last note:
Excel has an Option called Compile on Demand (in the VBE Menu – Tools->Options-General Tab). My understanding of the Compile on Demand option is that it tells Excel to load only the portion of the call tree required by the executed procedure. For example, if you call procedure in module X, any modules that contain procedures referenced in procedure X are loaded and compiled. However, Excel won’t load modules referenced in other procedures that may be called from in module X.
In other words, The Compile on Demand option tells Excel to load modules one level deep from the executed procedure’s immediate call tree; not the module’s call tree. This effectively makes execution of code faster.
You can uncheck this option to turn off the Compile on Demand. This will tell Excel to load and compile all modules at one time. I frankly don’t know why you would want to do that, but the option is there for you.
All that being said, it is quite possible I have my internal workings of VBA wrong somehow. Feel free to comment and correct me on any misstatements.