Explaining VBA Modules

The main difference of this blog from the others, in a more technical explanation, is how we deal with vba modules. Instead of publishing lines of code of functions or macros, that solve individual problems, we publish full module lines of code, so one can import the module and use all the routines and functions available on it.

This is the same approach used in the other languages. All of them use this "library" model. Even Excel uses it too. Let me explain.

All the API's (application programming interfaces) a user can write in a vba program (this is, the name of the functions, variable types etc.) are defined in the VBA references. Those references are found accessing the VBA Editor, clicking in 'Tools" then in "References" (Image 01)

Image 01: VBA References

The references in the image that are checked are, in a simplistic way, files with the definition of the codes the user can type into the editor. If the user wants to, say, use a ADODB connection function to connect to a SQL Server, he or she must check one of the "Microsoft Active X Data Objects" libraries available to add this reference so the APIs for this kind of connection are available - otherwise the compiler will not find the functions the user coded, and a error message will be displayed (Image 02).

Image 02: A reference is missing

This is how VBA abstracts the programming for the user: it makes references available for the programmer so he or she can add to solve specific problems such as dealing with Outlook objects or Word objects.

By default - as Image 01 shows - not all the references are checked. This means that for some programs, the user must add the references before coding - an example is when dealing with Word objects, which require the "Microsoft Word" reference checked, which is not checked by default.

So those references are all the libraries the VBA Editor makes available for the programmer. So our idea is to give the programmer more references - more APIs -, but, instead of really using references, which require a special compiler and some complex work to be created, we use modules, that can be added almost as easily as references.

So let's start explainig what are modules. Modules are TEXT files (very important to know that) that contain regular VBA code but that can be imported from and exported to Microsoft applications (such as Excel).

For the sake of simplicity, for now we will assume that there are only 2 types of modules: CLASS MODULES and REGULAR MODULES (or simply MODULES). See image 03 below.

Image 03: modules and class modules

The only 2 real differences, from the user's perspective, of this 2 kinds of modules are the text file layout (presence of the header) and the file extension. The programming differences will be discussed in another post.

When we export a regular module (we'll teach how to do that in a minute), the file exported has the ".BAS" extension and, when you open this file in a text editor, you will find the vba code but with a first line header similar to this:

Attribute VB_Name = "THIS_IS_AN_EXAMPLE_MODULE"

On the other hand, when you export a CLASS MODULE, the file has the extension ".CLS" and a text header like this:

VERSION 1.0 CLASS

BEGIN

  MultiUse = -1  'True

END

Attribute VB_Name = "ACCESS_CLASS"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = False

Attribute VB_Exposed = False

As you can notice, these headers are not in italic because they ARE NOT VBA CODE. This information is only necessary to import and export modules using the import file/export file feature of the VBA Editor and they only appear in the files themselves, not in the text of the code after they are imported to the VBA editor.

IMPORTING/EXPORTING MODULES

There are 2 ways to import a module: manually or using the Import File feature.

Importing Manually: To import a module named, say, TEST_MODULE, manually, the user must create a new empty module (Insert - Module), change the module name to "TEST_MODULE", open its text editor and paste the code of it WITHOUT the "Attribute VB_Name" header (in case you are importing it from a .bas file or without the ".cls" header file (mentioned above) in case it is a ".cls" file. This is: paste only the VBA code, not those flags.

Importing using Import File: To import using the import file feature the ".bas" or ".cls", the user must click File - Import File and select the ".cls" or ".bas" file he or she wish to import which has the module code and the header - which will not be imported to the VBA Editor, since it is not VBA code.

To export manually, the user should copy the module code and save it on a text file.

To export using the Export File feature, the user should select the module to be exported by clicking on it, then  File - Export File and saving it as a ".cls' or ".bas" file in case it is a class module or a regular  module respectively.

Now that you know how to import and to export a module, let's make an example.

Open your excel 2013 and go to the VBA Editor. Create a new regular module (Insert - Module) and name it TEST_MODULE. Open its text editor and paste the code below.

Option Explicit

' This is an example routine

Public Function Module_Test()

    

    MsgBox "I am a module test", vbInformation, "Module Test"

    Exit Function

    

End Function

Now  let's export this module by clicking on the module's name, then File - Export File and saving it with the name TEST_MODULE.bas.

Close the workbook without saving it.

Now open a new excel workbook. Create a new module named "MAIN" (just a best practice) and insert the code below.

' This is an example routine
Public Sub MAIN_Test()
    
    Call TEST_MODULE.Module_Test
    
    Exit Sub
    
End Sub

Run it (Run - Run Sub/User Form).

An error message should appear with the message "Variable not defined". This means that the compiler didn't fnd the TEST_MODULE module neither the Module_Test function. So let's import them.

Click File - Import File and select the TEST_MODULE.bas file. Click Open. This imports the TEST_MODULE module (which should appear in the list of modules in the Project Explorer).

Now run the MAIN_Test sub in the MAIN module again. A message box with the message "I am  a module test" will be displayed. This means that the routine in the MAIN module was able to find the Test function in the TEST_MODULE module. You have just used a module as it should be used. Congratulations!