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.

On 8/11/2010 2:39 AM, wrote:
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

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

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.