Excel 2010 crashing predictably -- HELP!!

jasmith4

Active Member
Joined
May 16, 2008
Messages
337
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.
 

Forum statistics

Threads
1,082,283
Messages
5,364,273
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top