I am getting the following error message "Microsoft Office Excel hasstopped working", when I try to

[dwidavidwilkinson]

The full error message is "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".

Hi,

I have written an Excel VBA application using a single Userform. But,
the Userform and a single Module I have defined each contains a large
amount of code. For example:

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

I suspected that is the problem. 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.

 

[Mike S]

The full error message is "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".

Hi,

I have written an Excel VBA application using a single Userform. But,
the Userform and a single Module I have defined each contains a large
amount of code. For example:

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

I suspected that is the problem. 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.

Do you have code to handle errors in your subs and functions, e.g.

Function Somename() as string
on error goto SomenameErr
...
'function code
...
exit function
'
SomenameErr:
msgbox "Error in Function Somename: " & err.number & " " & err.description
end Function

This might be useful, if the error always occurred in the same sub or
function then you could probably track it down further considering the
error number returned.

Mike

 

[Dave]

Mike,

I do have code to handle errors in most of my procedures - all but the
trivial ones. But I don't get as far as an error message from any of
these procedures. As soon as I invoke the procedure that loads the
Userform, The Userform does not load and I get the message I have
described..

David Wilkinson

 

[Dave]

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 attached
to the 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.

 

[Dave]

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.