VBA Code - where does it go?

A good place to start would be the Help menu in the Visual Basic Editor, using keywords such as "Event" "Macro" and "Module", where you will find some useful information about this topic.

Tom U.

Typically, code is put on a sheet or workbook when the programmer wants to trigger the code using events from that object. For example, you could write a Workbook_Open subroutine which would run everytime the workbook is opened. Or, if you have a button on Sheet1 called CommandButton1, you could write a CommandButton1_Click macro which would execute everytime someone clicks the button. You could not put these event-driven macros on a module.

Non event-driven macros are typically put on modules. The main advantage of using modules is that the code can be called from anywhere else...other modules in the same workbook, other modules on other workbooks, etc. And code on modules can change data or affect objects anywhere in the workbook instead of being limited to the scope of the object where they are placed.

This is my understanding at least. Hope it helps.

Well, it basically depends on what you're trying to do. I've learned, from experience, that you should put ONLY in the Workbook and Worksheet modules the event's related, but, that's not a constraint, in fact, you can work with a macro putting it anywhere... it's just to be organized.

Juan Pablo G.

I tend to use Modules for code. If I want to trigger code with an event e.g. Selection_Change, I just call subroutine that sits in a module. Putting code in modules no matter what triggers the code, makes it easier to put related functions and subroutines in the same module. e.g. I have some workbooks that parse lots of text files and I put all the string manipulation functions in the same module. This keeps things tidy and makes it easy to reuse code in other projects.

There are additional advantages to putting related code in the same module, including memory management. If you put a lot of code on a worksheet or workbook, the code is instantiated even if it's used. (i.e. takes up memory). In the case of a module, if no functions or subroutines are used in that module then the module is not instantiated. (I've been forced to supply VBA projects to some crappy machines and this really can help things out.)

Only calling subroutines on Worksheets and Workbooks also makes the code a little cleaner (i.e. more modular/generic) and easier to understand, I've found.