Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 15th, 2010, 03:27 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Microsoft Office Excel has stopped working.

Hi,

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).

Any help would be greatly appreciated.
Reply With Quote
  #2 (permalink)  
Old August 18th, 2010, 05:25 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem solved..

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:

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.
Reply With Quote
  #3 (permalink)  
Old August 23rd, 2010, 08:14 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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 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.

Does that make sense?
Reply With Quote
  #4 (permalink)  
Old August 23rd, 2010, 12:15 PM
Registered User
 
Join Date: Nov 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Thank you.
Juan Luis
Reply With Quote
  #5 (permalink)  
Old August 24th, 2010, 07:33 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #6 (permalink)  
Old August 24th, 2010, 08:28 AM
Registered User
 
Join Date: Nov 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello.
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)
msgbox(stext,vbokonly)
end sub

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
with oMessage
.MyMessage sText
end with
set oMessage = nothing
end sub


When I need show a message I will write the code
ShowMessage "My test is good"

The code is most clear.

Thank you
Juan Luis
Reply With Quote
  #7 (permalink)  
Old August 31st, 2010, 09:14 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem solved..

Juan,

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..
Reply With Quote
  #8 (permalink)  
Old September 6th, 2010, 09:49 AM
Registered User
 
Join Date: Aug 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem finally solved..

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
My contact form stopped working..again. Will BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 18 March 21st, 2009 01:47 PM
Intellisence stopped working beddowsm Visual Studio 2008 3 September 5th, 2008 12:32 PM
date function stopped working in report Loralee Access 6 December 8th, 2005 06:11 PM
Help... Smtp.Mail.Send stopped working funtent General .NET 1 October 27th, 2004 07:26 AM
Intellisense stopped working in InterDev 6 riga1966 Classic ASP Professional 3 June 1st, 2004 05:31 AM



All times are GMT -4. The time now is 08:49 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.