Macro Location Control

[Andy Pilkington]

I have a number of spreadsheets with many macros, one in particular with nearly 400. Whilst the sheet and the macros work fine, when I edit macros I note that the later ones are "saved" in separate modules. My tidy mind says that it would be neater and easier to search and edit if they were all in the same place/module.

Please can someone explain the purpose of modules and if there is a process whereby I can consolidate all macros in one place?

Thanks in anticipation.
Andy

 

[mikerickson]

There are three kinds of modules:
- Class Modules are for creating custom objects. It doesn't sound that those are applicable for this question.
- Userform code modules. Userforms are essentially class modules mixed with a user interface, again not applicable.

- Object Modules, like ThisWorkbook or the code module for a sheet. Event code for that object must be in this kind of module. The code for ActiveX controls has to be in the module for the sheet where the control lives.

- Normal modules. It sounds like this is what you are working with. A macro can be in any normal module and still be called with normal syntax.
With 400 macros. I would put them in different modules for the reason you mentioned, organization. Looking through a "pile" of 400 macros for the one that I want to edit is hard. (Plus my general approach to computers that when things (sheets, modules, workbooks, audio files) get too big, odd and bad things crop up.)
I would put those macros in different modules divided either by purpose or by alphabet.
You can think of different modules like different shelves in physical library of books. You can either organize your library by topic or alphabetically.

 

[Andy Pilkington]

Hi Mike,

Useful to know this, thanks.

Andy