DIfference between Module and Class Module ?

As you probably know a standard module can store procedures and functions which can be either Private or Public (the default) and can be accessed either from within that module only (Private) or from anywhere in the project (Public). Modules are also used to store variables, constants and declarations (i.e. API calls) that will need to be accessed from anywhere in the project.

Class modules allow you to create your own objects which can have their own properties and methods like any other object (range, worksheet, Excel, chart, blah, blah). The best way of describing is by use of a simple example. Say you wanted to have code that would allow you to create an invoice object, assign it a number, customer name, net amount, and VAT. You'd then want to save that invoice as a new workbook. Your code in a standard module might look like this:-

Sub SetUpInvoice()
'Demonstrates use of the Invoice class. We'll create a new invoice object and
'assign some dummy data to it. We'll then use the SaveInvoice method to
'create a workbook for the invoice as a permanent copy.

Dim invTestInvoice As New Invoice

invTestInvoice.Amount = 1000
invTestInvoice.VAT = 175
invTestInvoice.Name = "Marvins Milk"
invTestInvoice.InvoiceNumber = 122
invTestInvoice.SaveInvoice
Set invTestInvoice = Nothing
End Sub

Now, if you insert a class module and call it Invoice you'll find that if you type:

Dim AnyInv As

your new class will be recognised by Excel and will be listed in the combobox.

The code for the class module consists of properties for the invoice number, customer name, net amount and VAT. There is also one method (a Sub) which will create a new workbook, populate it with data, save it and close it. Here's the code:-

Option Explicit
'Declare private variables accessible only from within this class
Private m_Amount As Currency
Private m_Name As String
Private m_Number As Long
Private m_VAT As Currency

Public Property Get Amount() As Currency
Amount = m_Amount
End Property

Public Property Let Amount(ByVal cryNewValue As Currency)
If cryNewValue >= 0 Then m_Amount = cryNewValue
End Property

Public Property Get Name() As String
Name = m_Name
End Property

Public Property Let Name(ByVal sNewValue As String)
If sNewValue <> "" Then m_Name = sNewValue
End Property

Public Property Get InvoiceNumber() As Long
InvoiceNumber = m_Number
End Property

Public Property Let InvoiceNumber(lNewValue As Long)
If lNewValue > 0 Then m_Number = lNewValue
End Property

Public Property Get VAT() As Currency
VAT = m_VAT
End Property

Public Property Let VAT(cryNewValue As Currency)
If cryNewValue >= 0 Then m_VAT = cryNewValue
End Property

Private Sub Class_Initialize()
'Initialise variables once the class has been instantiated
m_Name = ""
m_Number = 0
m_VAT = 0
m_Amount = 0
End Sub

Public Sub SaveInvoice()
Dim wb As Workbook, sht As Worksheet, shts As Worksheet
Set wb = Workbooks.Add
Set sht = wb.Worksheets.Add
sht.Name = Me.InvoiceNumber

Application.DisplayAlerts = False
For Each shts In wb.Sheets
If shts.Name <> CStr(Me.InvoiceNumber) Then shts.Delete
Next
Application.DisplayAlerts = True

'Now add invoice data to the worksheet

With sht
.Cells(1, 1) = "Customer Name"
.Cells(1, 2) = Me.Name
.Cells(2, 1) = "Invoice number"
.Cells(2, 2) = Me.InvoiceNumber
.Cells(3, 1) = "Net amount"
.Cells(3, 2) = Me.Amount
.Cells(4, 1) = "VAT amount"
.Cells(4, 2) = Me.VAT
End With
wb.SaveAs "C:\temp\" & Me.InvoiceNumber
wb.Close

End Sub

All of this code has come from a demo I set up for an Excel training course I ran for my company last year. If you understand the principles of what I've shown here then you're doing well - it took me over a day to get some people to understand the concept.

Regards,
Dax.

Missed main point

I neglected to say this.

The whole point of creating classes is so that you can encapsulate your code in one place. All error handling, updating of data, etc can be handled 'behind the scenes'. They can easily be reused in other projects where you want duplicate functionality and a lot of the time the developer doesn't need to worry about how the object works, they only need to understand how to use it's methods and properties (unless they also created the class).

Laters,
Dax.

Re: Missed main point

Just to add to Daxs great reply.....

Class modules allow you to create and use your own object types in your application, as given by Daxs reply.
So when should you make one ??
When you need more functionality from the current
object. When you create an object you define an interface to that object via your object’s properties, methods, and collections.
These properties, methods and collections are all that the user of the object needs to know in order to use the object. It's then up to you to implement each feature in the object's source code using VBA class modules. Encapsulating the data and program components in a Class makes the management of the object easier and reusable. eg you may not be satisfied with excels application events and wish to add
another event....this is when you would use a class module........in short a Class is like a
User defined Function only more robust.

Have a look around at examples such as Daxs'

Ivan