VBA standard modules and class modules

We have already learned how to insert an empty module into the project of VBA through previous introductions. As you can see from the Insert module, there are two kinds of modules-standard module and class module. A class module is a special module that contains the definition of a class, including its properties and methods. In the following will be an introduction and description.

As the project becomes more and more complex, we are likely to have multiple modules. The advantage of using multiple modules is that it allows the associated processes to be aggregated, making the code more maintainable and reusable, and making it easier for us to manage our code. Through different modules, we can also customize different behavior for different modules, custom module behavior methods have 4 kinds:

1, Option Explicit. When you use Option Explicit, you must declare each variable in all procedures in the module, or a syntax error will not be compiled. The advantage of this is that it eliminates program errors caused by misspelled names in your program, so use this option. Automatically set method: In the VBA Editor tool menu in the selection of the Editor tab in the request to declare variable options. This declaration is inserted automatically each time the new module is inserted.

2, Option Private Module. When you use this setting, the code in the module will be marked private so that the code is not visible in the Macro dialog box, that is, the private subroutine name is not displayed in the Tools/Macros/Macros dialog box in the Excel main interface, which prevents the contents of the module from being referenced by other projects, However, other modules in the same project are still available.

3. Option Compare {Binary | Text | Database}. The default comparison method used to declare string comparisons. If there is no Option Compare statement in the module, the default text comparison method is Binary.

Option Compare Binary is a sort order derived from the internal binary representation of a character for string comparisons. In Microsoft Windows, the sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < A < B < E < Z < _ < _ < _ < _ < _ <?

Option Compare text compares strings based on a case-insensitive text sorting level determined by the system region. When you sort the same characters using option Compare text, the following text sorting levels are generated:

(A=a) < (_=_) < (B=B) < (e=e) < (_=_) < (Z=Z) < (_=_)

Option Compare Database can only be used in Microsoft Access. When string comparisons are required, comparisons are made based on the sort level determined by the region ID of the database.

4, Option Base {0 | 1}. The default lower bound used to declare an array subscript.

Note that the TO clause in Dim, Private, Public, ReDim, and Static statements provides a more flexible way to control the subscript of an array. However, if you do not explicitly specify a lower bound using the TO clause, you can use Option Base to set the default lower bound to 1. The lower bound of an array created with the array function or ParamArray keyword is 0;option Base does not work for array or ParamArray.

The Option Base statement affects only the lower bounds of the array in the module that contains the statement.