Best method of executing large macros?

[schulzy175]

I have a list of macros that is beginning to get pretty large. Some are split into multiple modules whereas others have multiple Subs in a single module. What would be the best method of consolidating large macros? Is it better to create multiple Subs in a single module or to create multiple modules? Thanks!

[6StringJazzer]

My opinion is that whatever is better for the way you think. Technically it doesn't matter. Just organize them in a way that makes the most sense to you.

I have many modules in my PERSONAL.XLSB file so it's easier to find things, and be able to export/import things more easily based on their functionality. I have one module that is for Outlook services to be used in Excel (send email, send meeting invitation, etc.), one for UDFs, and dozens more. I have other general purpose modules of VBA functionality, like regex pattern matching, that will work no matter what application I use them in.

[Keebellah]

If you have a large number of macros that need to be executed one after the other you might also need to check if the next macro must be be run only if the previous one completed successfully.
I use Functions that return a Boolean instead of Subs.
If the Function returns a True then the next one can be run etc etc.

This will avoid a macro to run with incorrect values and cause havoc.

Hope this helps you a little

[scottiex]

I have hundreds of macros and use the modules to group similar macros while trying not to raise the number of modules to a ridiculous level.

You can have some issues if you let an individual module get super large.

[Zer0Cool]

I agree with @6StringJazzer. Organize your macros however it makes sense for you. Come up with a consistent hierarchy that works for you.

Generally speaking, this is what I do:

I use my personal workbook (personal.xlsb) for code snippets that I use frequently. Not fully functional macros, mind you, but snippets that I can copy/paste into actual macros instead of needing to type it all over again. For example I have a "template" for modules and functions so when I go to create a new one I simply copy the snippet over into what I am working on instead of doing stuff like writing my general error handler every time or including screenupdating = false, etc. I also have things like a bubble sort snippet and a file dialog to open/save snippet so that I dont need to write them from scratch each time.

This makes code I need all the time generically always available to me without making my personal work book a ridiculous size.

Any code I need to use across multiple workbooks periodically or that has a general function not specific to any workbook I generally turn into an Excel Addin. I can activate/deactivate as needed. For example I have an addin to measure calculation times of formulas in various ways. I have this as an addin with its own tab on the ribbon and keep it activated as I use it regularly. I also made an addin that cleans up excess/corrupt styles, which I activate when I need it.

Any code specific to a workbook or a set of workbooks I keep the code in that file.

In this fashion each file/VBA project is separate and serves a well defined purpose. I dont have to guess what to open to get the code I need.

Within each file/project I aim to write my code as modular as possible without being overly complex. I use modules to separate code by its overall purpose within a project. So I may have (in larger projects) one module simply for all my declarations that need a scope beyond a single routine or function. Ill name it something like "allVars" so that its at the top of my modules list making it easy to go and update variables and constants. I may then have a module just for the functions used by the macro, so that troubleshooting functions doesnt become a game of hide and seek across modules and routines. I could then have a module for classes similar to how I have for functions.

I then may have a module with my general body of code or my main routine which is what gets called/ran to start. If the project is complex enough I may create other modules to better identify the workflows, for example a module has multiple subs that gets the user to pick a directory of files, stores the list of files in an array, does any type of checking on those files I need (like dates, names, file type, etc) and then another module with all the subs needed to process those files, maybe unzipping them, manipulating the underlying files, then repacking them into a zip. Lastly I may have another module with all the code for generating a report when the process is finished so the person who ran the macro knows what files were processed along with any other relevant data needed.

I start a new sub or function mostly when that code needs to be used multiple times, so I do not need to write it a bunch of times over and over. The other case for me splitting subs is when I want to be able to troubleshoot a small portion of my code or think it may be generic enough for me to later look back on and decide to copy it to my personal workbook to use as a snippet.

Basically, 1 enormous blob of code is bad as would using another module for each line of code. You need to find the happy middle ground that helps navigate, organize and troubleshoot your code.

[Keebellah]

I agree with @Zer0Cool that the personal.xlsb is a good option, but for your own use.
I write code for others and I cannot force the users to create or update their personal.xlsb files especially if the app will be used by multiple users.
My tactic is to put everything in a xlam (AddIn) file which loads with the workbook open event and unloads with the workbook close event.
I can include anything, including custom UI (ribbon) and more.
Avoid repetitive code build common macros of Functions with parameters to be used by one or more routines and yes organize (name) the modules accordingly so that you can find your macro.
Another advantage is that you can use this xlam file (if built correctly) for the user to use it with his/her non-macro embedded files.
It all comes down to clear and logical building-up of your code and VBA project lay-out

[Zer0Cool]

Here are examples of my "templates" for subs and functions that I keep in my personal.xlsb. I just copy/paste them into my working macro when I need to write another sub/function instead of manually writing it each time. I can then expand on each, filling in, changing or removing things from it to meet my needs.

Sub

Please Login or Register  to view this content.

Function

Please Login or Register  to view this content.

[scottiex]

my model is very similar to Keebellah's model with the xlam.
I "generalize" code as much as possible so that they can be used in the widest variety of situations and all options are easy to change.
Ie my live macros double as templates as much as possible (ie minimum amount of work to just use it if i wanted in another situation).