什么时候使用VBA中的类？ [英] When to use a Class in VBA?

问题描述

何时适合在Visual Basic应用程序（VBA）中使用类？

我假设加速开发和减少引入bug 是支持OOP的大多数语言的一个常见的好处。但是用VBA，有没有具体的标准？

解决方案

这取决于谁打算开发和维护代码。典型的高级用户宏编程者黑客小型特别应用程序可能会被使用类混淆。但是对于严重的开发，使用类的原因与其他语言相同。你有与VB6相同的限制 - 没有继承 - 但你可以通过使用接口有多态性。

好的使用类是代表实体和实体集合。例如，我经常看到VBA代码将Excel范围复制到二维数组中，然后用如下代码来操作二维数组：

  Total = 0 
 For i = 0 To NumRows-1 
 Total = Total +（OrderArray（i，1）* OrderArray（i，3））
 Next i 
 

将范围复制到具有适当命名属性的对象集合中更具可读性，例如：

  Total = 0 
对于colOrders中的每个objOrder 
 Total = Total + objOrder.Quantity * objOrder.Price $另一个例子是使用类来实现RAII设计模式（google for it）（b） ）。例如，我可能需要做的一件事是取消保护工作表，做一些操作，然后再保护它。使用类确保即使代码中出现错误，工作表也会再次受到保护：
  --- WorksheetProtector类模块--- 
 
私有m_obj工作表作为工作表
私有m_sPassword作为字符串
 
 Public Sub Unprotect（工作表作为工作表，密码为字符串）
'没有如果我们没有为工作表定义一个密码
如果Len（密码）= 0然后退出Sub 
 
'如果工作表已经不受保护，无需执行
如果不是Worksheet.ProtectContents然后退出子
 
'取消保护工作表
 Worksheet.Unprotect密码
 
'记住工作表和密码，以便我们可以再次保护
设置m_objWorksheet = Worksheet 
 m_sPassword = Password 
 End Sub 
 
 Public Sub Protect（）
'使用与取消保护相同的密码保护工作表
如果m_objWorksheet是Nothing然后退出Sub 
如果Len（m_sPassword）= 0然后退出
 
'如果工作表已经被保护，没有任何事情
如果m_objWorksheet.ProtectContents然后退出子
 
 m_objWorksheet.Protect m_sPassword 
设置m_objWorksheet =无
 m_sPassword =
结束子
 
私有子Class_Terminate b $ b'当此对象超出范围时重新保护工作表
在错误恢复下
保护
结束子
  
然后，您可以使用它来简化您的代码：
  Public Sub DoSomething ）
 Dim objWorksheetProtector as WorksheetProtector 
设置objWorksheetProtector =新的WorksheetProtector 
 objWorksheetProtector.Unprotect myWorks，myPassword 
 
 ...操作myWorksheet  - 可能会引发错误
 
 End Sub 
  
当此Sub退出时，objWorksheetProtector超出作用域，工作表再次保护。
 
When is it appropriate to use a class in Visual Basic for Applications (VBA)?

I'm assuming the accelerated development and reduction of introducing bugs is a common benefit for most languages that support OOP. But with VBA, is there a specific criterion? 
解决方案
It depends on who's going to develop and maintain the code.  Typical "Power User" macro writers hacking small ad-hoc apps may well be confused by using classes.  But for serious development, the reasons to use classes are the same as in other languages.  You have the same restrictions as VB6 - no inheritance - but you can have polymorphism by using interfaces.

A good use of classes is to represent entities, and collections of entities.  For example, I often see VBA code that copies an Excel range into a two-dimensional array, then manipulates the two dimensional array with code like:
Total = 0
For i = 0 To NumRows-1
    Total = Total + (OrderArray(i,1) * OrderArray(i,3))
Next i
It's more readable to copy the range into a collection of objects with appropriately-named properties, something like:
Total = 0
For Each objOrder in colOrders
    Total = Total + objOrder.Quantity * objOrder.Price
Next i
Another example is to use classes to implement the RAII design pattern (google for it).  For example, one thing I may need to do is to unprotect a worksheet, do some manipulations, then protect it again.  Using a class ensures that the worksheet will always be protected again even if an error occurs in your code:
--- WorksheetProtector class module ---

Private m_objWorksheet As Worksheet
Private m_sPassword As String

Public Sub Unprotect(Worksheet As Worksheet, Password As String)
    ' Nothing to do if we didn't define a password for the worksheet
    If Len(Password) = 0 Then Exit Sub

    ' If the worksheet is already unprotected, nothing to do
    If Not Worksheet.ProtectContents Then Exit Sub

    ' Unprotect the worksheet
    Worksheet.Unprotect Password

    ' Remember the worksheet and password so we can protect again
    Set m_objWorksheet = Worksheet
    m_sPassword = Password
End Sub

Public Sub Protect()
    ' Protects the worksheet with the same password used to unprotect it
    If m_objWorksheet Is Nothing Then Exit Sub
    If Len(m_sPassword) = 0 Then Exit Sub

    ' If the worksheet is already protected, nothing to do
    If m_objWorksheet.ProtectContents Then Exit Sub

    m_objWorksheet.Protect m_sPassword
    Set m_objWorksheet = Nothing
    m_sPassword = ""
End Sub

Private Sub Class_Terminate()
    ' Reprotect the worksheet when this object goes out of scope
    On Error Resume Next
    Protect
End Sub
You can then use this to simplify your code:
Public Sub DoSomething()
   Dim objWorksheetProtector as WorksheetProtector
   Set objWorksheetProtector = New WorksheetProtector
   objWorksheetProtector.Unprotect myWorksheet, myPassword

   ... manipulate myWorksheet - may raise an error

End Sub 
When this Sub exits, objWorksheetProtector goes out of scope, and the worksheet is protected again.

                        
这篇关于什么时候使用VBA中的类？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！