Excel Vba Guide: Modules And Procedures #03

In the first part of this Basic Excel VBA guide series, we learned how to write a few simple lines of excel VBA code. On that page it is explained that among the steps to compiling a VBA macro code is to open VBE and then create a new module .

You can learn more about VBE or Visual Basic Editor in the second part of this Basic VBA Tutorial series, while in this third part we will invite you to get to know more about Modules and Procedures in Excel VBA.

I think this discussion is quite important and there are still very few who discuss it. Although it is only an overview, with this guide, I hope that those of you who are still starting to learn VBA will get an idea of ​​how and where to write VBA scripts correctly.

Modules in VBA Excel

What is the meaning of the module in VBA Excel?

In a simple sense, the module can be interpreted as a place to store VBA code. If the VBE is a house you can imagine the rooms or rooms in the house as a module, where in each room or room is stored various other items with certain functions.

Another item that is intended in this VBA discussion is the VBA Procedure which contains program codes with certain functions and purposes.

In VBA Excel, there are at least 4 known types of Modules as follows:

• Standard Module

Commonly referred to as a code module or just a module, it is a general type of module that can be filled with many procedures and certain functions. This type of module is what you will often use in compiling VBA code.

• Workbooks and Sheet Modules

Is a special module that is bound directly to Workbook and Sheet objects. The module for a workbook is called This Workbook , and the module for each sheet or Worksheet has the same name as the sheet name. This type of module is actually similar to the Standard Module , except that this type can also be filled with event procedures .

This event procedure is a kind of detonator or trigger to activate a code. If you’re familiar with VBA I think you already understand that to run a code requires a certain trigger such as a click on a button. In addition to using the button a code can also run under certain conditions. This condition or activity that occurs in the VBA object is called an Event, for example, we want to run several lines of code when a file is opened, when a sheet is active and so on, then this event procedure is what we need. We will discuss more about this  event procedure another time.

• User Forms

Is a type of module that is part of the UserForm object, and contains VBA procedures and event procedures for controls related to Form objects.

• Class Module

This type of module is used to create a new object in VBA. For now you don’t need to think too much about this type of module.

It is in these modules that you will later place and group VBA procedures as needed.

When you create an excel file, then open VBE, excel will automatically prepare a workbook module and a sheet module with a number of sheets contained in the file. By default there are three sheet modules that you can reduce or add as needed. As for other types of modules, you have to add them manually via the insert menu as explained in the previous guide.

Types of Modules 2 and 3 or Workbook Modules, Sheets and User forms are also commonly referred to as Object Modules because these modules have tangible forms in the form of Workbooks, WorkSheets, Userforms, Charts, and others. 

Generally, the VBA codes contained in this module only apply to related objects. For example, in the Sheet1 module, the codes that we create in it only apply to Sheet1 and cannot be called for Sheet2.

The Standard Module or Module Type 1, also known as the Code Module, is a container for all procedures or variables that can be used by other modules as well, as long as the procedure or variable is declared as Public .

You can add as many Code Modules as you need, although actually we only need to make one standard module to place the VBA codes, it’s just that if the codes are too complex there are times when we need to group the procedures we create in the module by the goal is to facilitate in terms of code management.

I have mentioned the term procedure several times, what kind of stuff is this actually?

Procedures in VBA Excel

A procedure is a block of program code that contains certain command lines between the opening statement line of the procedure and the closing statement of the procedure to carry out certain tasks or actions.

In other words, in Excel VBA, the type of program code block that contains commands to perform certain tasks is called a procedure.

If in the program code or VBA project that we make there are commands to perform the same task or activity in several places, it would be better if these commands were made in a procedure . Then, the procedure can be called on another procedure when needed.

The use of procedures saves a lot of writing program code, because the same program code in several places is enough to be made in one part only. In addition, the existence of a procedure will facilitate repairs if there are changes or errors, because repairs are enough to be done in one part only.

Excel VBA Procedure Structure

The declaration structure of a VBA procedure consists of 3 parts:

1. Opening or Initial Statement
2. Program code
3. Closing or final statement

A procedure written in a module always begins with an opening statement and ends with a closing statement . Then the program code performed by the procedure is written between the two statements.

Consider the following simple procedure example:

As seen in the example procedure above, how to write a simple procedure at least like this:

VBA CODE

[Scope] ProcedureType ProcedureName()

‘Program code line 1, etc.

End Type Procedure

The ” MsgBox ” command in VBA is used to display a message box with a specific message content. The details of these commands will be discussed later.

Next we will learn the general explanation for each part of the VBA procedure.

Declaration Statement Procedure

The opening statement or declaration of a procedure must contain at least 3 things:

VBA CODE

[Scope] ProcedureType ProcedureName()

The three things above are the simplest form of procedural declaration. So, every procedure declaration will always contain at least 3 things, namely: scope, procedure type, procedure name .

Actually, the declaration statement of a VBA procedure is not only this, there are several other keywords that you can also include, just to simplify the discussion, I think the 3 main parts of this declaration are enough for now.

The term statement which can also be interpreted as an instruction in VBA refers to 2 things:

• Declaration statements , as the name implies, are used to declare something like a variable or constant. In the VBA procedure the declaration statement contains keywords that have a specific purpose.
• Executable statements are statements to carry out certain actions or activities that must be carried out, generally these statements are program codes in a procedure.

Scope in VBA Excel

Because the discussion of Procedure also involves Scope, I think it is important to provide an overview of Scope in VBA Excel.

The term Scoope is used to describe how a variable can be accessed and how a procedure can be called . This depends on where and how a variable or procedure is declared. The term visibility is also used to describe scope. These two terms are equivalent.

There are 4 levels of scope from the smallest to the largest scope:

1. Procedure :

Variables that are used locally in that procedure only and cannot be accessed by procedures from other modules directly. The lifetime of the variable is as long as the procedure is processed. When the procedure has finished processing, the variable is removed from the computer’s memory.

2.Module :

Procedure or variable that is used by all procedures in the module and cannot be accessed directly by procedures from other modules.

3.Project :

Procedure or variable used by all procedures in the VBAProject. Its lifespan is from the time the VBAProject is loaded into the computer’s memory, that is, immediately after passing the security macro, until the workbook is finished closing.

4. Global : A

procedure, variable, or class that is used by all procedures from another VBAProject. The door to use is to make a reference to the required VBAProject. For example, VBAProject1 has a procedure, variable, or class that has a global scope. VBAProject2 can use what is in VBAProject1 if VBAProject2 adds VBAProject1 in its reference.

Scope is declared with certain keywords :

• Public

By using the keyword “Public” basically a variable or procedure can be used anywhere.

• Private

By using the keyword “Private” basically, variables or procedures can only be used by modules that use this keyword.

• Friend

Keyword is specific to object and class module only and is used by VBProject where Friend keyword is used.

• Dim

Keyword is used specifically for declaring a variable. Basically, it can only be used by program code that comes after the variable declaration line.

The discussion about this scope is actually quite long and will usually be quite confusing if you are new to Excel VBA. For this VBA introduction stage, just use the practical way of using it as follows:

1. Use Dim to declare variables within procedures only.
2. Use Private for variables or procedures that can only be used or called within the module .
3. Use Public for variables or procedures that can be used anywhere in the VBProject .

 

Type or Type of Excel VBA Procedure

In general, there are 2 types of procedures in VBA, namely Sub Procedures and Function Procedures.

• Sub Procedure

Sub Procedure is a procedure that only performs certain activities without generating a value (Value) . The activity in this procedure depends on the command lines contained in the procedure.

• Function Procedure

Function Procedure is a procedure that produces a value (Value) .

You must be familiar with various functions or excel functions such as the SUM function, AVERAGE and so on. The way these Function Procedure works is very similar to these excel functions. It’s just that these functions already exist in excel. If we need certain functions that don’t exist yet, you can create a special function which is often referred to as UDF ( User Defined Functions ) such as an excel function.

Next time we will discuss more about these two types of procedures in a separate article. For now, please get used to using the procedure with the Sub type first.

In addition to the two types of procedures above, there are actually other types of procedures in VBA excel, namely the Property Procedure type .

This type of procedure is used to assign a value to a variable or retrieve a value from a variable.

Generally, this type of property procedure is used when creating a custom class in the Class Module. So, basically, this type of procedure can be used anywhere just like Sub or Function type procedures.

The property procedure consists of:

1. Let : to assign a value to a variable other than an object and it works similarly to the use of type Sub
2. Set : to enter a value into an object variable and it works similarly to using the sub . type
3. Get : to retrieve the value of a variable and it works similar to the type of Function

Next time we will discuss about properties, but before that you must understand about Sub Types and Functions .

Excel VBA Procedure Name

A Procedure must be given a name. The main rules you should follow when naming a VBA procedure are as follows:

1. The first character must be a letter of the alphabet (AZ, az).
2. Subsequent characters can be letters, numbers or certain punctuation characters (not all punctuation marks can be used).
3. You can’t use periods (.) and spaces ( ) and you can’t use the following characters: #, $,%, &, @, ^, * and !.
4. There is no difference between uppercase and lowercase letters.
5. The maximum number of characters that can be used is 255.

Another explanation of the rules for naming this procedure you can read on the following Microsoft page.

It is better if the name used for a procedure is descriptive, meaningful and unambiguous so that it can represent the purpose or function of the procedure. Thus, when you read the name, you will easily know the meaning of the contents of the program code in the procedure. So don’t carelessly make a procedure name so that you will be confused when a VBA project already contains many procedures.

Excel VBA Program Code Line

The line of code is generally an Executable statement, is a program line that contains a VBA command to do something which of course must follow the VBA writing rules.

Lines of code in a VBA procedure are always processed line by line. So distinguish between program lines and write lines.

The program line must always be one line. One line of this program can be written in several lines of writing and it is possible that several lines of the program are written in one line of writing.

In writing program lines, there are several special characters that have special meanings or purposes, including:

• Single quotation marks (‘)

This character indicates that starting from this single quotation character is a description or text that does not need to be processed. This text or description is generally used to provide explanations for lines of VBA code.

• Underscore (_)

This character indicates moving the writing line for one line of the program. If a line of program code is long enough, use an underscore to indicate that the next line is a continuation of that line of program.

• Colon (:)

This character is used to mark the end of a program line. The colon (:) is the opposite of the underscore character (_). If Underscore is used to separate 1 line of program into several lines, a colon is used to make several lines of program into one line only. That is, in one line of writing there will be several lines of program.

Final Statement of Procedure

A VBA procedure always ends or ends with the keyword ” END ” followed by the type of procedure. Example: ” End Sub ” or ” End Function “.

VBA CODE

End Type Procedure

Excel VBA Practice

To better understand the VBA guide on this page, please follow the following exercises.

Exercise 1

As much as possible do the writing of each code manually ( Do not Copy Paste ) so as to train your sensitivity to VBA codes.

1. Create a new excel file with .xlsm extension and name the file ” Excel Class-VBA Practice 2 “. Save this file in the folder that we prepared in the previous VBA guide .

2. In the ThisWorkbook module create the following procedure:

VBA CODE

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

‘This is an example of the Event procedure in the ThisWorkbook module

    MsgBox “The specified sheet in this file is currently active”

End Sub

3. If so, try switching between sheets in your workbook. What happened?

When you activate Sheet1, sheet2 or sheet3 a message box will appear containing the text: “The specific sheet in this file is currently active “. Event The procedure in thisWorkbook runs when you activate any sheet in the file.

4. Next, in the Sheet1 module, create the following procedure:

VBA CODE

Private Sub Worksheet_Activate()

‘This is an example of the Event procedure on the module sheet

    MsgBox “Sheet 1 is currently active”

End Sub

5. Now try to enable Sheet1, Sheet2 and Sheet3 on the file in turn. What happened?

When you activate Sheet1 besides the message from the code in ThisWorkbook , another message appears which is the result of the code in the Sheet1 module . While on Sheet2 and Sheet3 the same thing does not apply.

From this exercise, you can learn that event procedures in thisWorkbook affect all Sheets while event procedures on one sheet have no effect on other Sheets.

Exercise 2

Still on the same file do the following:

1. Add 2 new standard modules by clicking Insert–Module .

2. Activate Module 1 (Double click) then write the following VBA procedure:

VBA CODE

Public Sub workoutMacro1()

‘This is a public procedure with the name “practiceMacro1”

    MsgBox “This is a public procedure in -Module 1- with the name -practiceMacro1-“

    MsgBox “Learn VBA by -Excel Class-“

End Sub

3. Run the sub procedure.

4. Create 2 new procedures under the Macro1 training procedure , the VBA code is as follows:

Macro2 training procedure :

VBA CODE

Sub workoutMacro2()

‘This is a procedure with the name “Macro2 workout”

    MsgBox _

            “This is the procedure in -Module 1- with the name -practiceMacro2-“

    MsgBox “Learn VBA by -Excel Class-“

End Sub

Macro3 training procedure :

VBA CODE

Private Sub workoutMacro3()

‘This is a private procedure _

  with the name “macroGeneral3”

    MsgBox “This is a private procedure with the name -practiceMacro3-“: MsgBox “Learn VBA by -Excel Class-“

End Sub

5. Run each of these sub-procedures in turn.

In the exercise above I wanted to emphasize how to create a simple procedure in a module and how to use single quotes (‘), underscore(_) and colon(:).

Please re-read the explanation in the previous section.

Exercise 3

If you can understand the meaning of the above procedures, now we can continue with exercise 3.

1. Still in the same file, open the standard Module 2 , then create a sub procedure with the name exerciseMacro4 with the following code:

VBA CODE

Sub workoutMacro4()

‘Calling the practice procedureMacro1

    exerciseMacro1

End Sub

2. Run the procedure.

This macro4 training procedure intends to call or run the Macro1 training procedure by writing the name of the procedure to be called earlier. When the Macro4 exercise is executed, this procedure will call and run the program code contained in the Macro1 exercise.

3. Next we create 2 other procedures in module 2 with each code as follows:

Macro5 training procedure :

VBA CODE

Sub workoutMacro5()

‘Calling the training procedure Macro2

    exerciseMacro2

End Sub

Macro6 training procedure :

VBA CODE

Sub workoutMacro6()

‘Calling the training procedure Macro3

    exerciseMacro3

End Sub

4. If so, please try to run the two procedures above alternately. What happened?

When trainingMacro5 is executed this procedure calls to run the training procedureMacro2 so that what appears is a message box as you can see when running the training procedureMacro2 .

When the Macro6 exercise is run what happens is an error ” Compile error: Sub or Functions not Defined ” as shown below:

Why did this happen? Yes, your guess is correct, because the declaration in the Macro3 exercise scope is private, so the procedure can only be called from module 1 and cannot be called from module 2.

Besides trying to understand a little about private and public scopes from this 3rd exercise, I also want to convey that basically the sub procedure in a standard module scope is public so that if you don’t write the Public keyword in a VBA procedure in a standard module, it will be considered as public ( see exerciseMacro1 and exerciseMacro2 ) and is only considered private if you include the keyword “Private” in the procedure ( exerciseMacro3 ).

If you are still confused by the explanation on this page, I really understand because of my limited ability to choose words. For that, don’t hesitate to ask through the comments section that is already available.

So first our discussion about Modules and Procedures in VBA Excel . If this tutorial is useful, please also share it so that your other friends also get the benefits.

Thank you, see you in the next series of Basic VBA guides we will learn about Data Types in Excel VBA.