Four types of Autorun macros in VBA and what the module means

You can create 4 auto-run macros in standard modules in Excel, which are Auto_Open (run automatically when you open the workbook), Auto_Close, Auto_Activate, Auto_Deactivate. These autorun macros are reserved for compatibility with EXCEL5 and 95. You can now replace them with the Open,close,activate,deactivate event of the workbook.

So, what is a "standard module"? In fact, the module we inserted in the VBE, Microsoft called it a "standard module." While the rest of the three categories: Microsoft Excel objects, forms, and class modules also belong to the module, the relationship between them can be seen in this post: http://www.excelpx.com/thread-170289-1-1.html

(a) What are modules and what modules does VBA contain?

First, let's look at the three definitions in Microsoft VBA Help:

Module: A set of claim sets, followed by procedures

Declaration: Non-executable code that names a constant, variable, or procedure and specifies its attributes, such as the data type. For DLL procedures, declare the specified name, library, and parameters.

Procedure: A named sequence of statements that can be executed as a unit. For example, Function, property, and Sub are procedure types. The name of the procedure is always defined at the module level, and all executable code must be contained within the procedure, and the process cannot be nested in other procedures.

Through these three definitions we learn what information, what we usually write code is something, right, is the declaration and process, that is, in Sheet1,sheet2,sheet3,thisworkbook,userform1, Module 1, Class 1, the code written in these things, So we can conclude that Sheet1,sheet2,sheet3,thisworkbook,userform1, Module 1, Class 1 are all modules.

Although these are called modules, but they are different, in fact, Microsoft is classified as follows: (Microsoft such classification is based on, we will find the basis behind)

Document module: Sheet1,sheet2,sheet3,thisworkbook

Form module: UserForm1

Standard module: Module 1

Class modules: Class 1

Written here, one might ask, Sheet1,sheet2 these are the built-in objects that Microsoft provides for us, how can we say modules? Admittedly, in the process of writing our code, they are really different objects. However, because Microsoft has extracted these things separately, allowing us to write code to associate with these objects, they have different meanings and can be seen as Microsoft's custom and embedded modules. Since we can write and store code inside them, you should not look at them like Range,cell, but rather as a module to see them. (This shift in thinking is important, and they are here to be viewed as modules rather than as objects we use).

In fact, as the VBA language, from the VB, so he inherited most of the VB language features, but Microsoft alone for VBA provides the Excel object (or Word object, PPT object, etc.). Because in VB itself provides three kinds of modules: Form module, standard module and class module. So there are three of these modules in VBA. In order to be able to manipulate the Excel object, Microsoft also provided us with the document module, and is directly embedded in (Sheet1,sheet2,thisworkbook these document modules do not need us to insert, as long as there is this object, there is this document module). So, it explains why VBA has these four types of modules.

We learned from the above analysis of the VBA module is divided into these four kinds, from where can be seen from the Microsoft is classified as such, they are not the object (please be certain and the Excel model of the concept of Sheet1,sheet2 objects separate, remember, here they are the module), What the hell are they, everybody please look down.

(b) What is the object of the module? How does Microsoft differentiate him?

Or should I borrow this diagram?

We see Microsoft Excel objects (document modules), Forms (Form modules), modules (standard modules), class modules What are these modules under, right, under the VBAProject:

What is VBAProject, which is what we normally call engineering, in fact he is the VBAProject object, he is the following Microsoft Excel object (document module), form (Form module), module (standard module), class module is also object, what object is it, The VBComponent object. Now we can tell you that these modules are objects, which are called VBComponent objects.

To illustrate this problem, you need to introduce a concept, the VBA extension model (VBA extensibility models), which has the ability to manipulate VBA projects and modules:

(1) Add and remove VBA modules in code (instead of manually inserting and deleting them);

(2) Code to create the code (say a bit around, it should be the code to build a module, and then code in the module to write code)

(3) Create user form

VBA extension model can achieve a lot of functions, I can not say too clear, after all, I was just contact, hehe.

The simple hierarchy of the VBA extension model is this (just to illustrate the problem, not all listed):

VBE

VBProject

VBComponent

CodeModule

Designer

Property

Reference

Window

CommandBar

As seen, in fact, the VBProject object is what we often see in the project Asset Management, the following sheet1,sheet2,sheet3,thisworkbook,userform1, Module 1, Class 1 is VBComponent object. Here we come to a conclusion: Document module, standard module, class module, form module is an object, a kind of object called VBComponent

VBComponent object has a type attribute, clearly tells us Sheet1,sheet2,sheet3,thisworkbook,userform1, Module 1, Class 1 respectively belongs to what module, namely above said, How does Microsoft classify these modules, that is, the type attribute to tell us the problem.

We use the following code to show which types these modules belong to.

Sub Test ()

Dim Vbcomps as VBComponents ' defines an object variable for the VBComponents class

Dim Vbcomp as VBComponent ' defines an object variable for the VBComponent class

' Here we borrowed the object of ThisWorkbook to illustrate

Set vbcomps = ThisWorkbook.VBProject.VBComponents ' Assigns an object reference to an object variable

' Use loops to get the name and type of each component within the project

For each vbcomp in Vbcomps

MsgBox "Component name:" & vbcomp.name & "Component Constants" & Vbcomp.type

Next

End Sub

Copy Code

By running the code, we find that Sheet1,sheet2,sheet3,thisworkbook is a type, and the value of the part constant is 100.

Module 1 is a type, and the value of the part constant is 1

Class 1 is a type, and the value of the part constant is 2

