Excel Crashing Sometimes When Closed from Shared Network Location

Question

This problem will sound broad and non-specific, but I've tried many things and don't know where else to turn.

I have an Excel VBA project - it has hundreds of lines of code and several modules so I can't really post it all. It works as a template - users open the file, make a bunch of changes to the template and save a copy somewhere. The master template never changes.

Everything works great, except it frequently crashes when the user closes Excel saying "Excel has stopped working". It crashes any opened excel files so pretty frustrating.

This error only happens when the file is opened from a shared network location. I've never once experienced this working locally.

There is no VBA code executing .BeforeClose that could be interfering. On workbook open, I have it set to fetch two different CSV files and copy data over from them.

I've gone through each module and sub to see if any of them are contributing to the crash - but sometimes the workbook may crash upon closing without having run any VBA code (outside of the Workbook.Open code)

Also, it will never crash when in 'Read Only' mode (the Master file is always read only) - only once the user has saved a copy somewhere will the crashing begin.

Does anyone have any tips or ideas on where I can further troubleshoot this issue? Been working on this one forever, about to hire an expert if I can't solve soon. Thank you!

**edit 4/15 - just an update, still searching for answer if anyone has solution. The only thing I can find related to the problem is referencing files on a shared network drive. Upon closing, is there a reason anyone can think of that may cause a crash?

Answer 1

Hard to tell for this type of issue. I think it's a case of trial and error for you.

What I would try initially, would be some of the following.

1. Try a code cleaner on your Excel workbook. This one for example http://www.appspro.com/Utilities/CodeCleaner.htm
2. I'd consider rebuilding the workbook from scratch if possible and recopying your code back in. I'd save the code to text files and remake the modules, classes, sheets from scratch.
3. I'd clean out your temp directory. Excel saves VBA objects to the temp directory. If it gets too large it causes issues.
4. I'd check there's no addins that potentially could be affecting things. Try removing any unused adding for a minimum build.
5. In the past, I've had issues with some libraries that were early bound, but this was more with opening workbooks that caused exceptions. Perhaps you could try removing the references and late bind your objects - assuming of course you're not using events in those objects. So instead of using Dim x as SomeObject, use CreateObject("....") and remove the references to the libraries.
6. I'd also have a look in the Microsoft Event viewer to see if you can get any more information. A specific exception may help you in the right direction.

Sorry these are a bit vague, but at least you can try them. It's hard being able to give more concrete recommendations remotely without being able to see what's happening / test out some possible theories.

Hope that helps.