98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article VBA articles
What Are Modules In Excel VBA Coding?
Thu 26th May 2011
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.
Author is a freelance copywriter. For more information on vba training london, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1711-what-are-modules-in-excel-vba-coding.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsRED Engineering
ICT And Business Services Assistant Richard Tovell Excel Advanced Lara is warm, welcoming and interesting. I wouldn't suggest anything needs changing. Making Excel interesting is a skill in itself! Papworth Hospital Charity
Database And New Media Manager James Clayton Excel Advanced Excellently balanced course, outstanding coverage of different functions. Wasabi Co Ltd
Quality And Brand Standards Advisor Anita Bodis Excel Advanced It was nothing to do with the service, it just felt like a few of us in the training had a higher knowledge and would require a higher level course than the rest. Otherwise it was very interesting, wish I could learn more ideas to use for infographic designs (more various ways of charts to display a high amount of boring data). Thanks a lot for anything tho, this was fun! |
PUBLICATION GUIDELINES