Difficult to find short examples, but I'll include the code of a "sorted
list" class  module at the end of this.

The main benefit of using a class module  is that you can then create
multiple objects  that are instances of that class. For instance, I might
have several sorted lists active simultaneously - all independent and doing
their own thing. This couldn't be done with normal modules, because they
exist immediately (and only one instance exists at a time).

> I know you can have custom  made objects and methods  in Class module, this
> i have gone through 4 years back.

This is a bit misleading. You can have custom made  methods in modules too.
You don't have custom made objects IN class modules - they ARE the custom
made objects (or, at least, they become objects when instanced with a
"new").

> To be honest i have not used it till date.

And don't expect to very often. I'd write ten times mode modules than class
modules. Don't start with the intent of using them - let them "fall out" of
your design. In addition, there are always other ways of doing things, so
you tend to need an object-oriented view of programming before you start
seeing obvious uses for class modules.

I'll include the 120 lines of code for SortedList below my sig. If you want
it, feel free to use it. However, I did write it for a specific system
(where I had about a dozen instances of it for various purposes), and it
might have a few idiosyncrasies because of that.

An example  of its use from a client collection module that maintained all
the clients in a sorted order. I've annotated the individual statements and
also put in pseudo code for non-list statements - these annotations are in
square brackets.

Private mClients As New SortedList

Public Sub LoadDatabaseInformation
mClients.Reset [Empties the list before loading new
info from the database]
[Read database records and loop through them]
Call mClients.Add(Client, CreditApprovalID, ClientID, "") [Add this
client to the list]
[Note that the first sort key's value is from ClientID; the second sort
key's value is empty; and that the direct record lookup value is given as
the client approval id.]
[End of that loop]

[Later ...]
Call mClients.Find(CreditApprovalID).AddInvestment([stuff]) [Find a
client and attach an investment item to it]

[Later ...]
For I = 0 To mClients.Count - 1
Row = Row + 1
Call mClients.Item(I).PopulateTRRow(Sheet, HeadingRows + Row)
Next I
[Works through the clients - in their sorted order - and inserts each in a
separate row of an extract spreadsheet.]

... and many other uses of the client list.

By keeping the clients in this sorted list, I packaged away the problem of
keeping them in a particular order - while still allowing me to find
individual records when I needed them. And I also was able to use exactly
the same sorted list code for everything else in the system that needed to
be handled in the same way.

Notice also that there is a thing called a Client (e.g. the first parameter
of the Add statement). A Client was quite a complex item - holding a fair
amount of information and also able to do lots of things - and I needed lots
of them. Again, I used a class module as a client. This meant that all the
attributes of a client and all the things it could do were held neatly in a
single class module, rather than in lots of separate arrays (e.g. array of
client names, array of client addresses, etc). A slightly more
object-oriented concept that you'll possibly need to study OO concepts
somewhat to become comfortable with.

Next time you find yourself creating several arrays with the same bounds and
holding separate stuff for the same entity, give a thought to making that
entity a class module. It'll probably give you a neater solution.


Class module SortedList:

Option Explicit

Private Type ListItem
Item As Variant
Lookup As Variant
SortKey1 As Variant
SortKey2 As Variant
End Type

Private mCount As Integer
Private mItems() As ListItem

Private Sub Class_Initialize()
' nothing
End Sub

Private Sub Class_Terminate()
Call Reset
End Sub

Public Sub Reset()
mCount = 0
ReDim mItems(0)
Set mItems(0).Item = Nothing
End Sub

Public Sub Delete(ByVal Index As Integer)
Dim I As Integer
For I = Index + 1 To mCount - 1
mItems(I - 1) = mItems(I)
Next I
mCount = mCount - 1
If mCount > 0 Then
ReDim Preserve mItems(mCount - 1)
Else
Set mItems(0).Item = Nothing
End If
End Sub

Public Function Count() As Integer
Count = mCount
End Function

Public Function Item(ByVal Index As Integer) As Variant
Set Item = mItems(Index).Item
End Function

Public Function First() As Variant
Set First = mItems(0).Item
End Function

Public Function Last() As Variant
Set Last = mItems(mCount - 1).Item
End Function

Public Function Index(ByRef Lookup As Variant) As Integer
Dim I As Integer
For I = 0 To mCount - 1
If mItems(I).Lookup = Lookup Then
Index = I
Exit Function
End If
Next I
Index = -1
End Function

Public Function Find(ByRef Lookup As Variant) As Variant
Dim Position As Integer
Position = Index(Lookup)
If Position >= 0 Then
Set Find = mItems(Position).Item
Exit Function
End If
Set Find = Nothing
End Function

Public Sub Add(ByRef Item As Variant, ByVal Lookup As Variant, ByVal
SortKey1 As Variant, ByVal SortKey2 As Variant)
ReDim Preserve mItems(mCount)
Dim InsertPosition As Integer
InsertPosition = mCount

Do While InsertPosition >= 1
With mItems(InsertPosition - 1)
If (SortKey1 < .SortKey1) Or _
((SortKey1 = .SortKey1) And (SortKey2 < .SortKey2)) Then
mItems(InsertPosition) = mItems(InsertPosition - 1)
Else
Exit Do
End If
End With
InsertPosition = InsertPosition - 1
Loop

With mItems(InsertPosition)
Set .Item = Item
.Lookup = Lookup
.SortKey1 = SortKey1
.SortKey2 = SortKey2
End With
mCount = mCount + 1
End Sub

Public Sub ReplaceSortKey1(ByVal Index As Integer, ByRef NewKey As Variant)
mItems(Index).SortKey1 = NewKey
End Sub

Public Sub ResortList()
ReDim Items(mCount - 1) As ListItem
Dim I As Integer
For I = 0 To UBound(Items)
Items(I) = mItems(I)
Next I
Call Reset
For I = 0 To UBound(Items)
With Items(I)
Call Add(.Item, .Lookup, .SortKey1, .SortKey2)
End With
Next I
End Sub