Excel keeps crashing

[tonydev314]

I'm busy creating a VBA application in excel. A couple of weeks ago, I made some changes to the code and tried a function and Excel crashed. Nothing was lost in the crash, the recovered file had my latest changes, so I ran it again. Same result. Stepping through the code it got to one point which seemed rather innocuous and it crashed.
I resolved it by going back to the previous version of the file, and copying and pasting in all my code changes from the latest (crashing file).

Now 2 weeks later I have the same issue again - this time it all works perfectly when I step through the code- it just crashes when I'm not debugging.
By putting in place various breakpoints and seeing how far the code gets from the start, I've determined that it's this Sub that causes the crash.

Private Sub ShowChests()
    Dim index As Integer
    index = 1
    For Each c In pChests
        Dim im As image
        Set im = Me.Controls("chest" + CStr(index))
        im.Picture = LoadPicture(GetImageForChestType(c.Kind))
        im.BorderStyle = fmBorderStyleNone
        
        Me.Controls("Frame" + CStr(index)).BackColor = GetBackColorByStatus(c.Status)
        index = index + 1
    Next
End Sub

It works perfectly if I put a breakpoint on the ForEach statement, even if I only stop briefly, i.e Immediately hit F5
pChests is a custom collection of a custom object and set as a private variable.

Can anyone shed any light on why this is happening?

 

[tonydev314]

Update

I modified the code in ShowChests() down to this:

Private Sub ShowChests()
    Dim counter As Integer
    counter = 1
    For Each c In pChests
        MsgBox (CStr(counter))
        counter = counter + 1
    Next
End Sub

When the code runs it pops up a messagebox with a large -ve number (the number changes). As expected it increments 4 times (because there are 4 chests in the collection).
But I've set counter to 1 just 3 lines previously!
It originally was called index, but I use index in a few other locations so I gave it a name that is unique to the project.

 

[tonydev314]

Update 2 - even weirder:
Adding an extra msgbox Cstr(counter) before the For each statement and it works.

Private Sub ShowChests()
    Dim counter As Integer
    counter = 1
    MsgBox (CStr(counter))
    For Each c In pChests
        MsgBox (CStr(counter))
        counter = counter + 1
    Next
End Sub

The number now goes from 1 to 4 as expected.
Commenting out that first MsgBox line and it's a return to silly numbers.

 

[tonydev314]

Update 3
pChests is a Custom Collection object - if I replace it with a standard VBA collection object then the problem goes away.
If I replace the ForEach loop with a ForNext loop (which is what I've done), then the problem goes away.

Not sure what caused this bizarre behaviour but at least I can avoid it moving forward.

 

[tonydev314]

For Info I've found the answer in this post.

Bug with For Each enumeration on x64 Custom Classes