PDA

View Full Version : Solved: Excell crashing for apparently no good reason



zagrijs
02-08-2013, 12:52 AM
:hi:

I have previously posted under the subject "Apllication hanging for apparently no good reason". Aussiebear and snb assisted for which I did and still appreciate.

To recap what happened. The application crashed after the file was saved under a new named and when I tried to open the new file. snb suggested that it might have to do with rowsource of comboboxes. Although i had not used rowsource, I changed my comboboxes's lists. It seemed that the problem was solved and I reported it as solved.

However, a few days later the problem resurfaced. I tried everything I could think off. Eventually I had basically all code commented out, to no avail. As a last resort I started deleted objects from the form. When i had deleted about 50% of the objects the problem was solved. Fortunately I could split the form in two forms whithout compromising my application.

It would seem that there is a limit to the number of objects that one can put on a form. Unfortunately the compiler doesn't warn you when you exceed that limit.

I'm sharing this in the hope to learn more if I am still mistaken somewhere and simultaneously to make other people aware of the possible cause if you also experience Excel crashing without any obvious reason.

snb
02-08-2013, 12:20 PM
You better post a sample workbook so we can have a look into it.
Based on your assumptions I think the code in the workbook can be improved considerably.

zagrijs
02-09-2013, 03:12 AM
Thanks snb.

I tried to upload a file, but even after I deleted all my forms, except one as a sample, it still exceeded the 1mb limit. Please bear with me. I will try to delete more code to provide you with a sample file that would give an idea of what my code looks like.

I do NOT want to make misuse of you!

Regards

Aussiebear
02-09-2013, 03:57 AM
Can you tell us what you were doing at the time it wants "to hang for apparently no good reason"

zagrijs
02-09-2013, 08:05 AM
To Aussiebear,

It is difficult to pinpoint when and why it started behaving that way. I usually simply hibernate my laptop when I close up for the day without closing Excel. The next day when I start, I use "Save As" first to save the file under a new name with that day's date as part of the file name. That way weeks could have gone by without the file getting closed.

It was only when I closed the file and wanted to restart it that the problem presented.

It happened some months ago too. At the time I upgraded my laptop's RAM, to no avail. The dealer proposed that I reinstall Excel. I did and upgraded to MS Office 2010 simultaneously. That solved it, or so I thought, until it started again in January after a two week holiday period. This time reinstalling Excel didn't solve anything. The problem also occurred on other machines. So it was clear the problem was in the code.

I tried in every possible way to comment code out, i.e. the entire Initialize event and eventually all the other code linked directly or indirectly to objects on the form - literally everything! When I saved it under a different name, the first generation file with a different name loaded without hiccups, but the second generation (i.o.w. the same file - unchanged! - saved again under a different name) crashed Excel immediately.

In desperation I tried the only other option I could think of, i.e. I started deleting objects from the form. When I had removed about 50% of the objects, the file (even 2nd and 3rd generation files) loaded without crashing Excel. I started uncommenting the code bit by bit until all the code was active again; the file loaded and is still loading without problems, 1st, 2nd, 3rd and whatever generation under a new name. That is when I decided that Excel must have a limitation on the number of objects on a form and that I must have exceeded that limit whithout Excel warning me about it.

As you and snb were willing to assist me I felt I had to share my experience.

Aussiebear
02-09-2013, 02:27 PM
Since Excel is still running, could your code in the original file be tying up resources? Does this issue occur when opening Excel for the first time?

Just how many objects do you have attached to the form?

gringo287
02-10-2013, 03:34 AM
Hi zagrijs,

Please could you post examples of the error messages you get with this issue. I think I'm experiencing a similar issue and have posted here the other day to seek advice.

Thank you

Bob Phillips
02-10-2013, 04:13 AM
That is when I decided that Excel must have a limitation on the number of objects on a form and that I must have exceeded that limit whithout Excel warning me about it.

It would have to be an enormous number if there were such a limit, a number far exceeding good design. How many have you got?

