VBA class module preliminary

 

 

Here we will briefly introduce the class module in VBA, so that you can create and use simple classes in applications.
Class is the "template" of the object ". The object can be anything, and the class does not do anything or occupy memory. Only after the class becomes an object and is instantiated as a specific object using the set statement and the New Keyword, to do things and occupy the memory. The syntax for instantiating a class as a specific object is:
Dim C as class1
Set c = new class1
The preceding statement creates an object named C. The data type of this object is the defined class class1.
Before introducing the class in detail, let's take a look at the user-defined data type of VBA, that is, the variable defined by the Type keyword. For example, the following type variable defines employee information:
Type employee
Name as string
Address as string
Salary as double
End type
The preceding statement defines the variable employee, including the element name, address, and salary. Then, you can declare a variable of the employee type and assign values to each element:
Sub test ()
Dim fan as employee
Fan. Name = "fanjy"
Fan. Address = "Yichang"
Fan. Salary = 1000
End sub
User-Defined types are useful, but there are three major limitations:
1. All custom type variables must be declared during compilation. Although dynamic arrays can be used to process multiple custom types, the redim preserve keyword must be used. In addition, you cannot add new custom type variables at runtime.
2. the value assigned to the element of the custom type cannot be controlled. For example, in the above Code, it is possible to assign a negative value to the salary element.
3. Do not do anything about custom types, but store data statically.
User-Defined types are widely used in calling Windows API functions. In addition, using class modules is a better choice. Class to overcome the limitations of user-defined types.
1. Using the New Keyword, you can create new instances of any number of classes and store them in collection objects.
2. Using the property let/set/get statement, you can write code to verify the value assigned to the class element, and write the corresponding code executed when the value changes. For example, you can write code to ensure that the salary value is not negative.
3. A class can define a method (using sub and function processes) to execute an action.
Next, let's convert the custom type 'employe' to a class. First, insert a class module in the VBE editor and rename it as cemployee, as shown in 1. (The class module consists of attributes and methods. Classes are similar to nouns. attributes can be used as adjectives to describe classes. methods are verbs and operations are performed .)
The cemployee class should have three attributes: name, address, and salary. In the declaration part of the class module, three private variables are declared to store these values. The actual values of the attributes are stored in these three private variables.
Private pname as string
Private paddress as string
Private psalary as double
Because these variables are declared as private, they can only be accessed within the class and are invisible outside the class. How do I assign values to these variables? This is what the property let statement does. Each private variable has a corresponding property let statement.
Property let Name (s as string)
Pname = s
End Property

Property let address (S as string)
Paddress = s
End Property

Property let salary (D as double)
Psalary = d
End Property
The property let statement is used to assign values to properties, which introduce values to classes. In the preceding example, the introduced value is assigned to a private variable. Because the property let statement can contain code, you can write the code for data verification. For example, rewrite the code in the property let salary function so that it cannot accept negative values:
Property let salary (D as double)
If D> 0 then
Psalary = d
Else
'Error message
Msgbox "How can I afford my salary ?"
End if
End Property
The property get function corresponds to the property let function, which is used to obtain the property value from the class. In the cemployee class, the corresponding property get statement is:
Property get name () as string
Name = pname
End Property

Property get address () as string
Address = paddress
End Property

Property get salary () as double
Salary = psalary
End Property
The Data Types of the let statement and the get statement must match. For example, let salary accepts the value of the double type as its parameter, which means that the value of the corresponding get attribute returned by it must be double. If the data types are inconsistent, compilation errors may occur.
These get statements simply expose the three attributes. To create a read-only property, ignore the property let statement and only use the property get statement. For example, the withholdingtax attribute is read-only. In the get statement, the Code calculates an appropriate value and makes it public, but avoids changing the value of this attribute from the outside:
Property get withholdingtax () as double
Withholdingtax = some_tax_calculation
End Property
This attribute does not have a corresponding let statement. Therefore, this attribute is read-only and cannot be assigned to withholdingtax.
The class module can contain methods. For example, the cemployee class has a method for employees to review their salaries:
Public sub printpaycheck ()
'Place the actual code for printing and review
End sub
Now, we have completed the preliminary definition of the cemployee class, as shown in 1.

Figure 1: cemployee class
Next, instantiate the class in the code of the standard module and use its attributes and methods. First, insert a module in the VBE editor to declare a cemployee type variable. (Note: In complex applications, it is completely legal to declare and instantiate another class in the class module)
Dim EMP as cemployee
Next, instantiate the class and create an actual object that can be used for work, as shown in the following statement:
Set EMP = new cemployee
The preceding statement creates an object named EMP, which can use the attributes in the cemployee class to reference specific employee information. For example:
EMP. Name = "fanjy"
EMP. Address = "Yichang"
EMP. Salary = 1000
These three statements call the property let statement declared in the cemployee class to assign values to private variables in the class. We can use the following code to read the attribute values of an object:
Debug. Print EMP. Name
Debug. Print EMP. Address
Debug. Print EMP. Salary
The preceding statement calls the property get statement in the class module and obtains data. We can also read the read-only attribute withholdingtax in the class:
Debug. Print EMP. withholdingtax
Because the withholdingtax attribute does not have a corresponding let statement, it cannot be assigned a value. If you try to assign values:
EMP. withholdingtaxes = 4000
"Compilation error: the read-only attribute cannot be assigned ".
You can also call a method to perform the operation:
EMP. printpaycheck
Multiple instances of the Storage Class in the collection
If you do not know how many employees there are at runtime, you can create multiple EMP objects as needed. Each time an EMP object is created, it is stored in the collection. For example:
Dim Coll as new collection
Dim ndx as long
Dim EMP as cemployee

For ndx = 1 to numberofemployees
Set EMP = new cemployee
'Set the attributes of the EMP object
Coll. Add item: = EMP, key: = EMP. Name
Next ndx
The above... The next loop will create numberofemployees instances of the cemployee class. The specific quantity is determined by the runtime and stored in the Set object named Coll. Then, you can use the for each loop to obtain information about each employee from the coll set or perform operations:
For each EMP in Coll
EMP. printpaycheck
Next EMP
Instancing attributes of the class
The instancing attribute of the Class determines its visibility (or scope). The default attribute value is 1-private, which means that the class can only be created and accessed in the project that contains the class. Other projects cannot create objects based on this class. Private is sufficient for most applications.
The other value of the instancing attribute is 2-publicnotcreatable, indicating that other projects can declare variables as the class, but cannot use the set statement to create instances of the class.
Use classes between multiple projects
If a workbook needs to use a class defined in another workbook, you need to write code in the workbook containing the class module to export the class to another project. The instancing attribute of this class must be 2-publicnotcreatable.
Assume that book1.xlsincludes a class named class1, and book2.xls needs to use this class. First, change the project name of the book1 workbook from the default "vbaproject" to a unique name, for example, myproject. Then, activate the book2 workbook page in the VBE editor and set a reference to the book1 workbook. In VBE, select "tool-reference" and select "myproject" in the list ". Then, in book2, create the following statement:
Public C as myproject. class1
Because the value of instancing is publicnotcreatable, you can declare a class1 class variable, but you cannot create an instance of this class. Therefore, you need to write a function in book1 to create a new instance of class1 and return the instance to book2. In book1, create the following process:
Public Function getclass1 () as class1
Set getclass1 = new class1
End Function
Then, set the public variable C in book2 as the result of the above function, for example:
Set c = myproject. getclass1 ()
Now, C is set as a new instance of class1.
Well, the above is only a preliminary introduction to the class module, and some knowledge is not described, such as the property set statement, which will be sorted out later.