What is a VBA Module and How is a VBA Module Used?

What is a VBA Module and How is a VBA Module Used?
Play Video
Close

Presented below is a brief introduction to a VBA module and their uses.

A VBA module is where you type your computer code. The code you type in a module when run can perform tasks in Excel like format worksheet data, process data, create charts, create PivotTables, perform calculations, command databases...etc.

Product Questions? 1.805.498.7162   Customers >

Excel VBA Help Topics:

What is a VBA module.

VBA code is typed and viewed in the VBA Editor in what are called modules. A collection of modules is what is called a VBA project. In the VBA Editor, a VBA module when viewed resembles and behaves like a Word document in both basic organization and typing. When viewed, a VBA module will appear in its own window within the VBA Editor. Think of modules as organizational units for your code, you add VBA modules as needed to a project to organize and run code.

VBA modules come in three different flavors: Standard modules, Object modules, and Class modules. A Standard module is where you will be typing most of your code when starting off in Excel VBA. Think of it as the town square, everybody can easily get to you and talk to you. You can assign shape buttons to procedures in these modules to easily run them. An Object module belongs to an Excel element like a workbook, chart, worksheet or a VBA element like a userform. You create events in them which are just Sub procedures that run when something occurs like pressing enter in a cell, opening a workbook, or clicking on a worksheet tab. These procedures can be used in place of clicking buttons to run code which is really cool but they require a lot of logic and programming know how to bring them under control. They tend to run when you do not want them to run.  A Class module is used to create a class for an object. Classes are what you will be using to command Excel, Word, PowerPoint..., but these are already made, you will just learn to run them. Object oriented programming involves advanced programming concepts which are better left to investigate till after you learn the programming fundamentals. All of the modules just discussed appear in the Project Explorer window as icons. They are organized as follows: Standard modules are found under the Modules folder, Object modules are found under the Excel Objects folder, and Class modules are found under the Class Modules folder. One note here, they all do look alike so you have to look at the name of the module and then locate it in the Project Explorer tree to tell what type it is.

< < < Scroll Picture > > >
In the Excel VBA example above, the three module types can be seen in the Editor, they are: Standard, Class and Object. When typing your VBA code, you make use of the Object and Standard modules the most to create your program. The most common one to use is the Standard module. If you want to build a Class module for a complex object,  it is better to use VB.Net to build it and call it from VBA.
Excel VBA Advert

What is in a VBA module.

As stated eariler, modules are made up of elemental building blocks called procedures. Procedures are used to organize and run your code in a module. Think of a paragraph in a Word document and you are on the right track. You type Excel commands, variables, arrays, loops, logic, functions,... in your VBA module procedures and then run the procedure to control Microsoft Excel features and perform various tasks.

< < < Scroll Picture > > >
In the Excel VBA example above, a simple Sub procedure has been written in a Standard module that sorts a worksheet table on the active worksheet when run. The procedure is defined by the Sub and End Sub lines in the code.
Excel VBA Advert

How to view and control a VBA module.

To view a module, just double click on its icon in the Project Explorer window in the VBA Editor. Standard modules are located under the Modules folder, Object modules are located under the Microsoft Excel Objects folder, and Class modules are located under the Classes folder. Note that modules located under the Modules folder or Classes folder can be removed by right mouse clicking on their icons in the Project Explorer window and selecting Remove. They can also be added via the Insert menu. Object modules can only be deleted by deleting the worksheet or chart sheet they are assoicated with.

Excel VBA Double Click Module
< < < Scroll Picture > > >
In the Excel VBA example above, the Module1 standard module can be viewed by double clicking on its icon in the Project Explorer window. It will be displayed in a window on the right-hand side of the Editor. The module is located under the Modules folder in the window.
04/06
< Back
Next >
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2021

EMAGENIT All Rights Reserved