If you've started using Excel VBA, perhaps using the Macro recorder, or writing code directly, you'll have discovered that VBA code is contained in modules within the workbook. This article describes the four different kinds of modules, general purpose, workbook, worksheet and userform, and then describes how VBA code can be copied from one file to another by copying modules.

General Purpose Modules

Suppose you create and saving a macro in Excel using the macro recorder. To see the VBA code created, you can choose the Excel option to edit the macro and the VBA interface opens showing you the code created by the recorder. Alternatively you can open the VBA editor by using ALT + F11.

The code is contained in a module shown on the right of the VBA display. If you look at the left hand Project Explorer view your see the current workbook listed along with all the currently available worksheets. Under the worksheet you'll see the VBA module under the workbook file details under a Module heading. VBA created using the macro recorder appears in a general purpose module. If you exit the VBA screen and return to the normal Excel view, you might then create more macros. If you do this the additional macros are placed in the same general purpose module with a line separator.

If you save and close the workbook, then re-open it and create another macro, the VBA code is created in a new general purpose module, so over time if you create macros in different Excel sessions, your code is stored in several general purpose modules, all accessible via the VBA interface. If you wish to, you can copy code between workbooks, or create new code within a workbook. You can also create a new general purpose module in the VBA editor by choosing New, Module.

Workbook Code Modules

If you create code which triggers following a workbook event, such as a workbook opening, or closing, the code is placed in the workbook code module. There is only one module of this type per workbook.

With a workbook open you can view the workbook module this way. Choose ALT+F11 to open the VBA editor. Then look in the Project Explorer on the left, and you'll see ThisWorkbook in the list of items under the filename. Double click ThisWorkbook to open the module on the right. If you want to see the code options available, choose the pop down just right of General and select Workbook. Then in the right hand pop down you'll see all the workbook events and corresponding code options available. Select any event and the corresponding code is added to the module.

Worksheet Code Modules

Similarly there is only one worksheet code module per worksheet. To view one, in the Project Explorer double click on any of the sheet items and the module will open on the right. In the left hand pop down choose Worksheet. Now choose the right hand pop down and you'll see all the events available. Choose any event and the corresponding code is added to the module.

User Form modules

In the VBA editor you can create one or more user forms and then add form controls, for example for data entry. A separate userform module is created for each form you create. Once a form control is added to the form, the associated code is added to the module. To create a userform in the VBA editor you choose Insert, UserForm.

Copying VBA code between workbooks

If you open more than one Excel workbook, and then open the VBA editor using, say, ALT+F11 then you'll see all the open files listed in the Project explorer. If you want to copy VBA code, including macros, from one file to another, navigate through the Project Explorer view until you can see the module where the code is.

You may need to double click the module to open it and view the code. Then in Project Explorer drag the module where the code is onto the filename where you want to copy the code to and then release the mouse. The module is copied to the target file, and will appear under the correct module heading under the filename. Dragging a module in this way will copy the module, so the original is not moved. So you can easily copy code for macros, hand created code or forms between workbooks.

Interested in learning more about Excel VBA? A really effective way is to attend a training course and then really see your VBA skills increase to new levels.