What can a class do that can't be duplicated in a standard module?

Am I missing something somewhere?

In a word, yes.

You are looking at this from the wrong perspective, it isn't about what one can do that the other can't, it's about proper structure of applications. First though, a bit of background, a class isn't something in its own right, it is purely a text based template of an object; it is the definition of what an object can do and its properties. An object cannot exist until it has been created - by using the New keyword, this turns your class template into a living breathing object.

VBA is based on VB6 which isn't a particularly an object orientated language, it doesn't support the key features of object orientated programming namely Inheritance. As such it has the concept of modules and doesn't really force you to use objects (well it does, you just don't have to create them) - this actually makes it easy for self taught/novices to automate excel, they can make it do things without worrying about how it's actually doing it/what they are actually doing. This is why you don't see many class questions on Forums, there are very few examples in excel VBA where creating your own objects is actually required; in fact from the top of my head I can only think of one reasonably common task that cannot be achieved properly without defining a custom class.

Because of the way that people tend to learn VBA - through automating Excel, they learn how to do things in a specific way that makes classes/objects largely redundant from a "getting the job done perspective". The majority of users actually have edited classes and used them without realising it, the ThisWorkbook, UserForms and Sheets objects all expose classes that catch events raised by them and allow you to react to things happening in real time.

That brings me neatly on to what objects can do that modules cannot, they can raise and subscribe to events. When you add a control to a UserForm, Excel, behind the scenes automatically makes the UserForm listen for events that are raised by the controls it contains. So when you see something like:

Please Login or Register  to view this content.

What is actually happening is that the UserForm object is listening to events raised by the Combobox1 object. So when Combobox1 changes it broadcasts a message to all the objects that are subscribed to it, letting it know that it has changed.

These objects are all defined by classes, so lets make a simple example to demonstrate this:
Class1

Please Login or Register  to view this content.

So we've created a template for an object of type "Class1" - note that the name of the class is important, that will create an object with one subroutine and a public property - so everytime the changeMe sub runs, it raises an event and lets anyone know who's listening what it's changed to, lets wire this up in a userform:
UserForm1

Please Login or Register  to view this content.

So we've now created our own custom object that allows us to respond to changes within it and then used it in a UserForm. Which brings me on to the only common example I can think of in excel that requires classes.

Imagine that we want to add an unknown number of buttons to a UserForm when the code is running and we want to know when they are clicked, Excel cannot automatically subscribe the userform to the events because it doesn't know about the buttons before hand - just like our example above. The solution to this is knowing that not only can objects listen for events, but we can also create as many of them as we need. We can therefore create a class that can listen for the button click, then just create a new version of it for every button that we add.

So lets create our template for the object that will listen for the button clicks, just like in the userform example above, we use WithEvents to let our object know what to listen out for:
Class2

Please Login or Register  to view this content.

Then in our UserForm:

Please Login or Register  to view this content.

Pretty nifty, but it also allows us to extend the button, we can add more functionality to it, suppose we wanted an option to make the button red - we can just add it to our class:

Please Login or Register  to view this content.

Then we can change our original code to:

Please Login or Register  to view this content.

This is really useful for creating custom versions of controls that are re-usable and don't require you to clutter up your userform code. Now of course it would be perfectly possible to make the button red by creating a custom sub in a module, however that approach, I would argue, starts to spread your code around and makes it more difficult to follow - using a class means that all the custom methods for our custom control are held in a single logical place and encapsulated. You don't even need to be adding dynamically for this useful functionality either, if we wanted to add the makeRed functionality to an existing button we could simply do:

Please Login or Register  to view this content.

So again, whilst we could have created a function to make the button red and put it in a module, this method actually makes the code easier to come back to (you immediately know that the makeRed method is in Class2) and logically groups the custom button functionality together. You can take this even further, by creating a whole hierarchy of custom controls, have a look at a custom properties box I made here: http://www.excelforum.com/tips-and-t...ddly-bits.html.

I can create a class with a specific purpose and just expose public methods, all the faffing is done in the internals and I don't have to worry about it when consuming the object in normal code, this makes the parent code much easier to read and more fluid. It also allows much easier re factoring - all I have to keep the same are the method names and return types - since any application using the object doesn't know/care about the internals. It also allows proper testing, my moving application logic away from the user interface controls and into its own model you can write automated test scripts, for an idea about what I'm talking about here take a look at MVC or more relevantly MVVM design patterns.

Phew, that went on a bit longer than intended; hope some of it is useful anyway

From a convention viewpoint, I use far more classes than modules, they are self contained, allow me to raise events, property lets/gets are great for validation and let you consolidate validation rules, they allow you to keep variable scope far tighter than is possible in a module (module functions are private or public, class properties/methods are public/private only in the scope in which they are declared), but mostly they allow easier encapsulation of logic.