Best Practices for Organizing VBA Modules

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.

5 thoughts on “Best Practices for Organizing VBA Modules

  1. Charlie Hall

    Interesting approach that certainly has some justification. However, I would put finding code ahead of performance except when performance (and code loading) is known to make a significant difference (which for almost all of my code loading performance is not as issue)

    The VBAIDE is so unhelpful in terms of finding specific subroutines, that organizing by topic at least helps narrow down to the module quickly and then tools like code explorer show which subs are inside the module.

    In terms of all global variables in one module, I used to do that but now I only put the global variables that are used widely – if there is a global variable that is tightly tied to a topic, then I put it in the topic (and since the topic will be loaded anyway if the global variable is being used, performance should not be an issue).

    And finally a very long time ago I decided to not use compile on demand but I may reconsider – I like to know when there are compile errors and compile on demand hides them until the module is compiled which could be a long time for some subs. However, once I have fully tested the code, then I agree compile on demand would be better for my clients, since they will not be changing the code, just using it.

    Thanks for the insight.

    –Charlie

  2. rubberduckvba

    The article seems to be (intentionally?) mixing up Excel (the host application) and the VBA runtime – Excel isn’t loading, compiling or executing anything, that’s VBA’s job.

    Anyway I just wanted to chip in with an *actual* solution for organizing VBA code:

    https://rubberduckvba.wordpress.com/2017/03/05/annotations-the-underducks/

    Folders. The VBE’s Project Explorer forces all modules to be regrouped by type (document/worksheet code-behind, standard/procedural modules, class modules, user forms), regardless of their purpose. With Rubberduck’s Code Explorer you get to organize modules into actual folders by functionality instead.

    All you need to do is put a little comment at the top of each module, for example:

    ‘@Folder(“Foo.Bar”)

    And Rubberduck’s Code Explorer will put that module under folder “Bar”, itself under folder “Foo”, and the tree nodes drill down to individual procedures and even shows member signatures, private fields, enums, types and their members. Navigating VBA code or finding references to anything becomes a totally different story with Rubberduck.

  3. dan l

    Interesting. I’ll pick at that rubber duck thing. FWIW, I recently purchased a copy of MZTools and it’s search and explorer features have saved me a lot of time in *not* having to spend a ton of time organizing my code.

  4. rubberduckvba

    @dan try Ctrl+T on a parsed project. Type any identifier (with autocomplete – includes locals, parameters, modules, procedures, declares,…even line labels) and hit enter to navigate there. If it has a name, Rubberduck makes it trivial to navigate to it. Also place caret on a MsgBox call; status bar tells you how many times it’s referenced. Click that button and you can navigate to every single call site.

    And if you’re into more advanced OOP stuff and implement interfaces, you can use the “find all implementations” command to navigate to all implementations of a given interface member. That’s downright Visual Studio + ReSharper stuff, right there in the VBE.

Leave a Reply

Your email address will not be published. Required fields are marked *