Microsoft Office Excel has stopped working.. 1

[DavidWilkinson]

Hi,

I wonder if you could help me.

I am writing an Excel VBA application using a single Userform and a single Module. I was adding some new code to the Userform when I started getting the following error message:

"Microsoft Office Excel has stopped working. A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available".

The Userform and a single Module I have defined each contains a large amount of code:

- The Userform contains approx. 120 procedures and functions and approx. 12,000 lines of code.
- The Module contains approx. 150 procedures and functions and approx. 10,000 lines of code.

I suspected that my code needs to be separated into multiple modules. So I have already tried to break the Userform code and the code in the Module into separate modules, but I still get the error message, not always, but very frequently.

However, if I view the code using ALT+F11 and then invoke the initial macro, I don't get the error message..

I am using Excel 2007 with Windows 7 on one machine and Excel 2007 with Vista on another (I get the same results).

Any help would be greatly appreciated.

Best regards
David Wilkinson

 

[DavidWilkinson]

The problem has gone away. It turns out that the problem was caused by hitting a limit of either the allowed number of lines of code allowed for a Userform or the size of the Userform code module itself was greater than a certain limit. I don’t know for certain.

I suspected that something was corrupted, but it appears that that wasn’t the case. It looks like this message occurs when you hit some sort of undocumented limit. So by going back to a previous version, not the actual previous version, but two levels back which worked and by splitting the code into separate modules, I can add more code without a problem.

There doesn’t appear to be much original information on the Internet about Excel VBA limits. However I did find:

http://www.mvps.org/dmritchie/excel/slowresp.html

- mentions a soft limit of 64K per module and a limit of 4000 lines of code per module.

I did find some information in the following (excellent) books:

(1) Professional Excel Development (Wiley) - mentions a soft limit of 64K per module – page 45.

(2) Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition) (Wiley) – also mentions a soft limit of 64K per module – page 43.

 

[IDS]

FWIW, I have also seen the 64KB per module limit mentioned, but I don't recall seeing anything official from Microsoft.

It might be an idea to transfer all the code from the form to separate code modules and just have a three liner as the actual form code.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[DavidWilkinson]

Doug,

Thanks for the suggestion. Although I am writing a very large Excel VBA application, it is my first one and I am relatively new to the subject. So, I have a question.

If I do as you say and create a three liner as the actual Userform code. Will the Userform variables be available in the standard modules? In the simple tests I have made, they don't appear to be.

 

[Denial]

I think you will have to transfer the values of these variables to other, more widely available, variables. These new variables will need to defined in the "declarations" section at the top of your standard module, and declared as "public".

 

[IDS]

I'd avoid using public variables too much. They can be convenient, but are dangerous because any changes you make in one routine will be seen by every other routine, even in other modules, which may not be what you want.

I suggest passing all the variables to the top level routine in a normal code module. If you have a lot of data you can pass it as an array.

Professional Excel Development covers this in more detail, so it would be worth reading what they say.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[DavidWilkinson]

Thanks for that. I will try that.

 

[DavidWilkinson]

The problem has returned.

I have split the code into a number of Standard modules all less then 64K and the Useform code module is now 76K, using just the simple techniques (That is, not using classes).

But I get the same problem. I have also tried this latest version on Office 2010 Excel and I get the same problem.

However, I have tried all versions on my old desktop machine, which has XP, Office 2003 and less memory and I get no problem at all..

So I am not now convinced it is a size problem.

I have also spent a lot of time reading about how to use classes linked to events to add an extra User Interface layer. In other words, do it the proper way recommended in the Professional Excel Development books. But I don't really want to spend weeks doing this to find it may be a waste of time..

I am thinking about ordering Office 2003 Professional for my laptop, assumimg that will work, so I can continue developing.

Does that make sense?

 

[DavidWilkinson]

I believe I have finally solved the problem.

As a last resort, I rebuilt the form by hand, control by control into an empty workbook. I then only imported the form – not the code associated with the form, because there wasn’t any – into the version that failed and the versions I have created where I have split up the code. And to my surprise they all worked.

Note. It is important to take a copy of the form code in the version you are going to overwrite, before you import the new form. You then have to put the code back, before you can use it.

So it looks like the original problem was caused by a corruption of the graphical component of the form (.FRX).

Anyway, after all this lost time, I have learned a good lesson. I have already split the code into separate modules, passing across the form object as a parameter, instead of using the form name as a public variable.

I have also been studying how to use classes. So I am going to go the whole hog and do it properly.. Just in case I hit the dreaded 64K limit in the future.

I also export the components regularly to check if I am going over the limit..

 

[DavidWilkinson]

It turned out I hadn't solved the problem. But I am fairly certain I have now..

Rather than it being caused by the size of the code modules, the original error was certainly, or as certain as I can be, caused because I had exceeded the number of controls allowed on a User form.

This limit and the other limitations are explained clearly at:

http://kbalertz.com/229756/Maximum-Number-Controls.aspx

I have split my User form into 8 forms. I have been up and running now for 3 days, adding more code, more controls and it all works perfectly.

Since I had already split up the code into modules below 64K, I am also now regularly exporting all modules (and forms) to make sure I won’t exceed that limit.