I am experimentins with the Collection Object and want to create a new object
to hold a user defined type.
I want to store my data in the user defined type and keep track of the sets
of data using the collection. I have never used the Class module before, can
anyone point me in the right direction?
--
Tony Green

Hi Tony

Crashcourse:

Create a new class module. Name it "MyClass1" (without the quotes). Paste
this code into it:

'************ MyClass1 code ***********
Option Explicit

Public LID As Long
Public SName As String

Public Sub SpeakMe()
MsgBox "My name is " & SName & " and my ID is " & LID
End Sub
'*************** end MyClass1code ************

Insert a standard module. Paste this code itno it:

'************ Module 1 code: ************

Option Explicit

Public TheCol As Collection

Sub InitMe()
Dim i As Long
Dim NewClass As MyClass1
Set TheCol = Nothing
Set TheCol = New Collection

For i = 1 To 1000
Set NewClass = New MyClass1
NewClass.LID = i
NewClass.SName = Chr((i Mod 26) + 65) & _
Chr(Int(Rnd * 26) + 97)
TheCol.Add NewClass, "C" & NewClass.LID
Next

End Sub

Sub TestMe()
Dim i As Long
Dim ThisClass As MyClass1
i = Val(InputBox("ID to speak:"))
Select Case i
Case 1 To TheCol.Count
Set ThisClass = TheCol("C" & i)
Call ThisClass.SpeakMe
Set ThisClass = Nothing
Case Else
End Select
End Sub

'************ End Module 1 code: ************

Now runf initme and then several ibstances of TestMe. I believe you will
figure our what happens.

This has endless possibilities, so don't give up if it's a little confuring
the first time.

After a while, you may replace Public LID As Long with "property let" and
"property get" code, those are event macros running when you assign and read
the variables. But that is the next level...

HTH. Best wishes Harald

"ADG" skrev i melding
...
I am experimentins with the Collection Object and want to create a new
object
to hold a user defined type.
I want to store my data in the user defined type and keep track of the
sets
of data using the collection. I have never used the Class module before,
can
anyone point me in the right direction?
--
Tony Green

Ouch. Apologies for all typos, this went just a little too fast. And put

Set NewClass = Nothing
right after
TheCol.Add NewClass, "C" & NewClass.LID

Best wishes Harald

"Harald Staff" skrev i melding
...
Hi Tony

Crashcourse:

Create a new class module. Name it "MyClass1" (without the quotes). Paste
this code into it:

'************ MyClass1 code ***********
Option Explicit

Public LID As Long
Public SName As String

Public Sub SpeakMe()
MsgBox "My name is " & SName & " and my ID is " & LID
End Sub
'*************** end MyClass1code ************

Insert a standard module. Paste this code itno it:

'************ Module 1 code: ************

Option Explicit

Public TheCol As Collection

Sub InitMe()
Dim i As Long
Dim NewClass As MyClass1
Set TheCol = Nothing
Set TheCol = New Collection

For i = 1 To 1000
Set NewClass = New MyClass1
NewClass.LID = i
NewClass.SName = Chr((i Mod 26) + 65) & _
Chr(Int(Rnd * 26) + 97)
TheCol.Add NewClass, "C" & NewClass.LID
Next

End Sub

Sub TestMe()
Dim i As Long
Dim ThisClass As MyClass1
i = Val(InputBox("ID to speak:"))
Select Case i
Case 1 To TheCol.Count
Set ThisClass = TheCol("C" & i)
Call ThisClass.SpeakMe
Set ThisClass = Nothing
Case Else
End Select
End Sub

'************ End Module 1 code: ************

Now runf initme and then several ibstances of TestMe. I believe you will
figure our what happens.

This has endless possibilities, so don't give up if it's a little
confuring
the first time.

After a while, you may replace Public LID As Long with "property let" and
"property get" code, those are event macros running when you assign and
read
the variables. But that is the next level...

HTH. Best wishes Harald

"ADG" skrev i melding
...
I am experimentins with the Collection Object and want to create a new
object
to hold a user defined type.
I want to store my data in the user defined type and keep track of the
sets
of data using the collection. I have never used the Class module before,
can
anyone point me in the right direction?
--
Tony Green

Many thanks, example was perfect. I have adapted it into a particular
spreadsheet that I am using.

Another novice question, is it posible to step through the collection in Key
order, or is it only possible to use a loop to go from 1 to collection count?
--
Tony Green


"Harald Staff" wrote:

Ouch. Apologies for all typos, this went just a little too fast. And put

Set NewClass = Nothing
right after
TheCol.Add NewClass, "C" & NewClass.LID

Best wishes Harald

