VBA Brush Up 08：Custom Collections and Class-CSDN博客

Technorati 标签: VBA, Collection, class, customclass

来自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p102-p141

（1）Event — An action recognized by an object, such as a mouse click or a keypress, for which you can define a response. Events can be caused by a user action or a VBA statement, or can be triggered by the system.

（2）Class Module — A module that contains the definition of a class, including its property and method definitions.

（3）Form Module — A module that contains the VBA code for all event procedures triggered by events occurring in a user form or its controls. A form module is a type of class module.

（4）Module — A structure containing subroutine and function procedures that are available to other VBA procedures and are not related to any object in particular.

（5）在立即窗口试验集合类的方法：

 
 set myTestCollection = New Collection 
myTestCollection.Add "first member"
For Each m in myTestCollection : myTestCollection.Remove 1 : Next
 

（6）

 
 Dim collection_name As New Collection
object.Add item, key, before, after

The objects with which you populate your collection do not have to be of the same data type. You are only required to specify object and item. Object is the collection name. This is the same name that was used in the declaration of the Collection object. Item is the object you want to add to the collection.

items in a collection are automatically assigned numbers starting with 1. However, they can also be assigned a unique key value. Instead of accessing a specific item with an index (1, 2, 3, and so on) at the time an object is added to a collection, you can assign a key for that object.

If you want to specify the position of the object in the collection, you should use either the before or after argument (do not use both). The before argument is the object before which the new object is added. The after argument is the object after which the new
object is added.

（7）

 
 Sub NewEmployees()
    ' declare the employees collection  
    Dim colEmployees As New Collection 
    ' declare a variable to hold each element of a collection  
    Dim emp As Variant
    ' Add 3 new employees to the collection  
    With colEmployees 
        .Add Item:="John Collins", Key:="128634456" 
        .Add Item:="Mary Poppins", Key:="223998765" 
        .Add Item:="Karen Loza", Key:="120228876", Before:=2 
    End With 
    ' list the members of the collection  
    For Each emp In colEmployees 
        Debug.Print emp 
    Next
    ' remove the third element from the collection  
    colEmployees.Remove (3) 
    MsgBox colEmployees.Count & " employees remain."
End Sub
 

Note that the control variable used in the For Each…Next loop must be Variant or Object.

（8）Collections are reindexed automatically when an item is removed. Therefore, to remove all items from a custom collection you can use 1 for the Index argument, as in the following example:

 
 Do While myCollection.Count >0 
    myCollection.Remove Index:=1 
Loop
 

（9）Every time you create a new class module, give it a meaningful name. As a rule, the object class name is prefaced with an uppercase “C.”

（10）Class variables are called data members and are declared with the Private statement. It’s traditional to preface the class variable names with m_ to indicate that they are data members of a class.

（11）To enable other parts of your VBA application to set or retrieve the employee data, you must add special property procedures to the CEmployee class module.

 
 Property Get LastName() As String
    LastName = m_LastName
End Property
 

Similar to function procedures, the Property Get procedures contain an assignment statement. As you recall, in order to return a value from a function procedure, you must assign it to the function’s name.

（12）Immediate Exit from Property Procedures: Exit Property

（13）

 
 Property Let LastName(L As String) 
    m_LastName = L 
End Property
 

The Property Let procedures require at least one parameter that specifies the value you want to assign to the property. This parameter can be passed by value (see the ByVal keyword in Property Let Salary above) or by reference (ByRef is the default). The data type of the parameter passed to the Property Let procedure must have exactly the same data type as the value returned from the Property Get or Set procedure with the same name. Notice that the Property Let procedures have the same names as the Property Get procedures prepared
in the preceding section.

（14）To preserve the Property Get procedure’s local variables between procedure calls, use the following statement format:

Static Property Get FirstName() As String

不知道在不同的类实例之间是否能preserve局部变量，如果这样，我就可以不必维护全局的annatrees变量了。

（15）Before an object can be created, an object variable must be declared in a standard module to store the reference to the object.

Dim emp As New CEmployee

The emp variable will represent a reference to an object of the CEmployee class. When you declare the object variable with the New keyword, VBA creates the object and allocates memory for it. However, the object isn’t instanced until you refer to it in your procedure code by assigning a value to its property or running one of its methods.

 
 Dim emp As CEmployee 
Set emp = New Cemployee

If you don’t use the New keyword with the Dim statement (as shown above), VBA does not allocate memory for your custom object until your procedure actually needs it.

（16）

 
 Dim emp As New CEmployee 
Dim CEmployee As New Collection

The first declaration statement declares the variable emp as a new instance of the CEmployee class. The second statement declares a custom collection. The CEmployee collection will be used to store all employees’ data. 貌似类名和变量名可以重。

（17）Custom classes recognize only two events: Initialize and Terminate. These events are triggered when an instance of the class is created and destroyed, respectively. Because the statements included inside the Initialize event are the first ones to be executed for the object, before any properties are set or any methods are executed, the Initialize event is a good place to perform initialization of the objects created from the class.

 
 Private Sub Class_Initialize() 
    [code to perform tasks as the object is created goes here] 
End Sub
 

The Terminate event occurs when all references to an object have been released. This is a good place to perform any necessary cleanup tasks.

 
 Private Sub Class_Terminate() 
    [cleanup code goes here] 
End Sub
 

To release an object variable from an object, use the following syntax:

Set objectVariable = Nothing

When you set the object variable to Nothing, the Terminate event is generated. Any code in this event is executed then. 其实是要所有对象指针都释放之后，才会触发terminate

（18）Activate the EmpOperations module that you created earlier. The top of the module should contain the following lines:

 
 Option Compare Database 
Option Explicit 
Dim emp As New CEmployee 
Dim CEmployee As New Collection
Sub AddEmployee(empLast As String, empFirst As String, _ 
    empSalary As Currency) 
    With emp 
        .Id = SetEmpId 
        .LastName = empLast 
        .FirstName = empFirst 
        .Salary = CCur(empSalary) 
        If .Salary = 0 Then Exit Sub 
        CEmployee.Add emp 
    End With 
End Sub
 

When Visual Basic reaches the With emp construct, a new instance of the CEmployee class is created. 模块级变量emp在生成新对象之后被加到集合之中，所以虽然每次调用AddEmployee方法都会使emp指向新的cempolyee对象，但是旧的emp对象并不会因为释放而丢失，因为在集合之中仍有指针指向它，因为它不会被释放。但我并不喜欢这种写法，它维护这样的随用随扔到模块变量并没啥实际意义。我更愿意把这个变量声明写到过程里，作为过程级的临时变量，因为它本来也是临时用途。

（19）

 
 Sub DeleteEmployee(colItem As Integer) 
    Dim getcount As Integer 
    CEmployee.Remove colItem 
End Sub
 

The class module procedures were called from the standard module named EmpOperations. This was done to avoid creating a new instance of a user-defined class every time we needed to call it.