什么时候使用VBA中的类？

本文介绍了什么时候使用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.