maximum number of members in a collection

[Guest]

Hi

I tried to create an Excell2003 VB collection with say 1500-2000 members.
But after 256 members are added, the loop continues WITHOUT ANY ERROR MESSAGE
but no more members are added to the collection

Is it possible that 256 is the limit?

I couldn't find it written down anywhere, but that limit is just too low.

I was just wondering if any of you knows more about this?

 

[RB Smissaert]

Didn't you have a Byte variable anywhere causing the trouble?
Post the code.

RBS

 

[Guest]

This is just a part of my original code:

Dim myCollection As New Collection
Dim member As clsMembers

Public i As Long


For i = 1 To 3000
Set member = Range(Cells(i, 1), Cells(i, 14))
myCollections.Add member
next

I am trying to create a collection of row within a range. I tried the same
mechanism with simple elements like numbers etc. but I never got past 256

 

[Tom Ogilvy]

Sub abc()
Dim myCollection As Collection
Dim member As Range

Dim i As Long

Set myCollection = New Collection

For i = 1 To 3000
Set member = Range(Cells(i, 1), Cells(i, 14))
myCollection.Add member
Next
Debug.Print myCollection.Count
Debug.Print myCollection.Item(myCollection.Count).Address
End Sub


Returned:

3000
$A$3000:$N$3000

for me.

 

[Guest]

Your problem appears to originate from clsMembers. This worked for me:

Public i As Long
Sub x()
Dim myCollection As Collection
Dim member As Range 'clsMembers
Set myCollection = New Collection
For i = 1 To 3000
Set member = Range(Cells(i, 1), Cells(i, 14))
myCollection.Add member
Next
'Now test collection
For i = 500 To 3000 Step 500
MsgBox myCollection(i).Address
Next
End Sub

Regards,
Greg

 

[RB Smissaert]

What is the code of clsMembers?

RBS

This is just a part of my original code:

Dim myCollection As New Collection
Dim member As clsMembers

Public i As Long


For i = 1 To 3000
Set member = Range(Cells(i, 1), Cells(i, 14))
myCollections.Add member
next

I am trying to create a collection of row within a range. I tried the same
mechanism with simple elements like numbers etc. but I never got past 256

 

[Guest]

the clsMembers looks like this:

Option Explicit

Public Artist As String
Public Album As String
Public Name As String
Public PlayCount As Integer
Public Genre As String
Public Size As Long
Public Time As Long
Public Year As Integer
Public TrackNumber As Integer
Public BitRate As Integer
Public LastPlayed As String
Public DateAdded As String
Public Rating As Integer
Public CheckSum As Single

Thanks a lot

 

[Guest]

Well

It works.

The thing is that I was looking at the collection in watch window and there
it lists only 256 members. However the whole collection is there and the
members can be accessed.

My mistake, I shouldn't have trusted the Watches :-|

Thanks to all of you

 

[Peter T]

Just curiosity, why do you need a collection vs simply

Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(3000, 14))
Debug.Print rng.Rows(rng.Rows.Count).Address

Regards,
Peter T