When to use a Class in VBA?

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 = 0For 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 = 0For Each objOrder in colOrders    Total = Total + objOrder.Quantity * objOrder.PriceNext 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 WorksheetPrivate m_sPassword As StringPublic 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 = PasswordEnd SubPublic 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 SubPrivate Sub Class_Terminate()    ' Reprotect the worksheet when this object goes out of scope    On Error Resume Next    ProtectEnd 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 errorEnd Sub 

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

I think the criteria is the same as other languages

If you need to tie together several pieces of data and some methods and also specifically handle what happens when the object is created/terminated, classes are ideal

say if you have a few procedures which fire when you open a form and one of them is taking a long time, you might decide you want to time each stage......

You could create a stopwatch class with methods for the obvious functions for starting and stopping, you could then add a function to retrieve the time so far and report it in a text file, using an argument representing the name of the process being timed. You could write logic to log only the slowest performances for investigation.

You could then add a progress bar object with methods to open and close it and to display the names of the current action, along with times in ms and probable time remaining based on previous stored reports etc

Another example might be if you dont like Access's user group rubbish, you can create your own User class with methods for loging in and out and features for group-level user access control/auditing/logging certain actions/tracking errors etc

Of course you could do this using a set of unrelated methods and lots of variable passing, but to have it all encapsulated in a class just seems better to me.

You do sooner or later come near to the limits of VBA, but its quite a powerful language and if your company ties you to it you can actually get some good, complex solutions out of it.

Classes are extremely useful when dealing with the more complex API functions, and particularly when they require a data structure.

For example, the GetOpenFileName() and GetSaveFileName() functions take an OPENFILENAME stucture with many members. you might not need to take advantage of all of them but they are there and should be initialized.

I like to wrap the structure (UDT) and the API function declarations into a CfileDialog class. The Class_Initialize event sets up the default values of the structure's members, so that when I use the class, I only need to set the members I want to change (through Property procedures). Flag constants are implemented as an Enum. So, for example, to choose a spreadsheet to open, my code might look like this:

Dim strFileName As StringDim dlgXLS As New CFileDialogWith dlgXLS  .Title = "Choose a Spreadsheet"  .Filter = "Excel (*.xls)|*.xls|All Files (*.*)|*.*"  .Flags = ofnFileMustExist OR ofnExplorer  If OpenFileDialog() Then    strFileName = .FileName  End IfEnd WithSet dlgXLS = Nothing

The class sets the default directory to My Documents, though if I wanted to I could change it with the InitDir property.

This is just one example of how a class can be hugely beneficial in a VBA application.