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

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


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 ExplicitPrivate pTaskID As LongPrivate pPersonName As StringPrivate pHoursWorked As DoublePublic Property Get TaskID() As Long    TaskID = pTaskIDEnd PropertyPublic Property Let TaskID(lTaskID As Long)    pTaskID = lTaskIDEnd PropertyPublic Property Get PersonName() As String    PersonName = pPersonNameEnd PropertyPublic Property Let PersonName(lPersonName As String)    pPersonName = lPersonNameEnd PropertyPublic Property Get HoursWorked() As Double    HoursWorked = pHoursWorkedEnd PropertyPublic Property Let HoursWorked(lHoursWorked As Double)    pHoursWorked = lHoursWorkedEnd 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 ExplicitPrivate pWorkLogItems As CollectionPublic Property Get WorkLogItems() As Collection    Set WorkLogItems = pWorkLogItemsEnd PropertyPublic Property Set WorkLogItems(lWorkLogItem As Collection)    Set pWorkLogItems = lWorkLogItemEnd PropertyFunction 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 wliExit_Here:    GetHoursWorked = doubleTotal        Exit FunctionHandle_Errors:        'You will probably want to catch the error that will '        'occur if WorkLogItems has not been set '        Resume Exit_HereEnd 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 ExplicitFunction 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 = clnWlisEnd FunctionSub 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 DoublePublic Property Get HoursOnBreak() As Double    HoursOnBreak = pHoursOnBreakEnd PropertyPublic Property Let HoursOnBreak(lHoursOnBreak As Double)    pHoursOnBreak = lHoursOnBreakEnd 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 breakEnd 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.


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


There is one other thing you could add to the advantages other contributors have stated (sorry if it's somewhere in Ben McCormack's excellent answer and I missed it). Classes can have their uses if your VBA script is likely to be re-programmed at some point.

For instance, I am designing a sort of order management system. It is to be used by several colleagues for quite a while, but it may need re-progamming if ordering rules change. I have therefore designed a basic stock item class, which gathers all the information about a stock item. The rules about how this data is analyzed for any order are, however, written in easily accessible and well commented sub routines. By doing this, I hope that future VBA programmers can easily change the mathematical rules by which orders are generated, without having to deal with how all the data is gathered about a particular stock item (this is all done by subroutines and functions within the class, which are activated when the class is handed a stock number). A Class' public properties are also picked up by intellisense, allowing the next programmer, as well as yourself, to have an easier time of it.

I guess the point is that classes can make life easier for later users in this way if they encode some basic set of information, or some conceptual object, that is always likely to be relevant to the context of the program's use.