98.8% 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:
TestimonialsEdmonton County School
Anna Joannou Excel Intermediate Thank you for the training we received. I have had such positive feedback from those who attended - they all found the course very helpful. We all felt as a school that both yourself and the other trainer covered all areas of Excel that we needed. We felt the course was delivered in a clear and concise manner and nothing was too much trouble. You were both able to assist and cater to all delegates, as you know not everybody works at the same pace but you both dealt with your groups accommodating all. I would have no hesitation in recommending yourselves and the company to other schools or organisations. Thank you once again. Smurfit Kappa Townsend Hook
Infrastructure Engineer Andy Brotherton Implementing Reporting Services Stephen has helped me to understand writing queries from scratch. Entering this course I had no understanding about the different commands and what they meant. Now I feel confident enough to try and write a query and report for our existing data. Zodiac Maritime
Analyst - Treasury/Finance Tom Jackson Excel VBA Introduction Thanks to Marius for today's course. He was very knowledgeable and explained the different processes and functions in detail and with great enthusiasm. I hope to be back soon to continue learning more about VBA as it appears to be an incredibly powerful tool! Projector was a little difficult to see at times, but Marius informed us that this is being replaced shortly at the Bloomberg location. |
PUBLICATION GUIDELINES