Where To Put The VBA Code

This page describes what to do with and where to place the VBA code examples on this site.

Introduction

If you are reading this page, you are probably looking for what to do with code that you have read on this site, other web sites, or peer-to-peer help web sites and newsgroups. This page describes the various types of code entities you may encounter and how to use them. Broadly speaking, code will fall into one of three types: Modules, Procedures, and Snippets.

Modules

A module is a container for code. All code must reside in a module in order to be executed. You can think of a module as a file (and, indeed, you can export a module to a text file) even though it is really stored within the workbook file. A module can contain any reasonable number of procedures. For small projects, you may put all the code in a single module. For larger, more complicated projects, you may want to organize the code into several modules to promote reuse and organization. At run time, when the code is executed, it does not matter in which module a procedure resides. Code from one module can call code in any other module within the same project (as long as the procedure is not scoped as Private). The code that you can download from this web site is provided in modules. If you have a module file (whose filename ends with one of the extensions bas (for code modules), cls for class modules or frm for user forms), you can incorporate that module within your project by choosing the Import item from the File menu in the VBA editor.

A module contains three sections. The first is the Compiler Directives section, at the very top of the module, which contains directives such as Option Explicit and Option Compare Text. While it is good programming practice to use these directives, they are not required. I do, though, strongly recommend that you use them. The second section is called the Declarations section, where module-level or project-level scoped variables are declared. These variables can be accessed by any procedure in the module (or, if declared Public, any procedure in the project and by other projects) and they retain their values even after a procedure or group of procedures has been executed and modified their values. A module need not have any variables declared at the module level. The Declarations section also may contain Declare statements. These are function definitions that typically (but not necessarily) point to functions in the various DLL files that make up the Windows operating system. If you encounter code that has Declare statements, these statements must be before any Sub, Function or Property procedures. The third section is the actual code contained within the module. The code section contains all of the code that does the real work. Note that these sections aren't formally declared as separate sections. The sections exist only by definition of what appears where in the module.

Procedures

A procedure is a block of code that can be executed as a unit. All code must belong to one or another procedure. Code can't exist outside of a procedure. While a single module can contain any reasonable number (<= 1000) of individual procedures, it is often a good idea to apportion the procedures into several modules, grouped by related functionality. This makes the organization of the project better and encourages the reuse of code. There are two types of procedures you can use. (Actually, there are three types, but we will not discuss Property procedures here.) A Sub procedure can run by itself or can be called from another procedure. A Sub procedure does not return a value to the code that called it. It simply executes and then returns control back to the procedure which called it or, if it is not called by another procedure, terminates code execution. A Function procedure is similar to a Sub procedure except that a Function can return a value back to the code that calls it. Functions typically take one or more parameters as input and return a single value back to the caller, whose value depends on the values of the input parameters. In the code below, the Sub procedure AAA calls the function procedure CircleArea, passing it the Radius of a circle. The Function CircleArea accepts this input, does a simple calculation, and returns the result back to the caller procedure.

Sub AAA()
    Dim Area As Double
    Dim Radius As Double
    Radius = 1
    Area = CircleArea(Radius)
    MsgBox "The area is: " & CStr(Area)
End Sub

Function CircleArea(Radius As Double) As Double
    CircleArea = 3.14159 * (Radius ^ 2)
End Function

In order to use the code provided above, you must put the code into a module. As noted before, code cannot exist outside of a module. This is enforced by the fact that there is no other place to put the code. If you need to create a module in which to store code, go to the Insert menu within the VBA editor (which can be opened from the Macros item on the Tools menu in Excel, or simply by pressing ALT F11), and choose Module. This will create a new code module named Module1. While it is technically legal to put the code within one of the existing Sheet modules or the ThisWorkbook module, those modules should be used exclusively for event procedures and should not contain any other code.

Snippets

A snippet is one or more lines of code that should be placed within a new or existing procedure. Code that is provided on web sites or newsgroups is often just a snippet that illustrates how to accomplish some task. A snippet cannot, by itself, be stored as code and cannot be executed. It must be placed within a procedure. For example, the following code is just a snippet:

    Dim Area As Double
    Dim Radius As Double
    Radius = 1
    Area = CircleArea(Radius)
    MsgBox "The area is: " & CStr(Area)

This code cannot exist in a project or module strictly as written. It must be placed within a new or existing Sub or Function procedure.

This page last updated: 9-December-2008.