how to use Class Modules

[lucrahouse]

I'm trying something new. I want to learn how to use class modules, because I think it will take less time to process. Previously, I would read the data into table arrays and spit out the results in a results table. Here is an example of what I'm trying to accomplish.
Here is my Data.[tt]
1 AIG 500
2 DELL -500
3 INTC 300
1 AIG 600
2 INTC 500
3 DELL 610
1 DELL -700
2 INTC 830
3 AIG -900
1 SUNW 100
2 AIG 0
3 AIG 1270
1 DELL -1000
2 DELL 1490
3 DELL -1000
1 INTC -300
2 DELL 1820
3 INTC 1930[/tt]
The first column is the account number, the 2nd column is a symbol and the third column is the number of shares.
My goal is review each account and each symbol and record the greatest accumulated shares for each account and symbol. But first I have to be able to learn how to record the information.

I wrote this in a class module called [red]Accounts[/red]
Option Explicit

Dim acctNbr As Integer
Dim acctName As String
Dim stkSym As String
Dim stkShares As Integer

Property Let AccountNbr(y As Integer)
acctNbr = y
End Property

Property Get AccountNbr() As Integer
AccountNbr = acctNbr
End Property

Property Let AccountName(y As String)
acctName = y
End Property

Property Get AccountName() As String
AccountName = acctName
End Property

Property Let Symbol(y As String)
stkSym = y
End Property

Property Get Symbol() As String
Symbol = stkSym
End Property

Property Let Shares(y As Integer)
stkShares = y
End Property

Property Get Shares() As Integer
Shares = stkShares
End Property

Then I wrote this module to test if I was recording the information properly. But I failed. Maybe you could point out where I went wrong.

Sub StockSymbolAdd()

Dim Account As Collection
Dim objAccount As Account

Dim MyCurrentSymbol As String
Dim aa As Integer
Dim bb As Integer

Set Account = New Collection

Range("A1").Select

For aa = 1 To ActiveCell.CurrentRegion.Rows.Count

MyCurrentAcct = Cells(aa, 1).Value
MyCurrentSymbol = Cells(aa, 2).Value
MyCurrentShares = Cells(aa, 3).Value

On Error Resume Next
Set objAccount = New Account
objAccount.AccountNbr = MyCurrentAcct
objAccount.Symbol = MyCurrentSymbol
objAccount.Shares = MyCurrentShares

Account.Add objAccount, objAccount.AccountNbr
Account.Add objAccount, objAccount.Symbol
Account.Add objAccount, objAccount.Shares

If Err > 0 Then Err = 0

Account.Symbol(MyCurrentSymbol).Shares = AccountPosition.Symbol(MyCurrentSymbol).Shares + Cells(aa, 3).Value
Debug.Print Account.Symbol(MyCurrentSymbol) & ":" & Account.Symbol(MyCurrentSymbol).Shares & " ;" & aa

Next aa

Debug.Print " -------------- "

For bb = 1 To Account.Count
Debug.Print Account.Item(bb).Number
Debug.Print Account.Symbol(bb) & ":" & Account.Shares(bb) & " ;" & bb
Next bb

End Sub

Your input would be appreciated. Thanks

 

[DaveInIowa]

Just a quick observation. You are naming your class instance and your collection the same (Account). Normally you would name your collection as a plural of the object the collection is holding.

 

[lucrahouse]

You are correct. I have several errors in my code that I need to clean up. Sorry for the bad code. As you can see I'm not very good at this. Thank you for pointing this out.

 

[lucrahouse]

I found one major flaw in my thinking. I just read on page 293 in "EXCEL 2000 VBA" by Bullen, Green, Bovey, & Rosenberg that "if you supply a key value for each memeber of the collection, the keys must be unique. You will get a run-time error when you attempt to add a new memeber to the collection with a key value that is already in use. Using a person's name as the key is not recommended as people can have the same name. Use a unique identifier, such as a social scurity number." I can see that this is where my idea is flawed. I didn't have a unique identifier. I think I could create a unique identifier by joining the account number and the symbol.

Or is it possible to have one collection within in another?
I wonder if I can create collection of symbols within a collection of accounts?

 

[PHV]

You may also consider a Scripting.Dictionary object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Yeah PH!

Gerry