HELP!!! Please - Weird and inconsistent errors and crashes.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been jointly developing a form with a college and she started getting
“Microsoft Office Excel has encountered a problem and needs to close. We are
sorry for the inconvenience.†In other words it crashed with no errors or
indication of what the problem might be.

If you disable macro’s it comes up fine. I then stopped the Auto_Open sub
running by simply making the first line an Exit Sub, saved it, re-ran the
Auto_Open sub routine from VBE and it works fine. Save the file and re-run it
works fine. Re-start, exit without saving and it crashes again on re-start. I
also get intermittent “Automation Error†normally when trying to save the
file. The other error I get is “Not enough system resources to display
completelyâ€. I have tried deleting some of the form content and some of the
code and you think you have fixed it, you save it, re-start it and it works
fine. Then you make a change, which could be as simple a typing a character
into a cell, save it, exit re-start it and it crashes again.

In researching this error I have read comments about running out of memory
and my college only has 512MB, but I have 2GB. I also found a note about
zooming to 100%, and this appeared to work i.e. changing two sheets from 80%
to 100%. Then made a simple change (cell update) and I am back to where I
started.

There are only 250 lines of very basic code, but there are a lot of userform
details so it is not easy for me to paste here. Any ideas?
 
Step through the macro and find the point that immediately precedes the
crash. Hopefully you can reliably reproduce the error and work around the
bug.
 
Tim,

Thanks for your reply, but that's my point it doesn't crash if you step
through! AND you only get the other errors when you are trying to save.
 
Do you have bound data, say to comboboxes or listboxes, using RowSource
and/or ControlSource?
 
bound data? If you mean have I used RowSource and ControlSource to link the
userform fields to the cells on the sheet, yes. Is this a problem?
 
Maybe. The reason I ask is because something might be triggering recursion,
where a change in a cell triggers calculation and/or the control's change
event, which triggers calculation, which triggers the change event, etc. If
you remove all bound data instances in all of the controls, perhaps even
disable the events associated with them, you can see if the problem is
related to that, or at least rule it out.

Backing up a bit more, as an easy first step, turn calculation to Manual and
try to replicate the problem. (Calc to Manual, save workbook, close, open,
etc).
 
Tim,

By removing all of the Forms ControlSource and RowSource values, it fixed
the problem, so now I guess I will have to right some code to load them with
the desired value.

Many thanks.
 
Not fixed! All references to ControlSource and Row source have been removed
from the forms, but it still crashes on start up, but never from the VBE.

I have rem'd out a fair chunk of the code and it is now stable, but not much
is left. To bring back the error I can change various lines of code i.e
nothing in particular, then I have to save, then I have to restart, then I
have to change data in any of the form fields, then save, then exit, then
restart and it crashes. Having to go through this loop every time I wish to
test even one change is obviously very time consuming. The process of
elimination is not logical because one piece of code work one time and then
appears to fails the next.

Anyone got any ideas?


Anyone got any ideas?
 
I know the type of debugging difficulties this can pose. A similar situation
occurred to me at one time, requiring workbook close/reopens, incremental
changes...very time consuming. In the end, it was a recursion type of
problem, which is wonderfully efficient at crashing Excel.

If you export all of your forms, code, etc to a NEW workbook, does that
help? Perhaps your workbook is corrupted.

First things first...be able to reproduce the error 100% of the time. Once
you can do that, it is much easier to localize the problem and fix it.
 
Tim,

I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.


After removing all the controlsource code this is the first line on a macro
to load a value back to the form:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address

With this line in a Module (not in the form), it seems to consistanty crash
when started from the cmd line. When run in VBE it's fine.

I am still testing by slowly adding back in my code so will post another
update good or bad.

Thanks for the continued encouragement, cos I'm about to throw this excel
app in the bin!
 
A couple of observations:
DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address
Click to expand...

Try adding the External argument to the Address property. It pinpoints the
range
exactly:

DXPForm.AM_Name.ControlSource = ThisWorkbook.Sheets("DXP
Form").Range("DXP_AM").Address(External:=True)

You'll see the difference if you debug.print the differences. In the
immediate window:

?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address
?ThisWorkbook.Sheets("DXP Form").Range("DXP_AM").Address(External:=True)
(check the spacing in "DXP Form". My newsreader inserted a linebreak and I
could not see if there were 1 or 2 spaces).

Without the External argument, Excel will update the *active* sheet. So if a
sheet other than "DXP Form" is active during the ControlSource assignment,
Excel will stick the selected value from the control into THAT sheet's cell,
not "DXP Form". I'm suspect that's not the behavior you want, instead that
the value should always go into "DXP Form". The result, depending on how
your controls are setup, can possibly trigger another control's Change
and/or Click events if the 2nd control's ControlSource is referring to the
same range as the 1st control.
With this line in a Module (not in the form), it seems to consistanty
crash
when started from the cmd line. When run in VBE it's fine.
Click to expand...

cmd line? Not sure what you mean.

I exported all the modules and forms, then copied all the sheets from one
workbook to another and then imported everything back, no luck.
Click to expand...

I was thinking of exporting everything to a new workbook, but not importing
back. If the workbook is corrupted, you'll have to rebuild the workbook.
But, that's a "worst case", last-resort. No need to focus on that just yet.
 
Tim,

For the code below that failed, I added this into the form as the
ControlSource "=DXP_AM" and it worked fine, but then it stopped on another
line, 20 or so lines down, same issue, same fix.

Thanks for the (External:=True) tip, I have not seen that before.

You assumption was correct I always wanted to write to the "DXP Form" sheet
even if it was not the active one. This is why I added
"ThisWorkbook.Sheets("DXP Form")." on the front of the Range parameter
because I could see it was not always getting the data from the correct sheet.

cmd line as in Command Line, sorry its probably a Unix or DOS term, I meant
double clicking the file from an Explorer Window.

I have been using the MultiPage tool on the form and had pages 5 deep. As
part of trying anything, I removed one level and all me errors disappeared. I
have now reactivated all the code including the one I mentioned earlier and
they all now work ok. So far no crashes, I'm feel good at this point, but I’m
not shouting to loud just yet.

If this does indeed fix my issue my guess is there is a bug in Excel if
there are two many levels of pages each with a lot of data on.
 
Sounds like you're getting closer to a fix.
my guess is there is a bug in Excel if
there are two many levels of pages each with a lot of data on.
Click to expand...

If that is true, I'm curious why the workbook crashes except when executed
from within the VBE?
 
Back
Top