UserForm1 is a type, and the value of the part constant is 3

Is the meaning of each value of the component constant

Vbext_ct_document (constant) 100 (value) document module (description)

Through the above narrative, I do not know whether to clarify this problem, summed up just a sentence:

Document modules, standard modules, class modules, form modules are objects, and are all objects called vbcomponent, depending on the VBComponent attribute "type", Microsoft divides them into 4 categories: vbext_ct_StdModule, Vbext_ Ct_classmodule, Vbext_ct_msform, Vbext_ct_document.

(iii) Introduction of different modules and differences between modules

We've got a rough idea of the identities of these modules, so let's talk about the module itself.

The following is an article called "VB in the form module, class module, the difference between the standard module" article content, the original article chain to meet here

http://ykkykkl.blog.163.com/blog/static/718942282010717104046309/

VB code is stored in the module. There are three types of modules available in VB: Form modules, standard modules, and class modules.

A simple application can have only one form, the programs that reside in the form module, and when the application is large and complex, additional forms are attached. There may be several forms in which there are some common code to execute, in order to create a separate module that does not produce duplicate code in two forms, to implement code common. The standalone module is the standard module. You can also build a class module that contains shared code and data.

1. Form Modules

Because VB is an object-oriented application development tool, the code structure of the application is the corresponding model that the program represents on the screen. The object contains data and code according to the order of intersection. Each form in the application has a corresponding form module (with a. frm file name extension)

Form modules are the basis of VB applications. A form module can contain procedures for handling events, common procedures, and form-level declarations for variables, constants, custom types, and external procedures. The code that is written to the form module is specific to the application that the form belongs to, or it can refer to other forms and objects within the program

Each form module contains an event procedure that has a segment of the program that executes in response to the event during the event. A form can contain controls. In a form module, there is a corresponding set of event procedures for each control on the form. In addition to the event procedure, a form module can contain a common procedure that responds to calls from any event procedure in the form.

2. Standard modules

A standard module is a separate container in a program that contains global variables, function (functions) procedures, and Sub procedures (sub-procedures).

You can put code that is not related to a particular form or control into a standard module. The standard module contains procedures and declarations within the application that allow access to other modules. They can contain variables, constants, types, external procedures, and global declarations or module-level declarations.

3. Class Modules

In VB, the class module is the basis of object-oriented programming. You can write code in a class module to create a new object. These new objects can contain custom properties and methods. In fact, a form is a class module where controls can be placed to display form windows.

Use class modules to create objects that can be called by procedures within an application. A standard module contains only code, and a class module contains code that contains data and can be treated as a control without a physical representation.

The above is very clear, I only write some personal views:

I personally think, first of all, we have to distinguish between the standard module and the document module, the form module, the class module partition.

Why do I say that, in fact, Microsoft itself is such a distinction, in the VBA help has such a concept:

Object modules: Modules that contain object-specific code, such as class modules, form modules, and document modules. The object module contains the code after the associated object, and the rules of the object module are different from the standard modules.

From the above concept, we can see that the standard modules and document modules, form modules belong to the object module, can be used as a class, the standard module is different, belonging to another class.

I personally think that the difference between the standard module and the object module is mainly two points:

1. First, the standard module does not support events, and the object module supports event procedures.

The standard module itself is not an event process, which means that you want to write the event process, never write to the standard module, of course, you can not write in.

2. A standard module is a stand-alone container in which global variables, function (functions), and Sub procedures (sub-procedures) in a standard module are not associated with an object module.

Object modules in the same project can share and use global variables and procedures in standard modules. However, the variables and procedures of the object module are exclusive to the object module. If you want to invoke it, you must access it as an object.

And that tells us a principle. Usually when we write VBA code, we should write the common variables and procedures into the standard module, and the variables and procedures related to the object module are written to the corresponding object module, so that the problem is not easy to appear. (I wrote too few VBA code, anyway, I am a little bit confused, when writing code, not quite distinguish between the standard module or object module, it seems that I want to correct later.) ）

(iv) Some speculations about document modules, form modules, and class modules

Next, I would like to separate the document module, form module, and class module separately for two more sentences.

In my thinking and practice, I found that the form module and the document module can define properties, methods and events exactly like the class module (this can be done by yourself, the code is too cumbersome, I won't post it), and can be called in other modules, so I guess the form module and document module is actually a kind of module, is just a special class module.

Through the Object Browser, as shown in

From what we see

Sheet1,sheet2,sheet3,thisworkbook belongs to Class

UserForm1, Class 1 belongs to private class

It should be said that the document module and the form module are really a special class of modules, or have a part of the function of the class module (later this idea has been confirmed by the HYY514 teacher), so they can define properties, methods and events, and in other modules called.

However, compared with standard class modules, form modules and document modules cannot be instantiated as new objects as class modules, they are already instantiated objects.

The document module can be seen as a Microsoft custom and embedded class module, which is a module that we manually insert (but he is an instantiated object).

So although they can define properties, methods and events, but also only to the object itself, and can not be instantiated as a new object (such as the Sheet1 module definition of the properties, methods, we can only use the SHEET1 call, not like the standard class module, and then create a Sheet1 object), This is the biggest difference between a document module, a form module, and a standard class module.

Because of the limited knowledge, I can now summarize only these, I hope to have the opportunity to continue to share with you. The above narrative must have the wrong, unreasonable and inaccurate, please be positive to me to point out, but also hope that everyone actively and I discuss, can make these concepts more clear, in order to facilitate the learning of VBA.

Four types of Autorun macros in VBA and what the module means