11

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?

arbitel
  • 321
  • 6
  • 22
  • ok it's a wild guess here, but i can only imagine that it has something to do, with Excel encountering some Problem while checking if it should Display the save file Dialog, have you tried to overwrite the Auto_Close() function ? – Doktor OSwaldo Apr 18 '16 at 11:08
  • 2
    Try without the VBA code, or without the template format or without data to get a better idea of the source of your issue. As it is there is too many factors. – Florent B. Apr 19 '16 at 03:25
  • Any hints from **Event Viewer**? Event Viewer --> Applications and Services Logs --> Microsoft Office Alerts. Some Add-Ins can cause this, try Safe mode Excel: `EXCEL.EXE /safe` – PatricK Apr 20 '16 at 05:44
  • 1
    I have the same issue unfortunately, what i found from scanning Microsoft forums is that after checking Add ins etc and using safe mode, they have put a lot of the issues down the transition from using different versions of office it seems as if the majority of the problems with Microsoft applications crashing arise with Office 13. It may be due to people using different versions of office running on shared networks with a variety of the problems discussed by everyone so far. – Matthew Bond Apr 20 '16 at 10:55
  • Oh yes, that's a very good point. I've had compatibility issues due to that. Maybe try saving the workbook as an older version and seeing if you get the same issue. – PaulG Apr 20 '16 at 19:05

4 Answers4

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
PaulG
  • 1,051
  • 7
  • 9
  • I would attempt the second point here. I've had this kind of thing before too, but rebuilding it fixed it. No idea what the real problem was though. – steveo40 Apr 22 '16 at 09:23
0

Open a new instance of Excel, navigate to your file, click once, click the down arrow on the Open button, click Open and Repair.

halfer
  • 19,824
  • 17
  • 99
  • 186
ASH
  • 20,759
  • 19
  • 87
  • 200
  • Unfortunately, that did not work - I got through 7 open and closes before the "Microsoft Excel has stopped working" error. Thank you for your response though, appreciate the help! – arbitel Mar 24 '16 at 18:28
0

One reason could be due to AutoRecover. When AutoRecover is saving a backup copy and user also try to save, there is a very high chance that Excel will crash for a file on network path. AutoRecover by default runs every 10 minutes, which is also quite frequent. I'm not particularly sure why it crashes, could be due to network latency.

AutoRecover can be turned of programmably. But it will be turned off at application level instead of workbook level. So remember to turn it back on when exit the workbook you don't want to use AutoReceover with.

Hope this helps.

Rosetta
  • 2,665
  • 1
  • 13
  • 29
  • I haven't heard that before, and it sounds really promising - I'll run some tests when I have a moment and update if this is the answer. Thanks either way! – arbitel Apr 15 '16 at 16:44
  • ya it does, but i wouldn't quite expect this to be the answer really... sometimes workbook just turn nasty after lengthy edits, and perhaps restart from scratch could be the answer... but first lets hope this helps, finger crossed – Rosetta Apr 15 '16 at 17:06
0

I am using Excels with Share drive too in my organisation... There can be many reason and depends on share drive you are using. If you are using shared drive which is web based and not compatible with Macros it will stuck excel. It is basically issue with office share point.

Also you need to shorter the code and need to use Option explicit in VBA code and describe all variables. Sometime due to missing defined variables it will stop working and excel will stuck.

Also you need to disable ADD-INS in excel to make it work faster. If you can share code people can tell better why it is not working perfectly. If you can share what type of Share drive you are using we can check that also because i faced same thing in past and i worked on my code and share drive again.

Atul Vij
  • 241
  • 2
  • 14