Category Archives: 11540

Too many Modules Spoil the VBA Project?

So many times you see someone asking about whether they should split their VBA code into many modules, if so, how should they structure it?

We probably know that there is a limit to the amount of code that a VBA code module can handle. The code module is essentially an in-file text file (if exported it can be viewed with a simple text eitor), and can accomodate a size of approx 64k bytes. As such, the answer to that previous question should always be an emphatic yes. But it also should be yes from a code maintainability persepctive, even if it never approaches the 64k limit. The code should be organised into functionally discrete modules, for instance a module that contains all of the file handling procedures, another that contains all of the graphing procedures and so on.

For my projects, I tend to have a global module, which contains all of my global variables (although I try to keep these to a minimum), and all of my application constants, such as messages and enumerated lists. This way, it is simple to track the source of these variables, rather than scattering them amongst many modules.

I would normally have an application class, specific to the particular application, which would hold any procedures common to that application, such as a common validation procedure, and application variables, such as the AppId, AppName, AppVersion and so on. It could be argued, and I have gone this route in some projects myself, that many of those global variables are application specific and should be in the application class. I think I subscribe to this in theory, but don’t always practise it.

Of course, I have a common error handling procedure, a utilities procedure (which would hold my generic procedures such as SheetExists), and a history module, which is pure comments.

If I need to trap application events, I would have such a class. I tend to hand off the actual processing of these events to a helper module, the class module simply traps the event, decides whether it applies to this application in these circumstances, and then calls the procedure in another module. Theoretically, I am not happy with this from a design perspective, but I do find it makes the code a lot more manageable, and convince myself it is good from a maintainability perspective.

I also always have a scratch code module, where I have procedures to easily setup data, reset things, test specific aspects of the application, and so on.

Finally, I apply a strict naming convention, and add two underscores to a few modules to force them to the top of the list, and a single underscore to some others to forec them into the next batch. I find this categorisation also helps with maintenance.

Here is an example of one of my projects.



As you can see, it is a good size with over 8,000 lines of code, the 363 procedures are nicely scattered over the modules, no module having more than 30 procedures except AppClass (many of which would be property Let/Gets). Apart from AppClass, no module has more than 700 lines of code, and each procedure is circa 30 lines line. Of these code lines, nearly 1,500 are comment lines (circa 15%). I think this is a high comment rate for me, not typical, as I do not over-comment my code.  In fact, without the history module, the comments reduce to 12%. 10-12% I would think is my typical comments percentage.