What are the benefits of using Classes in VBA? [closed]

Question

I am doing some VBA programming in Excel and have one workbook where all the datasheets are to be copied from into another sheet. The new sheet will have several header rows, and I would like to keep track of where they are situated so I don't have to find words in them constantly.

Is the simplest thing to use classes and keep them running while the Excel workbook is open? Or will this make it heavy and hard to handle, and I should keep working with subroutines? What are the benefits of using classes? It is not like I have several objects, only sheets and validation on columns.

Answer 1

The advantage of using classes instead of just subroutines is that classes create a level of abstraction that allow you to write cleaner code. Admittedly, if you've never used classes before in VBA, there is a learning curve, but I believe it's certainly worth the time to figure it out.

One key indication that you should switch to classes is if you're constantly adding parameters to your functions and subroutines. In this case, it's almost always best to use classes.

I've copied an explanation of classes from one of my previous Stack Overflow answers:

---

Here's a long example of how using a class might help you. Although this example is lengthy, it will show you how a few principles of object-oriented programming can really help you clean up your code.

In the VBA editor, go to Insert > Class Module. In the Properties window (bottom left of the screen by default), change the name of the module to WorkLogItem. Add the following code to the class:

Option Explicit  Private pTaskID As Long Private pPersonName As String Private pHoursWorked As Double  Public Property Get TaskID() As Long     TaskID = pTaskID End Property  Public Property Let TaskID(lTaskID As Long)     pTaskID = lTaskID End Property  Public Property Get PersonName() As String     PersonName = pPersonName End Property  Public Property Let PersonName(lPersonName As String)     pPersonName = lPersonName End Property  Public Property Get HoursWorked() As Double     HoursWorked = pHoursWorked End Property  Public Property Let HoursWorked(lHoursWorked As Double)     pHoursWorked = lHoursWorked End Property 

The above code will give us a strongly-typed object that's specific to the data with which we're working. When you use multi-dimension arrays to store your data, your code resembles this: arr(1,1) is the ID, arr(1,2) is the PersonName, and arr(1,3) is the HoursWorked. Using that syntax, it's hard to know what is what. Let's assume you still load your objects into an array, but instead use the WorkLogItem that we created above. This name, you would be able to do arr(1).PersonName to get the person's name. That makes your code much easier to read.

Let's keep moving with this example. Instead of storing the objects in array, we'll try using a collection.

Next, add a new class module and call it ProcessWorkLog. Put the following code in there:

Option Explicit  Private pWorkLogItems As Collection  Public Property Get WorkLogItems() As Collection     Set WorkLogItems = pWorkLogItems End Property  Public Property Set WorkLogItems(lWorkLogItem As Collection)     Set pWorkLogItems = lWorkLogItem End Property  Function GetHoursWorked(strPersonName As String) As Double     On Error GoTo Handle_Errors     Dim wli As WorkLogItem     Dim doubleTotal As Double     doubleTotal = 0     For Each wli In WorkLogItems         If strPersonName = wli.PersonName Then             doubleTotal = doubleTotal + wli.HoursWorked         End If     Next wli  Exit_Here:     GetHoursWorked = doubleTotal         Exit Function  Handle_Errors:         'You will probably want to catch the error that will '         'occur if WorkLogItems has not been set '         Resume Exit_Here   End Function 

The above class is going to be used to "do something" with a colleciton of WorkLogItem. Initially, we just set it up to count the total number of hours worked. Let's test the code we wrote. Create a new Module (not a class module this time; just a "regular" module). Paste the following code in the module:

Option Explicit  Function PopulateArray() As Collection     Dim clnWlis As Collection     Dim wli As WorkLogItem     'Put some data in the collection'     Set clnWlis = New Collection      Set wli = New WorkLogItem     wli.TaskID = 1     wli.PersonName = "Fred"     wli.HoursWorked = 4.5     clnWlis.Add wli      Set wli = New WorkLogItem     wli.TaskID = 2     wli.PersonName = "Sally"     wli.HoursWorked = 3     clnWlis.Add wli      Set wli = New WorkLogItem     wli.TaskID = 3     wli.PersonName = "Fred"     wli.HoursWorked = 2.5     clnWlis.Add wli      Set PopulateArray = clnWlis End Function  Sub TestGetHoursWorked()     Dim pwl As ProcessWorkLog     Dim arrWli() As WorkLogItem     Set pwl = New ProcessWorkLog     Set pwl.WorkLogItems = PopulateArray()     Debug.Print pwl.GetHoursWorked("Fred")  End Sub 

In the above code, PopulateArray() simply creates a collection of WorkLogItem. In your real code, you might create class to parse your Excel sheets or your data objects to fill a collection or an array.

The TestGetHoursWorked() code simply demonstrates how the classes were used. You notice that ProcessWorkLog is instantiated as an object. After it is instantiated, a collection of WorkLogItem becomes part of the pwl object. You notice this in the line Set pwl.WorkLogItems = PopulateArray(). Next, we simply call the function we wrote which acts upon the collection WorkLogItems.

Why is this helpful?

Let's suppose your data changes and you want to add a new method. Suppose your WorkLogItem now includes a field for HoursOnBreak and you want to add a new method to calculate that.

All you need to do is add a property to WorkLogItem like so:

Private pHoursOnBreak As Double  Public Property Get HoursOnBreak() As Double     HoursOnBreak = pHoursOnBreak End Property  Public Property Let HoursOnBreak(lHoursOnBreak As Double)     pHoursOnBreak = lHoursOnBreak End Property 

Of course, you'll need to change your method for populating your collection (the sample method I used was PopulateArray(), but you probably should have a separate class just for this). Then you just add your new method to your ProcessWorkLog class:

Function GetHoursOnBreak(strPersonName As String) As Double      'Code to get hours on break End Function 

Now, if we wanted to update our TestGetHoursWorked() method to return result of GetHoursOnBreak, all we would have to do as add the following line:

    Debug.Print pwl.GetHoursOnBreak("Fred") 

If you passed in an array of values that represented your data, you would have to find every place in your code where you used the arrays and then update it accordingly. If you use classes (and their instantiated objects) instead, you can much more easily update your code to work with changes. Also, when you allow the class to be consumed in multiple ways (perhaps one function needs only 4 of the objects properties while another function will need 6), they can still reference the same object. This keeps you from having multiple arrays for different types of functions.

For further reading, I would highly recommend getting a copy of VBA Developer's Handbook, 2nd edition. The book is full of great examples and best practices and tons of sample code. If you're investing a lot of time into VBA for a serious project, it's well worth your time to look into this book.

Answer 2

If there are lots of subroutines or subroutines are very long then structuring the code in to classes may help. If there are only a couple of subroutines, say, each being only 10 lines of code each then this is over kill. The benefit of structuring the code in to classes is that it is easier to read and change when you come back to it down the line. So another reason to structuring the code into classes is if the code is likely to need changing down the line