Excel sometimes crashes when running a sub routine

**Brunstgnegg** · 08-19-2010, 03:09 AM

Hello

I have a really weird error and I have no idea what's causing it. In my project there is a very big userform with several tabs that is loaded using a subroutine. Sometimes when the sub is run it results in the error "Microsoft excel has encountered a problem and needs to close" with no further information as to what is causing it. The issue is complicated further by the fact that every time I attempt to add breaks to the code the error does not occur which makes it difficult to pinpoint the problem. If the error does not occur the first time the sub is run it will run smoothly all other runs as well until Excel is restarted, then it (sometimes) start happening again. If I open the project and do something like generate a report before I run the sub it works fine. Furthermore if I add msgboxes scattered across the code to track how far it gets (since breaks make the error go away) the error does not occur.

I don't know exactly when the error started occuring either since it doesn't occur until I restart Excel.

I wish I could give you code to look at but the the sub is HUGE and I have no idea what part of it is causing the error. The code is mostly database queries, comparisons and updates to form controls. An example code snippet (which could cause it for all I know) is here:

Please Login or Register  to view this content.

Has anyone else encountered this problem? How did you solve it? Any suggestions as to how to pinpoint the error?

**romperstomper** · 08-19-2010, 03:14 AM

Try running excel with no addins or other workbooks loaded and see if the error still occurs.

**Brunstgnegg** · 08-19-2010, 03:44 AM

Originally Posted by romperstomper

Try running excel with no addins or other workbooks loaded and see if the error still occurs.

