What is a VBA module and how is a VBA module used? - VBA Tutorial

This page reviews the different VBA module types and how to view, control, and type in them.

Want to learn more? Excel VBA classes for business & industry > and engineering & science >.

Excel VBA Tutorial

Key page concepts.

Please Note: The code presented on this page is fully operational and can be easily run. To run it, you will need to read 3 other key pages in our Excel VBA help, What is the VBA Editor > , What is a Procedure > , and How to Run a Sub Procedure >. You will type the code in a Standard module (explained below) and can ignore the green text which are comments.

What is a VBA module?

VBA code > is typed and viewed in the VBA Editor in what are called modules. A collection of modules along with other key elements is what is called a VBA project >.

In the VBA Editor >, a VBA module resembles and behaves like a Word document when 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 store your code. They come in three different types in VBA: Standard, Object, 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 and pictures 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 (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, 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 too so be careful. 

A Class module is used to create a class for an object. Classes contain the code you use to command Excel, Word, PowerPoint..., but these are already made, you will just learn to run them. Object oriented programming involves advanced programming concepts so learn the fundamentals of programming first before you attempt to tackle them.

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.

Excel VBA Modules

Scroll < > picture if hidden.

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.

What is contained in a VBA module?

As stated earlier, 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  procedures and then run the procedure to control Microsoft Excel features and perform various tasks.

Excel VBA Delete Column

Scroll < > picture if hidden.

In the Excel VBA example above, a simple Sub procedure has been written in a Standard module that copies a worksheet table to a new workbook then sorts the table when run. The procedure is defined by the Sub and End Sub lines in the code. To learn how to type and run a procedure click here >.

How to view, insert, and remove 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. These folders can be viewed in the first image above.

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. These two module types can also be added by proceeding to the Editor menu and selecting Insert / Module or Insert / Class Module.

Object modules can only be deleted by deleting the worksheet or chart sheet they are associated with. They are automatically added when you add a worksheet or chart sheet.

Excel VBA Double Click Module

Scroll < > picture if hidden.

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. To learn how to type and run a procedure click here >.

How to type in a VBA module.

To type in a module, click in its window with your mouse and start typing VBA code. VBA does not start in any particular column or row and leaving blank lines in your code does not effect it.

You can use the Tab key to indent your code, you do this to make your code easier to read and interpret. To remove an indent in your code, just highlight the code and press Shift+Tab.

While typing in the module, you will notice 4 distinct text colors appear.

When typing Excel and VBA commands (i.e Functions, Properties, Methods...), a good test to see if you have misspelled what you are typing is to type everything in lower case then click off the line, if everything caps on the first letter, it is a least spelled correctly. This however does not indicate if it will run correctly.

A common mistake also encountered when correcting code is that you want to press Enter when done. Enter only inserts a new line of code in the module and will split your statement (line of code) in two causing a compilation / syntax error. Just click off the line when you are done typing or go to the end of the line and press enter.

When typing your computer code, most of your code is typed within a procedure's boundaries (Sub--->End Sub or Function--->End Function). Exceptions include Option statements, Declare statements, and Public / Private / Const / Type statements. These statements are typed at the very top of a module in the declaration section before any procedures are typed and only if they are needed.

Sub, Function, and Property procedures may all be typed in the same module, just make sure to name them uniquely. A procedure itself is limited to 64K in size, which means how much text is typed within its boundaries. It is a very large amount of code (~1000+ lines) and you will normally not bump up against it.

Excel VBA Color Data

Scroll < > picture if hidden.

In the Excel VBA example above, Excel VBA is being used to color column C on the worksheet if the value is above 800. The VBA code is pictured in the VBA Editor on the right-hand side. Excel commands like Cells, Columns, Color... can be seen in the code. Notice the different code colors, these are the colors discussed above. Also note that nrows must be placed after To with a space in order for the code to run correctly. To learn how to type and run a procedure click here >.

Does it matter what workbook your VBA module is stored in?

A common mistake is that VBA code must be stored in the same workbook as it is commanding. Actually your modules containing your VBA code can be stored in any desired workbook. The author generally stores his in a project workbook that a user clicks buttons in to run their procedures. Think of a smart phone type layout.

VBA code can search through Excel workbooks commanding what you tell it. You can lock onto workbooks using a variety of coding techniques, knowing the workbook name is not required. Keeping code in a single project workbook that is not attached to your data, charts, reports, and so forth prevents you from making 500 copies of your VBA code. This allows you to maintain version control for code upgrades.

A note here, when using VBA to create models in workbooks, it is recommended that you have only one model workbook. The idea is to build upload / download VBA code that controls model parameters and data.  That way you can upgrade the model workbook and not have 100's of copies floating around. You can easily store model information in other workbooks or even text files. This strategy is great for batch processing and trade studies also.

How VBA commands Excel is through object expressions (i.e. Command().Command().Command() ) commonly referred to as "paths". If you do not assume things are active in your VBA code and create the proper object expressions to track the Excel elements your are commanding, the code may be housed anywhere as discussed above.

Relying on things being active is what causes most Excel VBA code to run slow and intermittently fail. While using the Macro Recorder is a very good research tool for figuring out specific Excel commands and their syntax, it writes horrible procedures. Learn to track objects in your code using object expressions that do not rely on anything being active and your code will be very robust in execution.

The Set statements you see in the code above are used to lock onto an active sheet initially, but then the variable ws_data is used in the object expressions commanding Excel after that point. You are tracking the sheet and that code will never fail because the sheet becomes inactive.

Excel VBA Color Data

Scroll < > picture if hidden.

In the Excel VBA example above, Excel VBA is being used to color column C on a worksheet in a workbook it just opened if the value is above 800 (look for Workbooks.Open() in the code). What matters in your code is tracking the paths of objects, not relying on what is active or everything being in the same workbook which is self defeating. To learn how to run a procedure click here >.

Additional Tutorial Topics:

04/06
< Back
Next >
Need Help? Please call us at 1.805.498.7162

Copyright © 2002-2023

EMAGENIT All Rights Reserved