Excel crash when Reopen file after running VBA macro

[Alli]

Hello
This has been driving me mad for ages. I have a VBA macro in a 'main' workbook that loops through a series of workbooks. A workbook is opened, a macro is run from it, it is saved and closed, then the next workbook is opened. The macro runs fine, doing all it needs to do.
However, if, after running the macro, I then open one of the same workbooks (from another macro, from file open, or from explorer) excel crashes with "Microsoft Excel has stopped working ". This happens every time, it is not intermittent.
If I open other, unrelated excel files, I don't get the crash.
So, I'm imagining that something is not being completely closed / finished / cleared when the macro runs from the main workbook, or in the macro run from each of the workbooks that is opened in the loop.

Here is part of the code that deals with the opening and closing of the workbooks from the 'main' workbook:

'...code

Workbooks.Open Filename:=theFile(K), UpdateLinks:=3
Set wbSpec = Workbooks(specFile(K, 1))
myFile = ActiveWorkbook.Name
Application.Calculation = xlCalculationAutomatic
wbSpec.Worksheets("Flow Indicators & Run Scenarios").Activate
Application.Run ("'" & myFile & "'" & "!AnyScenario.AnyScenario")
'CLOSE THE FILE
Application.CutCopyMode = False
wbSpec.Close SaveChanges:=True
'...further processing
'...code
Application.Calculation = xlCalculationAutomatic
  StopTime = Time
  elapsedTime = Round((StopTime - StartTime) * 24 * 60, 2)
  wSheet.Range("N19").Value = elapsedTime
  wSheet.Activate
Application.StatusBar = False
Application.ScreenUpdating = True
wbHome.Save
Set wSheet = Nothing
Set wbHome = Nothing
Set wbSpec = Nothing
End Sub

The macro that is run from each opened workbook contains the following at the end (i.e. when finishing off the code):

'...code
Application.CutCopyMode = False
Worksheets(mySheet).Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Set SummISheet = Nothing
Set SummSheet = Nothing
Set SummMinSheet = Nothing
Set SummMaxSheet = Nothing
Set hmSheet = Nothing
End Sub

I'm not sure if this is enough of the code to be able to identify any potential problems.
Any hints would be very gratefully received.

 

[Trevor G]

Would you also need to add to set the Excel app to nothing so it seems as though it has completely finished its tasks!

 

[Alli]

Hello Trevor, thanks for your response.

Wouldn't I only "set the excel app to nothing" if i was opening and running excel from somewhere else?

My activities start with me opening the 'main' Excel file, then clicking a button to run the macro that opens and closes the series of workbooks. Then from the main file I want to carry on running other macros and doing other processing.
So i don't want to close Excel altogether.

Also if I look at the Task Manager after the macro has completed its run through the series of workbooks, I only see one instance of Excel running.

 

[Alli]

Hello again.
I'm not sure how to 'bump' this up to earlier in the list. In the Guidelines for this forum, #23 says: "At the top of every forum index is a link labeled "View Your posts in this forum."" I don't see this link?? and then #23 says: "You can "bump" posts back up to the top by posting a reply in the thread". Posting a reply doesn't seem to bump the post anywhere higher.
Am still really hoping for a solution. Whatever the mistake is in my code, I've probably unknowingly repeated it in other macros, but it just hasn't been so consistently apparent as it is here.
Please help!

 

[Alli]

Hello-I have still not found a solution. I suspect its something to do with running a sub that sits in Workbook2 from Workbook1, then saving and closing Workbook2.
Somehow that workbook2, Sub or something related to them is not being cleared out of memory or closed properly or something, and hence Excel crashes when i try to open workbook2 again.
If I close Workbook1 (but not Excel altogether) and then reopen it and then reopen Workbook2, it doesn't crash.
I didn't mention before that this is Excel 2010 running with Windows 7 - I think I didn't have this problem with Excel 2003, but I can't check now, as I have too many columns to convert back to 2003).

The error detail is:

Problem signature:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 14.0.5138.5000
Application Timestamp: 4d8146b8
Fault Module Name: EXCEL.EXE
Fault Module Version: 14.0.5138.5000
Fault Module Timestamp: 4d8146b8
Exception Code: c0000005
Exception Offset: 0012125e
OS Version: 6.1.7600.2.0.0.256.48
Locale ID: 7177

Additional information about the problem:
LCID: 1033
skulcid: 1033

 

[Alli]

Hello
Well I have not found a solution or a reason why the crash happens,
but I have found what is causing it, and therefore can work around it:

From Workbook1 I was running a macro which sits in Workbook2
using the following syntax:

Application.Run ("'" & myFile & "'" & "!AnyScenario.AnyScenario")

Everything was set to "nothing" at the end of that macro, Workbook2 was
saved and closed.

However, if I reopened Workbook2 ("myfile") whether with another macro or from My documents
or <File> <open> Excel would crash.

I have now instead duplicated the code that was in Workbook2 and put
it in Workbook1, so now I can run that code from Workbook1 using:

[B]Call AnyScenario.AnyScenario(NoScenarios, wbSpec)
[/B]

Excel no longer crashes if I reopen Workbook2 (wbSpec).
It is fantastic not to have to close excel before doing anything
to do with Workbook2 after running the macro, but annoying that I
have to have yet another macro housed in workbook1.

But I would still LOVE to know why this was happening if anyone can help
now that I've isolated what was causing the crash.
Any hints anyone?
</pre>

 

[RoryA]

Does this crash happen if you run any code in the other workbook during the process, or only the specific code you have?
As an aside, it's not good practice to have a routine with the same name as the module it is contained in, so I would recommend you change one of them.

 

[Alli]

Thank you Rorya for your response,

>Does this crash happen if you run any code in the other workbook during >the process, or only the specific code you have?

I've just tried now to do the same thing running another routine from within the second workbook, and get the same crash happening if I use the Application.Run method (also if I changed the module name to be not the same as the routine name).
I presume it can't be a general problem with using Application.Run in this way, as there would be far more posts about it if it were?

>As an aside, it's not good practice to have a routine with the same name >as the module it is contained in, so I would recommend you change one of >them.
I'm afraid I'm probably guilty of many bad practices. - I always have only one routine per module; does that make the same name issue less of an issue? Or, put differently: is this only a bad practice if there are multiple different routines within a module?
And: Is it bad practice to have only one routine per module?

Thank you!

 

[RoryA]

It does not really matter how many routines you have in a module, subject to the limitation on module size (around 64KB when exported as a text file), but you should avoid giving routines and modules the same name.

Have you tried installing Office 2010 SP1?

 

[Alli]

Thanks again, Rorya

I've just installed SP1 for Office 2010, and the same problem persists:

Excel crashes if I run, from WB1, a subroutine housed in WB2 and then reopen WB2 (no matter what the name of the module and routine, or what the routine does) if I use this syntax:

Application.Run ("'" & theFile & "'" & "!AnyScenario1.AnyScenario")

...but all runs fine if I call the same subroutine, but have it housed instead in WB1, therefore using:

call AnyScenario.AnyScenario(noScenarios, wbSpec)

When I have a bit more time I will rebuild WB2: the original form of it was built long ago in Excel 2003, with lots of piecemeal changes and additions (and rebuilds), plus upgrades from Office2003 through Office2007 to 2010 and from Win XP through Vista to Win7. Perhaps its just a horrible file.