"Harald Staff" skrev i melding
...
Hi Tony

Crashcourse:

Create a new class module. Name it "MyClass1" (without the quotes). Paste
this code into it:

'************ MyClass1 code ***********
Option Explicit

Public LID As Long
Public SName As String

Public Sub SpeakMe()
MsgBox "My name is " & SName & " and my ID is " & LID
End Sub
'*************** end MyClass1code ************

Insert a standard module. Paste this code itno it:

'************ Module 1 code: ************

Option Explicit

Public TheCol As Collection

Sub InitMe()
Dim i As Long
Dim NewClass As MyClass1
Set TheCol = Nothing
Set TheCol = New Collection

For i = 1 To 1000
Set NewClass = New MyClass1
NewClass.LID = i
NewClass.SName = Chr((i Mod 26) + 65) & _
Chr(Int(Rnd * 26) + 97)
TheCol.Add NewClass, "C" & NewClass.LID
Next

End Sub

Sub TestMe()
Dim i As Long
Dim ThisClass As MyClass1
i = Val(InputBox("ID to speak:"))
Select Case i
Case 1 To TheCol.Count
Set ThisClass = TheCol("C" & i)
Call ThisClass.SpeakMe
Set ThisClass = Nothing
Case Else
End Select
End Sub

'************ End Module 1 code: ************

Now runf initme and then several ibstances of TestMe. I believe you will
figure our what happens.

This has endless possibilities, so don't give up if it's a little
confuring
the first time.

After a while, you may replace Public LID As Long with "property let" and
"property get" code, those are event macros running when you assign and
read
the variables. But that is the next level...

HTH. Best wishes Harald

"ADG" skrev i melding
...
I am experimentins with the Collection Object and want to create a new
object
to hold a user defined type.
I want to store my data in the user defined type and keep track of the
sets
of data using the collection. I have never used the Class module before,
can
anyone point me in the right direction?
--
Tony Green

Hi Tony

Glad it was useful.

As long as you know what's in the collection, you can do things like

For Each ThisClass in TheCol
Call ThisClass.SpeakMe
Next

The key is a string. Keys aren't necessarily ordered, they just have to be
unique.

Now don't ask. Experiment.

HTH. Best wishes Harald

"ADG" skrev i melding
...
Many thanks, example was perfect. I have adapted it into a particular
spreadsheet that I am using.

Another novice question, is it posible to step through the collection in
Key
order, or is it only possible to use a loop to go from 1 to collection
count?
--
Tony Green


"Harald Staff" wrote:

Ouch. Apologies for all typos, this went just a little too fast. And put

Set NewClass = Nothing
right after
TheCol.Add NewClass, "C" & NewClass.LID

Best wishes Harald

"Harald Staff" skrev i melding
...
Hi Tony

Crashcourse:

Create a new class module. Name it "MyClass1" (without the quotes).
Paste
this code into it:

'************ MyClass1 code ***********
Option Explicit

Public LID As Long
Public SName As String

Public Sub SpeakMe()
MsgBox "My name is " & SName & " and my ID is " & LID
End Sub
'*************** end MyClass1code ************

Insert a standard module. Paste this code itno it:

'************ Module 1 code: ************

Option Explicit

Public TheCol As Collection

Sub InitMe()
Dim i As Long
Dim NewClass As MyClass1
Set TheCol = Nothing
Set TheCol = New Collection

For i = 1 To 1000
Set NewClass = New MyClass1
NewClass.LID = i
NewClass.SName = Chr((i Mod 26) + 65) & _
Chr(Int(Rnd * 26) + 97)
TheCol.Add NewClass, "C" & NewClass.LID
Next

End Sub

Sub TestMe()
Dim i As Long
Dim ThisClass As MyClass1
i = Val(InputBox("ID to speak:"))
Select Case i
Case 1 To TheCol.Count
Set ThisClass = TheCol("C" & i)
Call ThisClass.SpeakMe
Set ThisClass = Nothing
Case Else
End Select
End Sub

'************ End Module 1 code: ************

Now runf initme and then several ibstances of TestMe. I believe you
will
figure our what happens.

This has endless possibilities, so don't give up if it's a little
confuring
the first time.

After a while, you may replace Public LID As Long with "property let"
and
"property get" code, those are event macros running when you assign
and
read
the variables. But that is the next level...

HTH. Best wishes Harald

"ADG" skrev i melding
...
I am experimentins with the Collection Object and want to create a
new
object
to hold a user defined type.
I want to store my data in the user defined type and keep track of
the
sets
of data using the collection. I have never used the Class module
before,
can
anyone point me in the right direction?
--
Tony Green