When to use a Class in VBA?

by

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.

More Related Contents:

  1. Pass arguments to Constructor in VBA
  2. Is it bad practice for a class to have only static fields and methods?
  3. VBA: how to test for object equality (whether two variables reference the same object)
  4. Program * does not contain a static ‘Main’ method suitable for an entry point
  5. What is the difference between old style and new style classes in Python?
  6. Inheritance vs. Aggregation [closed]
  7. Can I extend a class using more than 1 class in PHP?
  8. What techniques can be used to define a class in JavaScript, and what are their trade-offs?
  9. Static vs non-static class members
  10. C++ virtual function from constructor [duplicate]
  11. HAS-A, IS-A terminology in object oriented language
  12. What are public, private and protected in object oriented programming?
  13. What is the rationale behind having companion objects in Scala?
  14. What is the dependency inversion principle and why is it important?
  15. No ways to have class-based objects in javascript?
  16. declare property as object?
  17. Do write-only properties have practical applications?
  18. DDD – the rule that Entities can’t access Repositories directly
  19. What is the difference between a concrete class and an abstract class?
  20. Are Polymorphism , Overloading and Overriding similar concepts? [closed]
  21. If-less programming (basically without conditionals) [closed]
  22. When to implement an interface and when to extend a superclass?
  23. Mixin vs inheritance
  24. Does the Single Responsibility Principle work in OOP?
  25. What does ‘low in coupling and high in cohesion’ mean
  26. Learning/Implementing Design Patterns (For Newbies) [closed]
  27. Get all defined classes of a parent class in php
  28. How do you change the value of one attribute by changing the value of another? (dependent attributes)
  29. How to implement custom iterable class in VBA
  30. Cannot use concatenation when declaring default class properties in PHP?

Leave a Comment