【ExcelVBA 】类模块学习从入门到放弃

📚 VBA 类模块全面学习指南

类模块是 VBA 中实现**面向对象编程（OOP）**的核心工具，允许创建自定义对象，封装数据和行为。以下从基础到高级的系统化教程，助你掌握类模块的核心用法。

一、类模块基础

1. 创建类模块

1. 插入类模块

   • VBE（Visual Basic Editor）中：插入 → 类模块（默认名如 Class1）。
   • 重命名类模块：在 属性窗口 修改 (名称)（如 clsCustomer）。

2. 定义属性与方法

   ' clsCustomer 类模块代码
   Private pName As String
   Private pAge As Integer
   
   ' 属性定义（Property Let/Get）
   Public Property Let Name(Value As String)
       pName = Value
   End Property
   Public Property Get Name() As String
       Name = pName
   End Property
   
   Public Property Let Age(Value As Integer)
       If Value >= 0 Then pAge = Value
   End Property
   Public Property Get Age() As Integer
       Age = pAge
   End Property
   
   ' 方法
   Public Sub DisplayInfo()
       MsgBox "姓名：" & pName & vbNewLine & "年龄：" & pAge
   End Sub
   

二、实例化与使用类对象

1. 创建对象

' 在标准模块中使用
Sub DemoClass()
    Dim customer As clsCustomer
    Set customer = New clsCustomer  ' 必须使用 New 和 Set
    
    customer.Name = "张三"  ' 设置属性
    customer.Age = 30
    customer.DisplayInfo    ' 调用方法
End Sub

2. 销毁对象

• VBA 自动处理对象释放，也可手动释放：

  Set customer = Nothing
  

三、类模块高级功能

1. 集合类（封装多个对象）

1. 定义集合类（clsCustomers）

   Private colCustomers As Collection
   
   Private Sub Class_Initialize()
       Set colCustomers = New Collection
   End Sub
   
   Public Sub Add(customer As clsCustomer)
       colCustomers.Add customer
   End Sub
   
   Public Function Item(Index As Variant) As clsCustomer
       Set Item = colCustomers(Index)
   End Function
   
   Public Function Count() As Long
       Count = colCustomers.Count
   End Function
   

2. 使用集合类

   Sub ManageCustomers()
       Dim customers As clsCustomers
       Set customers = New clsCustomers
       
       Dim cust As clsCustomer
       Set cust = New clsCustomer
       cust.Name = "李四"
       customers.Add cust
       
       MsgBox "客户数量：" & customers.Count
   End Sub
   

2. 事件（Event）

1. 在类模块中声明事件

   ' clsCustomer 类模块
   Public Event DataSaved(Success As Boolean)
   
   Public Sub SaveToDB()
       On Error Resume Next
       ' 模拟保存操作
       If Err.Number = 0 Then
           RaiseEvent DataSaved(True)
       Else
           RaiseEvent DataSaved(False)
       End If
   End Sub
   

2. 响应事件（标准模块）

   Dim WithEvents cust As clsCustomer
   
   Sub TestEvent()
       Set cust = New clsCustomer
       cust.SaveToDB
   End Sub
   
   Private Sub cust_DataSaved(Success As Boolean)
       MsgBox IIf(Success, "保存成功！", "保存失败！")
   End Sub
   

四、实战案例：员工管理系统

1. 定义类模块

• 员工类（clsEmployee）

  Private pID As String
  Private pSalary As Double
  
  Public Property Let ID(Value As String)
      pID = Value
  End Property
  Public Property Get ID() As String
      ID = pID
  End Property
  
  Public Property Let Salary(Value As Double)
      If Value >= 0 Then pSalary = Value
  End Property
  Public Property Get Salary() As Double
      Salary = pSalary
  End Property
  

• 部门类（clsDepartment）

  Private colEmployees As Collection
  
  Private Sub Class_Initialize()
      Set colEmployees = New Collection
  End Sub
  
  Public Sub AddEmployee(emp As clsEmployee)
      colEmployees.Add emp
  End Sub
  
  Public Function TotalSalary() As Double
      Dim emp As clsEmployee, sum As Double
      For Each emp In colEmployees
          sum = sum + emp.Salary
      Next
      TotalSalary = sum
  End Function
  

2. 使用案例

Sub ManageEmployees()
    Dim hrDept As clsDepartment
    Set hrDept = New clsDepartment
    
    Dim emp1 As clsEmployee
    Set emp1 = New clsEmployee
    emp1.ID = "E001"
    emp1.Salary = 5000
    hrDept.AddEmployee emp1
    
    MsgBox "部门总工资：" & hrDept.TotalSalary
End Sub

五、注意事项与最佳实践

通过类模块，你可以将复杂逻辑封装成独立对象，显著提升代码的可维护性和复用性。建议在实际项目中逐步应用这些技巧，加深理解！