Artik
02-10-2013, 09:59 PM
zagrijs, maybe you can find the answer here (XLimits) (http://www.xlam.ch/xlimits/userforms.htm#Allgemeines über Limitationen von UserForms und Controls). Unfortunately, in the German language.

Artik

Simon Lloyd
02-10-2013, 10:13 PM
Don't post the workbook if it's too large, instead why not post ALL your code, maybe we can help from that, you can also help yourself by making sure you have released all variables after they are used i.e setting them to nothing...etc

zagrijs
02-11-2013, 01:01 AM
Thanks for all the replies and everybody's interest. I will try to respond to everybody.

@Aussiebear and gringo287: It happens when I attempt to load Excel and as I said with the 2nd or 3rd generation of a file saved under a new name. When I click the option to enable macros, I immediately received the following error message:
"Microsoft Excel has stopped working.
Windows can check online for a solution to the problem and try to recover you information.
-> Check online for a solution and close the program
-> Close the program"

Clicking the first option is useless. The program will simply keep you waiting for hours and the only way to get out of it is to log off. The best way to get out is to click the "x" button to close the message, which immediately also close Excel with it.

If I open any previous version under an existing name that worked or another Excel spreadsheet with macros enabled, the offending file will open without any hiccups. If I simply save the offending file as is while the other Excel file is open, the offending file will open the next time and function without hiccups.

It thus doesn't seem to be a case of tying up resources, seeing that it worked while another file with the same code is already open and especially as commenting ALL code out didn't solve the problem! - only removing objects from the form irrespective of code solved it.

@Artik. My german is a bit rusted and relates to other disciplines than IT. However, if I understand correctly, it would seem to support xld's statement that there is no limit.

@xld. The number of objects on the form that crashed Excel is 662. After splitting the form in two forms, the maximum on one form is 330.

@Simon Lloyd. I don't mind posting the code (but the code is BIG) and I'm sure that more experienced people would be able to improve tremendously on it, but I do not understand how the code can be at fault when every bit of code was commented out and the problem still occurred until I removed about half of the objects on the form. I will readily admit that I am not releasing variables. I know how to do it in dBase and Visual dBase, but I have not seen any examples in VBA. However, it cannot possible be the reason for the error if all the code is commented out and no variables are used.

snb
02-11-2013, 02:45 AM
I usually simply hibernate my laptop when I close up for the day without closing Excel. The next day when I start, I use "Save As" first to save the file under a new name with that day's date as part of the file name. That way weeks could have gone by without the file getting closed.

You probably better use the same file, without renaming it, and when activating save a copy as 'backup'

e.g.
Private Sub Worksheet_Activate()
c00="G:\Backup\" & format(date,"yyyymmdd") & thisworkbook.name
if dir(c00)="" then thisworkbook.saveCopyAs c00
End Sub

zagrijs
02-11-2013, 04:22 AM
@snb. It is important that the users should be able to save the file under a different name. It is used as a reporting tool to collect data from different regions. Every region should send it in with it own name part of the file name as well as the date representing the reporting period.

As I said, splitting the form in two forms with about half the original number of objects on each form solved the problem.

Artik
02-11-2013, 04:28 AM
zagrijs
Oh, my german is not exist.
But there is Google Translator. :-)


Theoretically there is no limit, but ...
Google translate:
Number of controls
The maximum number of controls on a Visual Basic for Applications UserForm is not determined by a fixed boundary. In one test, a user could form with 600 controls are created.

Please take note that although virtually unlimited controls can be placed on a user form, only the first 411 controls that were added, can be addressed directly with VBA code. In a control code is directly referenced, after which the 411th Control has been added, so Microsoft Excel crashes immediately. In addition to Excel, Word and PowerPoint different Office versions are affected.
xld also said something about "a number far exceeding good design".

Artik

Bob Phillips
02-11-2013, 05:19 AM
I did indeed, and I would suggest that 411 does far exceed good design. I cannot comprehend anyone being able to take-in a form with that many controls.

I would assume that not all of the controls can be relevant, some would apply in one situation, some in another (otherwise I think you really do have a problem), and as such you would be far better implementing a wizard type approach, start with a form where a choice is made, and depending upon the selection or data entered one of a number of other forms is then shown, and so on. This way each form would be much smaller, and would also guide the user through the data entry process. Of course, you would need to provide the ability to step-back as well as forward in case they make a mistake.

snb
02-11-2013, 05:22 AM
It is important that the users should be able to save the file under a different name

That's exactly what I am proposing.
If you think I didn't I recommend you to dive into the helpfiles of the VBEditor and scrutinize the lemma 'SaveCopyAs'.

zagrijs
02-11-2013, 06:12 AM
Thanks Artik.

It would seem that settles it then and that my conclusion that it was caused by the number of objects seems to be correct.

I am not sure what xld would regard "good design". As the application has to be used by many data capturers, I have done my best to keep it as simple as possible without compromising on the information needed.

Bob Phillips
02-11-2013, 06:29 AM
I am not sure what xld would regard "good design". As the application has to be used by many data capturers, I have done my best to keep it as simple as possible without compromising on the information needed.

I made in suggestion in post #16.

zagrijs
02-11-2013, 06:42 AM
Thanks to everybody.
@snb and xld. My last reply was posted before I read your last two postings. I take note - thanks!

I will rethink my design and I will check the VBEditor helpfile re "SaveCopyAs".

Thanks to everybody once again. I close this thread now as solved.