Unexplained Excel crash

Question

I am running an Excel VBA macro from a workbook macroWb which:

1. Opens an Excel Workbook wb(k);
2. Copies some cells from that workbook to macroWb;
3. Executes some fairly basic Excel commands (formatting, assigning values to cells, etc.) within the workbook macroWb;
4. Closes wb(k);
5. Repeats 1-4 for wb(k+1).

Letting WBs = {wb(1),...,wb(n)}, it seems that when I add further workbooks to WBs Excel ends up crashing. But then, if you ran the macro at another time it doesn't crash anymore - e.g. yesterday evening the macro was crashing, then I ran it this morning with the same set WBs as yesterday and it worked fine; I then added 3 new workbooks to WBs and it crashed again.

The crash does not seem related to any particular wb(k) as I have tested for that.

The crash details are as follows:

[...]
Fault Module Name:          StackHash_f2b6
Fault Module Version:       0.0.0.0
[...]
Exception Code:             c000041d
Exception Offset:           74384f69
[...]
Additional Information 1:   f2b6
Additional Information 2:   f2b6a6f8ea78ee3ad1bda3596b78feb6
Additional Information 3:   7b01
Additional Information 4:   7b010e90eab9e4895f15c0f6ba536eb5

Any idea of what might be causing this?

Answer 1

It very much looks as a time competition issue. There is an apparent difference between having a wait before opening a new file and having a breakpoint at the same location when running with debugger. My guess is that the breakpoint does not halt the sub-process of closing files whereas the wait does.

[If your WAIT was not at the same location as the breakpoint, I'd suggest you to move it there and retry]

What I would try is:

1. I'm assuming that the issue is related to the closing and opening of files; the issue is not caused from the number of files open (I worked in the past having perhaps 20 excel files open at the same time), but by the processes of closing and open; to test this theory, I would disable the file close part and leave files open (my recollection is that you would get the crash just above 10 files, so it should be easy and quick to test);

2. If the result of "1" is no crash, I would search for a mechanism (event?) that would be fired as soon as the file-close process completes, and only then attempt to open the next file;

3. If the result of "1" is yes crash... post the relevant section of your code and we'll see if there is any change that can be suggested.

This is indeed an interesting case!!!

Answer 2

Following @FDavidov advice, I ended up finding a (rather random) solution to this issue, which consists on popping up a message box that automatically closes after a specified amount of time just before opening a new file; surprisingly, the PopUp does not even seem to pause the program, lines following the PopUp execution line keep being executed while the PopUp is open.

Specifically, the PopUp code - credits to @Orphid, see his answer in the following thread - is the following:

Sub subClosingPopUp(PauseTime As Integer, Message As String, Title As String)

Dim WScriptShell As Object
Dim ConfigString As String

Set WScriptShell = CreateObject("WScript.Shell")
ConfigString = "mshta.exe vbscript:close(CreateObject(""WScript.Shell"")." & _
               "Popup(""" & Message & """," & PauseTime & ",""" & Title & """))"

WScriptShell.Run ConfigString

End Sub

The end result is then:

Sub Main()

Dim PauseTime as Integer
Dim Message as String
Dim Title as String
Dim wbk as String
Dim wb as Workbook

'Code

PauseTime = ...
Message = ...
Title = ...

'Code

For k = 1 to n
    '[Code]
    wbk = ...
    Call subClosingPopUp(PauseTime, Message, Title)
    Set wb = Workbooks.Open(wbk)
    '[Code]
Next k

'Code

End Sub

Answer 3

I encountered this same problem happening repetitively. It turned out the problem was to do with faulty RAM in the computer. The same issue did not occur on other computers, and we are now replacing the faulty RAM.