MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Code - where does it go?

Posted by TBone on December 11, 2001 11:10 AM

Looks like I can put my Excel VBA code into either a module, ThisWorkbook or Sheet1 (sheet2, sheet3, etc).

Can someone help me understand the difference between putting code into those sources? Not sure why I would ever need to create a module. An explanation would be very much appreciated!

Posted by Tom Urtis on December 11, 2001 11:30 AM

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.

Posted by Ben H on December 11, 2001 11:30 AM

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.

Posted by Juan Pablo G. on December 11, 2001 11:31 AM

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.

Posted by Mark O'Brien on December 11, 2001 6:32 PM

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.