locked
Recent crash problem with VBA code-bearing spreadsheets RRS feed

  • Question

  • Hi folks,

    I've been redirected here from https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/ because my issue is apparently more complex than typically addressed there. I have reproduced my post from there unchanged below (except that I corrected the MS Office build number. Either I made a typo first time around, or it changed when Office was last updated on April 18 2017, after my original post on March 31).

    First, the techy bits:

    • Windows 7 Home Premium Service Pack 1
    • Office 2013 build number 15.0.4919.1002 according to Programs list in Control Panel

    I have an application that I have been developing for a couple of years now. Originally it was a single spreadsheet, but as it has grown, I have spread the load across several spreadsheets, after I figured out that I could get them to pull data from each other, using the indirect function. Provided the “server” spreadsheets are open when the "client" needs to pull, this works fine  … most of the time. Just occasionally, the pulling cells all return #REF!, even though the server spreadsheet is open and calculation mode is set to automatic. Initially, I thought it had to do with order of opening, so I started to make sure I always opened the server before the client, but that turned out to be a red herring. I can’t remember how long ago I started to use this technique, and in any case it is only an occasional and not reproducible error, so I can’t say if it was always an issue or only became one at some point subsequently. Also, it has (so far) always been fixable by closing and reopening the spreadsheets, so I have not pursued it as an issue requiring support. I mention it in case it is relevant to the big issue I am about to describe.

    One of these spreadsheets, the “engine”, includes a small amount of VBA. I introduced it because the engine is slow to calculate and I need to loop round the calculation a large number of times, changing the inputs in a structured way. The VBA is a relatively late introduction – I avoid code in my spreadsheets wherever possible – and I was using the client/server technique well before I introduced the VBA. Anyway, the VBA was also working fine until about three weeks ago, about the same time as the release of KB3178690 that damaged Excel 2010, when the “engine” started to show the same symptoms as described for Excel 2010 (I know it was about the same time because it was Googling when the problems started that led me to identify that folks were having similar problems with Excel 2010 spreadsheets including VBA code). Unfortunately, I didn’t read carefully enough at the time to realise that the problems being reported were specific to Excel 2010.

    I did briefly think I had managed to code something spectacularly stupid, because I managed briefly to soldier on by going back to a previous version and updating the code, and it worked, briefly. But it didn’t last. I ended up in a situation where I couldn’t use the spreadsheets at all because I got stuck in a crash loop. When the engine finally opened (it’s ~200 MB so it takes a while) it would immediately crash. I would close down the other spreadsheets and reopen Excel, and Excel would try and open the spreadsheets that were open when the crash happened, including (of course) the engine, and (of course) when it finished trying the crash would happen again. Groundhog Day ...

    While this was going on, I once got an error message, which I dd not have the presence of mind to screenshot, about something-or-other’s inability to find some library or other. I also suffered wholesale file corruption: “engine” size c. 30 MB less after the crash than before.

    So I have been waiting impatiently for the fix, and now it has arrived, I find that isn’t going to help me, because whatever is causing my problems, it isn’t KB3178690. I haven't even tried to work with the spreadsheets since mid-March. Suggestions, please, for how to proceed.

    Sunday, May 14, 2017 9:45 AM