I’m writing about a subject I’m sure you’ve dealt with hundreds more times than I have: Excel crashing. What’s going on is that during certain operations as it runs, Excel crashes with the restart/debug dialog box. Here’s the thing: it happens predictably in two different places – two specific lines of code – that I’ve found so far:
• A routine in ThisWorkbook which removes lots of tabs, connections and objects, then saves a copy of itself by another name makes Excel crash when it gets to the very statement that saves: “Me.SaveAs FN$”, where FN$ is a complete path name, and at that time EnableEvents, DisplayAlerts and ScreenUpdating are off, and calculation is manual.
• A routine which refreshes a listobject’s query and then scans the top row for formulas to auto-fill down makes Excel crash as it looks at the top row and asks “If Cell.HasFormula Then” before doing the auto-filling – that one I avoid by asking first “If Not IsEmpty(Cell.Value)” first. FYI all the queries are ODBC, either to Oracle 11g32 or to Access 2010.
You see, a few months ago this workbook started exhibiting the behavior that when I first open it, I see “This action will reset your project, proceed anyway?”, even though there’s no Workbook_Open or AutoOpen. But I click OK, put it in Design Mode, save it, close Excel altogether, and re-open it, and I don’t see that any more. When I compile the code, save it, close it, and re-open it, the file is about 200k bigger, and I see that dialog box twice. So to me this means there’s some kind of physical corruption in the file itself – even if I open the XLSX I see this dialog box. Perhaps it’s something about my machine or its Office installation – even when I first start up and ATF-Clean my machine, I open Excel, I get a new blank workbook, I hit Ctrl-S to save, and the default file type is XLSM, not XLSX.
This workbook has changed a lot – it’s now version 58. I run Rob Bovey’s Code Cleaner on it just about every time I save it, so at least 10 times a day. I even have my own utilities that export all VBA objects to text (forms as both FRXs and FRMs), remove all of them, then re-import all of them – I use these to check code into CVS for comparing versions – and Rob Bovey himself tells me this is basically what his Code Cleaner does. I’ve even exported and removed all the code, saved it as XLSX, then opened the XLSX and saved it as XLSM, THEN imported back all the code, and it still shows that dialog box when opened and crashes in the same places when run. Also, again at least 10 times a day, I close EVERYTHING and use ATF-Cleaner from Welcome to the Frontpage - www.atribune.org, which I’ve done for many years and has cured or prevented many Excel problems before by cleaning out files Excel notoriously leaves behind, even under normal circumstances.
I’m desperate here, and I have a hard deadline. I’ve been all over the forums, I’ve seen a zillion recommendations for Code Cleaner, and lots of other “cures”, but nothing seems to work. I’ve disabled every add-in I have, and the only thing that’s done is to get rid of the VBIDE’s “phantom” projects, whereby if you open a file, close it, and open it again, you’ll see two identical projects in the Project Explorer, because the first doesn’t disappear when you close it. I’ve even started and made pretty good progress on a utility which duplicates a workbook, cell by cell, character by character for labels, listobject by listobject, shape by shape, and code line by code line (which I can do already) – I figure re-creating it from scratch will make a copy without any corruption.
Any suggestions you may have would be truly appreciated – I know I’m asking a lot. I’ll send the whole thing, if you can provide some place I can drop it for you – that would be great.
• A routine in ThisWorkbook which removes lots of tabs, connections and objects, then saves a copy of itself by another name makes Excel crash when it gets to the very statement that saves: “Me.SaveAs FN$”, where FN$ is a complete path name, and at that time EnableEvents, DisplayAlerts and ScreenUpdating are off, and calculation is manual.
• A routine which refreshes a listobject’s query and then scans the top row for formulas to auto-fill down makes Excel crash as it looks at the top row and asks “If Cell.HasFormula Then” before doing the auto-filling – that one I avoid by asking first “If Not IsEmpty(Cell.Value)” first. FYI all the queries are ODBC, either to Oracle 11g32 or to Access 2010.
You see, a few months ago this workbook started exhibiting the behavior that when I first open it, I see “This action will reset your project, proceed anyway?”, even though there’s no Workbook_Open or AutoOpen. But I click OK, put it in Design Mode, save it, close Excel altogether, and re-open it, and I don’t see that any more. When I compile the code, save it, close it, and re-open it, the file is about 200k bigger, and I see that dialog box twice. So to me this means there’s some kind of physical corruption in the file itself – even if I open the XLSX I see this dialog box. Perhaps it’s something about my machine or its Office installation – even when I first start up and ATF-Clean my machine, I open Excel, I get a new blank workbook, I hit Ctrl-S to save, and the default file type is XLSM, not XLSX.
This workbook has changed a lot – it’s now version 58. I run Rob Bovey’s Code Cleaner on it just about every time I save it, so at least 10 times a day. I even have my own utilities that export all VBA objects to text (forms as both FRXs and FRMs), remove all of them, then re-import all of them – I use these to check code into CVS for comparing versions – and Rob Bovey himself tells me this is basically what his Code Cleaner does. I’ve even exported and removed all the code, saved it as XLSX, then opened the XLSX and saved it as XLSM, THEN imported back all the code, and it still shows that dialog box when opened and crashes in the same places when run. Also, again at least 10 times a day, I close EVERYTHING and use ATF-Cleaner from Welcome to the Frontpage - www.atribune.org, which I’ve done for many years and has cured or prevented many Excel problems before by cleaning out files Excel notoriously leaves behind, even under normal circumstances.
I’m desperate here, and I have a hard deadline. I’ve been all over the forums, I’ve seen a zillion recommendations for Code Cleaner, and lots of other “cures”, but nothing seems to work. I’ve disabled every add-in I have, and the only thing that’s done is to get rid of the VBIDE’s “phantom” projects, whereby if you open a file, close it, and open it again, you’ll see two identical projects in the Project Explorer, because the first doesn’t disappear when you close it. I’ve even started and made pretty good progress on a utility which duplicates a workbook, cell by cell, character by character for labels, listobject by listobject, shape by shape, and code line by code line (which I can do already) – I figure re-creating it from scratch will make a copy without any corruption.
Any suggestions you may have would be truly appreciated – I know I’m asking a lot. I’ll send the whole thing, if you can provide some place I can drop it for you – that would be great.
