You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
I am writing an Excel VBA application using a single Userform and a single Module. I was adding some new code 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. 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 having too much code in two places is the problem. So I have 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).
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 assicaited with 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:
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 really 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.
Hello, I think that you will put all code with ' the mark for comments. Except a small procedure will work ok.
Run the program.
The program run sucessfully and then stop the program.
Remove the mark ' from all code.
Run the program another time.
In the latest version that has the code into split into sub 64K chucks, I put in a special trace yesterday and one by one found three statements, that when executed, cause the error. They may be more, but I stopped after 5 hours..
They all set the back ground colour property of three different text box controls, in three different frames of my multi-page control, in the User form I added prior to the error occurring, about 3 weeks ago.
So it is reassuring to know that these are the offending statements. (I should have done this sooner. But, I was under the impression that the error was occurring before the User form was loaded. I was wrong.)
The first question is: Why has this happened? Did these controls and the associated statements, that set the back ground colour, tip some sort of internal User Form table past a limit? Or did some sort of internal corruption occur? I guess I will never know the answer to that..
But the next question and perhaps the most important question is: What do I do now?
The large amount of code is not problem.
But you can divide the code using modules and class.
Then the code would be most clearly.
For example: I want to use a message, I create a class in my project.
Name: cls_Message (F4 Properties, change the name)
Public Sub MyMessage(sText as string)
In my main module I create a procedure for example:
private sub ShowMessage(sText as string)
dim oMessage as cls_Message
set oMessage = new cls_Message
set oMessage = nothing
When I need show a message I will write the code
ShowMessage "My test is good"
Thanks for you input. 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..