0

BACKGROUND: I am trying to identify how many unique time periods I have from a list of dates that have. Elsewhere, I have seen a method which utilizes collections and error trapping (right term? I mean "On Error Resume Next" in any case) to build the collection with unique values. I have even used this structure successfully in other code that I have written, but in my current circumstance, I am getting an "Error 457: This key is already associated with an element of this collection." Thinking I was using the collection incorrectly, I opened up some older code I wrote 6 months ago (on a different computer for a different company) which uses the same structure and was known to WORK. This older code broke on the same identical error, which it previously did not do. Here is the sample of my work-in-progress code:

Dim rng as range
Dim TimePeriod as Collection

Set TimePeriod = New Collection

For Each rng In Range("I2:I6")
   On Error Resume Next
   TimePeriod.Add rng.Value, CStr(rng.Value)  'This is where the code breaks
   On Error GoTo 0
Next rng

QUESTION: I'm wondering if there is a setting or a reference library that I am somehow missing that is causing both pieces of code to break, or how to determine that, since both codes are functionally identical, and the previously tested satisfactory code breaks like my work-in-progress. I expected the "On Error Resume Next" to force the loop to pass over the error. Any suggestions?

--Update--

Sample data in range("I2:I6") as follows:

1/21/15
1/21/15
1/21/15
1/23/15
1/27/15
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
ScottyJ
  • 945
  • 11
  • 16
  • To improve your question and "help us to help you", I would suggest you to give us a sample data to put in Range("I2:I6") in order to replicate the error. – Matteo NNZ Jan 17 '15 at 00:50
  • @MatteoNNZ I should have included sample data. Sorry. Added it now. – ScottyJ Jan 17 '15 at 03:12
  • @MatteoNNZ one thing, though: I tested this on another computer and it worked. I think it is a setting rather than a code problem. I expect you won't get an error with the sample data. – ScottyJ Jan 17 '15 at 03:20
  • I think that the error is coming from the fact that your sample data has repeated dates. "Key is already associated with this collection", as in the thing that you are trying to add to the collection is already there. I tried your code on my computer, though, and it just skipped the repeated dates when adding to the collection – Kyle Tegt Jan 17 '15 at 09:05

2 Answers2

2

Your code works properly on my Excel 2007, although I would rewrite it to enclose the entire loop within the on error resume next for efficiency.

I suspect you are seeing the errors now because of a mis-set macro option error break.

Check Tools/Options that you have not selected to Break on All Errors

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This was it. I failed to understand that I should have been on "Break on Unhandled Errors." I'm good now. Thank you!!!!!! – ScottyJ Jan 19 '15 at 13:22
-1

Try getting rid of the On Error Goto 0 line. Take a look at this:

Difference between 'on error goto 0' and 'on error goto -1' -- VBA

It comes from Visual Basic 6, but works pretty much the same in VBA, it appears. Should work if you keep the On Error Resume Next line but eliminate the On Error Goto 0 line.

Community
  • 1
  • 1
Kyle Tegt
  • 103
  • 1
  • 10