That's the way I'm running it. At least I think so. (Nothing is selected in add-ins and it's the first workbook I open when opening Excel)

**romperstomper** · 08-19-2010, 03:51 AM

Do you have any Com addins loaded?

**Brunstgnegg** · 08-19-2010, 03:53 AM

Originally Posted by romperstomper

Do you have any Com addins loaded?

How can I tell?

**romperstomper** · 08-19-2010, 04:10 AM

Run this code:

Please Login or Register  to view this content.

**Brunstgnegg** · 08-19-2010, 04:18 AM

I get Run-Time Error '1004';

Method 'COMAddIns' of object '_Application' failed

on the line

For Each objCOMAddin In Application.COMAddIns

Before it crashes it generates the following lines:

XLA AddIns
Name Installed? File name
ANALYS32.XLL FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\ANALYSIS\ANALYS32.XLL
ATPVBAEN.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\ANALYSIS\ATPVBAEN.XLA
SUMIF.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\SUMIF.XLA
EUROTOOL.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\EUROTOOL.XLA
HTML.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\HTML.XLA
LOOKUP.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\LOOKUP.XLA
SOLVER.XLA FALSE C:\Program Files\Microsoft Office\OFFICE11\LIBRARY\SOLVER\SOLVER.XLA
COM AddIns
Description Connect? File name

**romperstomper** · 08-19-2010, 04:27 AM

What if you run:

Please Login or Register  to view this content.

from the immediate window? Do you get 0, or an error?

**Brunstgnegg** · 08-19-2010, 04:34 AM

Originally Posted by romperstomper

What if you run:

Please Login or Register  to view this content.

from the immediate window? Do you get 0, or an error?

The same error.

**romperstomper** · 08-19-2010, 04:36 AM

Try customising the toolbar to show the COM addins dialog:
View-toolbars-customize..., select Commnads tab, select Tools from left list, and about halfway down right list is COM Addins. Drag it onto a toolbar.
Note: this only lists user-specific add-ins not ones installed for all users, but we'll probably assume you don't have any if the list is blank.

**Brunstgnegg** · 08-19-2010, 04:41 AM

Originally Posted by romperstomper

Try customising the toolbar to show the COM addins dialog:
View-toolbars-customize..., select Commnads tab, select Tools from left list, and about halfway down right list is COM Addins. Drag it onto a toolbar.
Note: this only lists user-specific add-ins not ones installed for all users, but we'll probably assume you don't have any if the list is blank.

The list is blank.

**romperstomper** · 08-19-2010, 04:45 AM

Doesn't sound like anything else is interfering then, so I'd have to assume it's something in the code or a glitch in your system. Can't help you with either, I'm afraid.

**Brunstgnegg** · 08-19-2010, 05:35 AM

Originally Posted by romperstomper

Doesn't sound like anything else is interfering then, so I'd have to assume it's something in the code or a glitch in your system. Can't help you with either, I'm afraid.

Do you have any idea what might be causing it? Like some specific kind of operation that usually causes these kinds of errors?

I changed a few things around and it now gives the error

Run-Time error '-2147417848(80010108):
Method 'Value' of object 'IMdcCombo' failed

When I choose 'end' it crashes as before and if I choose 'debug' it crashes when I try to look at the userform where the error occurs.

**romperstomper** · 08-19-2010, 05:40 AM

None at all. You're talking about an occasional crash with no specific error message. Could be anything and doesn't even have to be related to your code. It's impossible to troubleshoot that with nothing to work on.

**Brunstgnegg** · 08-20-2010, 03:00 AM

Ok I have an update and hopefully you guys will be able to think of something with a little more information. The form I'm loading in the sub is, as I said, extremely big. For this reason I've split the sub up into different subsubs (

) so the sub itself looks like this:

Please Login or Register  to view this content.

Since I couldn't add msgboxes or breaks to the code and it only crashed on startup I tried commenting out different subs to see when the error occured. That's when it got weird, it occured regardless of which code was actually run. I thought that maybe it was my computer settings so I tried running it at another computer but had the same error. So then I tried removing some of the tabs entirely and then commenting out the code related to those tabs and that made it stop crashing even though the code I actually do run is the same code that crashed it before. My idea is that maybe Excel can't handle the amount of tabs and controls in my form?

Any ideas?

Btw I have 622 controls and I access the controls by simply using the name i.e. ComboBox1.Value. Do you think it might help to access them by Me.Controls.Item("ComboBox1").Value instead?

**romperstomper** · 08-20-2010, 03:32 AM

I'm not aware of any limit on the number of controls per se but 622 sounds like bad UI to me, to be honest.
Have you run a code cleaner on your workbook?

**Brunstgnegg** · 08-20-2010, 03:37 AM

Originally Posted by romperstomper

I'm not aware of any limit on the number of controls per se but 622 sounds like bad UI to me, to be honest.
Have you run a code cleaner on your workbook?

No I havn't, could you recommend any?

**romperstomper** · 08-20-2010, 04:01 AM

I use this one by Rob Bovey.

**Brunstgnegg** · 08-20-2010, 04:33 AM

Originally Posted by romperstomper

I use this one by Rob Bovey.

Can't run it, I'm sitting at my computer at work. (And now that I think about it I'm actually not allowed to install any software). Any other suggestions? Do you have any guesses as to what might be causing the problem? I'm sorry for being so annoying but my deadline is today and everything else works fine except this. I've been trying to fix this bug for days now and I'm getting kind of nervous :/.

**romperstomper** · 08-20-2010, 04:41 AM

You can achieve much the same thing manually by removing each form and module from your workbook, choosing Yes when asked if you want to export, then re-import them all from the exported files.

**Brunstgnegg** · 08-20-2010, 04:54 AM

Originally Posted by romperstomper

You can achieve much the same thing manually by removing each form and module from your workbook, choosing Yes when asked if you want to export, then re-import them all from the exported files.

Re-import them where? I can't install the software... Sorry for being dumb

**romperstomper** · 08-20-2010, 05:02 AM

From wherever you saved them. The export is part of the VBIDE - it does not require any other software.
Right-click a form or module, choose Remove, then follow the prompts to export. When done, bring them all back via the File-Import File... menu.

**Brunstgnegg** · 08-20-2010, 05:27 AM

Originally Posted by romperstomper

From wherever you saved them. The export is part of the VBIDE - it does not require any other software.
Right-click a form or module, choose Remove, then follow the prompts to export. When done, bring them all back via the File-Import File... menu.

If I remove the form and import it again it doesn't crash. But if I then save, close excel and reopen it it crashes again.

**romperstomper** · 08-20-2010, 05:50 AM

How is the form launched?

**Brunstgnegg** · 08-20-2010, 06:03 AM

Originally Posted by romperstomper

How is the form launched?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

etc.

I tried moving the loading of the form to the Activate Event instead but it made no difference. I also tried moving it to the Activate Event and then add code to the startup of the workbook to initialize the form and then close it again but that made no difference either.

**romperstomper** · 08-20-2010, 06:30 AM

Never use

Please Login or Register  to view this content.

on its own in code.

**Brunstgnegg** · 08-20-2010, 06:45 AM

Originally Posted by romperstomper

Never use

Please Login or Register  to view this content.

on its own in code.

I actually changed it to exit sub after I posted. But I don't think that has any effect on the bug.

I'll be perfectly honest with you, I've given up. I honestly believe that it is a bug in Excel from the use of too many form controls. Because it doesn't matter what part of the code I run it still crashes but if I remove controls and their corresponding code it doesn't matter what code I run either as it stops crashing. I'm not claiming that my code is perfect, I know it isn't. But I really think this is beyond my skills/knowledge to fix and I don't expect someone on this forum to take the time to find the cause of a bug that I have spent days trying to find.

I have managed to add a workaround though. I have NO idea why this works but it does. I added this to the WorkBook_Open event:

Please Login or Register  to view this content.

I got the idea because I noticed that everytime I ran a sub which copies worksheets before I loaded the form it never crashed.

Regardless, I want to thank you for taking the time to help me!

PS. Should I add a SOLVED prefix to this thread?

**romperstomper** · 08-20-2010, 06:55 AM

Yes, please. I don't think it could be truly solved without seeing the workbook.

Excel sometimes crashes when running a sub routine

LinkBack

Thread Tools

Rate This Thread

Display

Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Re: Excel sometimes crashes when running a sub routine

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions