When dealing with VBA, you will come across terms such as modules and procedures, which are not used in everyday life. Here, we would like to explain about modules and procedures.
*Modules include standard modules, book modules, sheet modules, form modules, class modules, etc. Modules here refer to standard modules. Modules other than standard modules will be described in the future.
What is a module?
Think of a module as a place to enter a VBA program.
Some large-volume programs have several of these modules.
While it is of course possible to create just one module and put all the code in it, there are many situations where you can create several modules and use each one differently. Why do so? The advantages of separating modules are described below.
Advantages of Separate Modules
Separate modules have the following advantages
Advantage 1, code can be separated when creating different functions within a system.
The functionality built into a single system is not necessarily a single function. Often, completely different functions are combined to form a single system.
For example,
- Module 1: Function to import data on the web into Excel
- Module 2: Function to process data
- Module 3: Ability to save created data in csv format
Suppose there is a system A consisting of
Next time you want to create a completely different System B, and you want a function to save data in csv format, you can say, "Oh, I remember that System A had a function to save data in csv format when I created System A. Let's use that function. If you want a function to save data in csv format, you only need to copy module 3 of system A to system B and apply that function.
If this is in one module, you will have to read System A carefully to see where the code to save the csv file is from, and if the range of data is incomplete, errors will occur, and it will be difficult to determine why the errors are occurring.
Therefore, when creating different functions, try to create separate modules as much as possible.
Benefit 2, Code becomes easier to read.
Connected to merit #1, if you are creating several different functions and have code for two or more functions mixed in one module, the module will be long and the code will be very difficult to read.
If the functions can be completely separated, the code will be very readable if the modules themselves are separated.
How to create a module
How to create a new module.
First, click on "View Code" on the ribbon that appears from the "Development" tab on the Excel toolbar
*If there is no "Development" tab, refer to "How to create your first Excel (Excel) macro (VBA)".
Then click on the ▼ sign to the right of the Insert button on the screen below and click on "Standard Modules
Then "Module1" is created as shown below.
The new screen on the right is the module window.
We can now create a new module called "Module1".
What is a procedure?
Think of a procedure as a single instruction statement.
For example, the area enclosed by Sub and Endsub as shown below.
Advantages of Separate Procedures
Advantage 1, code is shorter and easier to read if the same thing is repeated in the code
Sub Execution()
If a = b Then
Call Process 1
Else
Call Process 1
Call Process 2
End If
End Sub
'--------------------------------------------------------
Sub Process 1()
'Put the code for Processing 1 here
End Sub
'--------------------------------------------------------
Sub Processing2()
'Put the code for Processing 2 here
End SubFor example, if you have a code like the above
When a=b, only process 1 is executed.
If a=b, process 1 and process 2 are executed in succession.
If you wish to do this, it is necessary to describe the contents of process 1 twice, but if the procedures are separated, there is no need to describe the contents of process 1 many times. If the procedures are separated, there is no need to describe the contents of process 1 more than once. Also, when modifying the contents of process 1, the modification can be completed only by modifying the procedure of process 1, so there will be no omissions.
Advantage 2, Easy to use cords
Sub Execution1()
Call Process 1
Call Process 2
End Sub
'--------------------------------------------------------
Sub Execution2()
If a = b Then
Call Process 1
Else
Call Process 1
Call Process 2
End If
End Sub
'--------------------------------------------------------
Sub Process 1()
'Put the code for Processing 1 here
End Sub
'--------------------------------------------------------
Sub Processing2()
'Put the code for Processing 2 here
End SubSuppose there is a code "Execution 1" and "Execution 2" with very similar contents as shown above.
Advantage 3, minimizing the number of areas to be modified.
Sub Execution1()
Call Processing 1
Call Process 2
End Sub
'--------------------------------------------------------
Sub Execution 2()
If a = b Then
Call Processing 1
Else
Call Processing 1
Call Process 2
End If
End Sub
'--------------------------------------------------------
Sub Processing 1()
'Put the code for Processing 1 here
End Sub
'--------------------------------------------------------
Sub Processing2()
'Put the code for Processing 2 here
End SubSuppose you want to modify procedure "Processing 1," which appears across multiple procedures as shown above.
If the procedures are not separated and described as they are, there will be three places to modify, but if the procedures are separated and described, all procedures can be modified just by modifying the "Processing 1" procedure, so there will be no omission of modification.
Disadvantages of Separate Procedures
Variables do not automatically take over.
Separating procedures cannot take over arguments in variables as they are.
In that case, you need to pass arguments to the following procedure.
The method is explained here.
How to pass arguments between procedures ByRef,ByVal Excel(Excel) Macro(VBA)
How to Create Procedures
The procedure is first entered as "Sub Procedure Title (any letter)".
sub ExecutionWhen you have typed up to this point (with the cursor to the right of the execution), press the "enter" key.
sadistub Execution()
End SubIt automatically adjusts to the shape shown above.
The rest of the code is written between this "Sub" and "End Sub".
If you want to create the next procedure, you can do it again with the cursor under "End Sub".
Sub Procedure Title (any character)" and press "Enter" key to create it.
Sub Execution()
End Sub
Sub Process1()
End Sub
Comment