automation error 440 when adding to collection

[Ken]

I have a collection built from data in a range, and want to augment
that collection with data from another range. I also want to keep
track of the the new data by putting it in a listbox. The following
code works as far as augmenting the list

Set R = Range("Table14")

we = Range("Week_ending").Value
Count = EmpList.Count

For i = 1 To R.Rows.Count

If R.Cells(i, 1).Value = we - 7 Then
Set E = New Employee
E.Badge = R.Cells(i, 3)
E.LName = R.Cells(i, 4)
E.FName = R.Cells(i, 5)
On Error Resume Next
Call EmpList.Add(E, CStr(R.Cells(i, 3)))
ListBox3.AddItem CStr(E.Badge) 'this should
only happen when E is actually added to EmpList
ListBox3.List(ListBox3.ListCount - 1, 1) = E.LName
50
End If

Next i

Since I don't want to add the employees to listbox3 if they are
already in the collection, I thought I should be able to make the
following simple change to the On Error Resume Next line

Set R = Range("Table14")

we = Range("Week_ending").Value
Count = EmpList.Count

For i = 1 To R.Rows.Count

If R.Cells(i, 1).Value = we - 7 Then
Set E = New Employee
E.Badge = R.Cells(i, 3)
E.LName = R.Cells(i, 4)
E.FName = R.Cells(i, 5)
On Error GoTo 50
Call EmpList.Add(E, CStr(R.Cells(i, 3)))
ListBox3.AddItem CStr(E.Badge)
ListBox3.List(ListBox3.ListCount - 1, 1) = E.LName
50
End If

Next i

This causes an "Automation Error" 440 which I can't seem avoid. I
have tried clearing the error and moving the On Error statement
around, but, the second time through the If-End If loop it always
crashes.

Is there a fix to this, or another way to keep track of the names that
get added?

Thanks

Ken

 

[GS]

Try...


Set R = Range("Table14")

we = Range("Week_ending").Value
Count = EmpList.Count

For i = 1 To R.Rows.Count

If R.Cells(i, 1).Value = we - 7 Then
Set E = New Employee
E.Badge = R.Cells(i, 3)
E.LName = R.Cells(i, 4)
E.FName = R.Cells(i, 5)
On Error Resume Next
Call EmpList.Add(E, CStr(R.Cells(i, 3)))
'this should only happen when E is actually added to EmpList
If Err = 0 Then
ListBox3.AddItem CStr(E.Badge)
ListBox3.List(ListBox3.ListCount - 1, 1) = E.LName
Else
Err.Clear 'reset for next iteration
End If
End If
Next i

 

[Ken]

Garry

I tried that and had the same problem. The first time through it
works fine, identifies the err, skips a couple line, and goes through
again. When I debug.print the error, after resetting, is shows the
error is 0 the first time, the error is 0 the second time, then it has
the key alreadt used error like it should, but then still gives the
404 error rather than just skipping to line 50 as it did the first
time. I don't know what is different the second time through. I came
up with a work around that works quite well, but, I wish I understood
what was going as the following solution might not always work.

Set R = Range("Table14")

we = Range("Week_ending").Value
Count = EmpList.Count

For i = 1 To R.Rows.Count

If R.Cells(i, 1).Value = we - 7 Then
Set E = New Employee
E.Badge = R.Cells(i, 3)
E.LName = R.Cells(i, 4)
E.FName = R.Cells(i, 5)
On Error Resume Next
Call EmpList.Add(E, CStr(R.Cells(i, 3)))
' ListBox3.AddItem CStr(E.Badge) 'i gave
up on this
' ListBox3.List(ListBox3.ListCount - 1, 1) = E.LName
'and this
End If

Next i

For i = Count + 1 To EmpList.Count
ListBox3.AddItem EmpList(i).Badge
ListBox3.List(ListBox3.ListCount - 1, 1) = EmpList(i).LName
Next i


Thanks for your time on this, and your many other contributions to
this group.

Ken

 

[GS]

Can you show the code for EmpList.Add()?

Also, why are you resetting E each time? Seems to me that this might be
causing some conflict since I don't see the connection between Employee
and EmpList.

 

[Ken]

Garry
EmpList is a collection of Employees. Employees is a collection
defined in my Class module as something with a few properties
including badge number, first and last name, and a bunch of man hour
fields. E is short lived employee that comes from a chunk of data and
is added to the collection if and only if it is not already a member
(badge number exists) of the collection. I use this technique a lot
to simply get a unique list of something. It works great, resuming
next and generating a unique collection; until I try to skip a couple
other steps like putting the skipped name in a listbox. There is no
code, or at least no code that I am aware of, to the EmpList.Add. It
is the method for adding to a collection that exists simply by virtue
of EmpList being a collection.
Ken

 

[GS]

Ken expressed precisely :

Garry
EmpList is a collection of Employees. Employees is a collection
defined in my Class module as something with a few properties
including badge number, first and last name, and a bunch of man hour
fields. E is short lived employee that comes from a chunk of data and
is added to the collection if and only if it is not already a member
(badge number exists) of the collection. I use this technique a lot
to simply get a unique list of something. It works great, resuming
next and generating a unique collection; until I try to skip a couple
other steps like putting the skipped name in a listbox. There is no
code, or at least no code that I am aware of, to the EmpList.Add. It
is the method for adding to a collection that exists simply by virtue
of EmpList being a collection.
Ken

Ken,
Thanks for the details. This is what I expected and so was trying to
figure out if the problem lies in executing the Add method, OR if it
has to do with the ListBox. I can only guess as to where the ListBox is
(in a userform or worksheet) and that makes it hard to understand
exactly what's going on.

I reproduced the Employees collection and was able to add new members
as you were doing. I repeated a member that already existed and it
worked as expected (ie: didn't add the member to the ListBox. I don't
seem to be able to reproduce the problem using a ListBox on a userform.
Haven't tried using one on a worksheet, though.

 

[Ken]

Garry
My listbox is on a userform; specifically in a frame on a multipage on
a userform, to throw in a couple more potential, but unlikely,
culprits. I am using Excel 2007.
Ken

 

[GS]

Ken explained on 7/9/2011 :

Garry
My listbox is on a userform; specifically in a frame on a multipage on
a userform, to throw in a couple more potential, but unlikely,
culprits. I am using Excel 2007.
Ken

Ok, Ken. Thanks! Unfortunatly, I hit the Close button without saving
and so will have to restart from scratch...! <ugh!>

 

[GS]

Ken,
I'm still unable to reproduce your error. I'm wondering how you're
managing the collection and its class instances. (I've been reviewing
this topic in both Excel VBA Programmer's Reference (Ch6) and Pro Excel
Development (Ch7) just to make sure I wasn't missing something)

My thoughts are that you might be having a problem with class
instanciation, so I recommend reviewing either publication if you have
them. I suspect you have the former since your code and class scenario
closely resembles it, but the latter has more detailed info about
managing collections using a class.

Sorry I can't be of more help...