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

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. If you encounter a _ (space underscore) in the code, that is a line continuation for the code to continue on the next line down and can be ignored. Just type that code on one line. Last layout the data on a worksheet (if needed) as seen in the illustrations. Place the code module in the same workbook as the data and make the data worksheet active before running.

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 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.

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 so be careful. 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 fundamentals of programming. 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.

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 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 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 assoicated with and they are automatically added when you add a worksheet or chart sheet.

< < < 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. 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 directly. 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. Black code indicates that you have typed a statement in your code correctly as far as VBA grammar rules are concerned. That however does not mean the code will run correctly, just that you made no syntax errors. Red indicates that you have made a compilation/syntax error (i.e. grammar) in your code and the code will not run until it is corrected. Blue code indicates a reserved keyword has been typed. Keywords cannot be used to name anything in your code remember that. Green code is a comment which is basically a note to the person that is reading your code. These lines start with an apostrophe or the word Rem. 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 editing.

When typing your computer code, most of your code is typed within a procedure's boundaries. The 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 and you will not bump up against it when first starting out.

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

A common misperception 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 them in a project workbook that a user clicks buttons in to run their procedures. VBA code can search through Excel for the workbooks it needs or open them as needed, command them, then make new workbooks to store reports, charts, tables...etc. 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 thus making version control impossible. A note here, when creating models in workbooks, you would have one model workbook that could be upgraded as needed and issued and you would upload and download the model parameters to the model using VBA storing them separately in workbooks and text files. This strategy is great for batch processing and trade studies.

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 work intermittently. While using the Macro Recorder is a very good research tool for figuring out specific Excel commands and their syntax, it writes horrible code. 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 because you open another workbook later in the code. Also making Excel's elements active with VBA code so you can command them like with the way the Macro Recorder records is ultimately self defeating because it requires a lot of code, makes the code slow, is error ridden, and darn near impossible to track multiple Excel elements at once.