# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] VBA causes Excel to crash when re-opened after it has been saved

## AdLoki

UPDATE: I have completely resolved this problem - see reply below!

Hi All.

This issue is driving me nuts. I know that there is an instability in Office 2010 that is the cause of this and that similar problems have been reported by a number of users on different fora, but I am still trying to find a workaround that works for me. Please bear with me while I describe the issue:

*The Situation*
I have a large project built in Excel - it is a complex statistical model with a userform GUI. It consists of 81 worksheets, 6 userforms and 10 VBA modules. The overall size of the .xlsm file is <4Mb. The VBA files are password-protected in VBEditor.

Of the VBA files, the largest BAS is 36Kb and the largest FRX is 141Kb. The largest userform contains 506 controls. Everything compiles fine.

The workbook is arranged such that only one sheet is visible, containing a launch button for the tool's GUI, which automatically initializes when the work book opens via the *Workbook_Open* procedure in the ThisWorkbook module.

*The Problem*
Sometimes Excel will crash when opening the workbook ("Microsoft Excel has stopped working..."). Through many hours of trial and error I have confirmed that: 
1) it is the initialization of the main userform that is triggering Excel to crash - removing the *Workbook_Open* procedure stops Excel from crashing on opening but it still crashes as soon as the userform is launched
2) opening the workbook in Protected View doesn't stop it from crashing once "Enable editing" has been clicked
3) Open and Repair _sometimes_ resolves the problem, until the next time it is saved
4) if I am able to get the VBA files open and make a save _from within VBEditor_ sometimes it can then be closed and reopened successfully - but moving the workbook to a new directory or saving it the brings the problem back
5) saving the workbook via VBA *ThisWorkbook.Save* also causes Excel to crash next time the workbook is re-opened
5) disabling password protection within VBEditor seems to resolve the problem, but this tool is being published and has to be protected so disabling it permanently is not an option.

*Potential Solutions*
Several things have been suggested in other post; I have tried the following, with no success:
1) CodeCleaner
2) I was already using Option Explicit
3) changing all object references from text handles such as *Userform1.CommandButton1.Visible* to index references like *Userform1.Controls.Items(1).Visible*

When I allow Visual basic to debug Excel, it produces the error _Unhandled exception at 0x73C1C9F1 in EXCEL.EXE: 0xC0000005: Access violation executing location 0x00000000._ at the line:



```
Please Login or Register  to view this content.
```


*Any Ideas?*
I have been struggling with this for weeks. The project is a national public sector tool and I really need to get it sorted. The only remaining issue that I can see is the size of the main userform - 141Kb and 506 controls. I appreciate that there is a 64Kb maximum limit for modules (which still amazes me in the era of modern computing) but I can't see an eloquent way of splitting the form into two smaller packets. I have tried stripping all procedures in the userform code down to simply calling routines in other modules but this didn't considerably reduce the size of the FRX file, which I assume is a result of the number of controls.

Does anyone have any suggestions? I cannot provide an unlocked copy of the tool but I can provide snippets of code if that is likely to help.

Many thanks,
AdLoki

----------


## AdLoki

I have not managed to identify the specific cause of the problem but there were a few things that I changed which seemed to have resolved it. Roy MacLean's suggestion on the following page was very helpful in allowing me to identify and edit problems that weren't being flagged up by the compiler. 

I would recommend doing this if you are encountering problems with UserForms that launch on Workbook_Open: http://roymacleanvba.wordpress.com/2...event-handler/

----------


## arlu1201

If your issue has been resolved, please mark your thread as solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

----------


## AdLoki

*RESOLUTION:* 

I know that many people have been experiencing the same problem and I believe that I have discovered a comprehensive solution. It seems that when you have a userform with more than 500 controls, Excel 2010 experiences problems communicating with those controls if they are referenced in the normal way, i.e. [UserForm1.ControlName.Parameter].

I originally thought that using numeric index referencing would solve the problem - i.e. [UserForm1.Controls.Item(1).Parameter] - but this didn't stop Excel from crashing sporadically and also raised the problem that deleting a control causes the indexes of all subsequent controls to change in line with the missing index.

But it transpires that if you follow the same syntax but use, instead, the control's name, the crashing stops - i.e. the format [UserForm1.Controls.Item("ControlName").Parameter]

I have written some code to automatically update all control references in VB project modules. If you want to use this, simply paste the following code into a new module in your project and run the relevant macro ("CleanControlNames"):




```
Please Login or Register  to view this content.
```

----------


## OrangeShelby

Yes !!! you found the right solution... :Smilie:  My application started doing that after the customer requested some changes... 
I used your code to update the code in the application but noticed that il will not go thru all the modules... but anyway... I made the changes manually... Tried it after that about 20 times without problems!!!

Again, thanks a lot!!!

----------


## unicco

AdLoki thanks alot for this solution.

I've been working months on a worksheet for a customer - and have never experienced an issue in this matter. Every time I wanted to run a procedure, excel freezes and returns a vapid errormessage: "Excel has stopped working". I've got such a headache since this error, and had literally no clue what so ever, how to solve it. Google searches returned millions of solutions regarding add-ins etc.

I wasn't aware of Excel documents' userform had these limitations regarding controls - so first of all thanks for the solution, and the revelation on that matter as well. 

Working like a charm.

----------


## AdLoki

Glad to be of assistance! To be fair, this took up about 100hrs of my time last year and was driving me crazy -- I now routinely reference all controls in that format and haven't had any similar problems since  :Smilie: 

Happy coding!

----------


## basavg@gmail.com

Thanks for posting such a grate stuff..was almost pulling my hair on this.. you just have saved my hair and time too  :EEK!: 
Thanks again.

----------


## dabaol

This is really a great finding! I'm so lucky that the first article I clicked during Google search is the solution.
Many thanks, AdLoki!
I'll save this article and share with more people.

Cheers,
David

----------


## mlj61289

Adloki - 

I'd just like to add to the praise that everyone is giving this post. I have a UserFrom with some 864 controls and I have been going mad for the past two weeks, exhausting every place I know to look for help! Not only did you have the right answer, your macro even saved me the effort of manually re-wording each control. I will also now use this format to reference all future controls regardless of how many I've got. Thank you! :Smilie: 

Couldnt be more grateful,
Mike

----------


## AdLoki

Glad it helped, Mike (and everyone else) - I totally feel your pain at exhausting all options to resolve bugs in VBA, it can really be a nightmare sometimes! 

As I have mentioned previously, I now routinely use this syntax for all VBA coding, even in small projects. It was something of a ball ache to start with but it has become second nature and I now advise it as standard when training others.

I'm also glad that the code worked for you - it has been a little inconsistent for other users, I should probably take the time to re-code it and re-post this as an advice thread; hopefully that will mean that coders with the same problem won't have to spend so long battling to find a solution in the future.

All the best and thanks for the feedback,
AdLoki

----------


## HenryOak

Thanks AdLoki for your solution!

I was already banging my head against the table (quite literally) because of this problem. Copied your code and it works like a charm. 
 :Smilie: 

All the best, 
Henry

----------


## EliasOz

It still doesn't work for me I don't know what to do. It's like a  :Frown:  problem I run the "magic" code but I get the same problem:
"Excel has stop working" I made my code in excel 2007. but i still getting the problem, y have a form with multipage with a bunch of many controls, like more than 100,
It seems like I have to many code in my form i don't know how to divide it.
any help?

----------


## AdLoki

Hi Elias,

Not entirely sure that I understand where you are encountering the problem but I know that not everyone has been able to use the code I wrote. If you send me your workbook I can take a look and see if there is something I can do to help  :Smilie: 

Best,
AdLoki

----------


## EliasOz

Adloki:
Thanks for the quick reply, I didnt mean to offend, do you, have a mail or something where I can add my file?
sorry for asking,  I'm new in this forum :Confused:

----------


## AdLoki

No offence!  :Smilie:  I have accepted your friend request, feel free to PM me your workbook and I'll take a look  :Smilie:

----------


## EliasOz

hello adloki
as you can see is really long for just one form. can you please help me to understand how can I divide the code, and work....
I really appreciate any help you can provide

----------


## EliasOz

I don't know how to paste the workbook :Frown:

----------


## AdLoki

Hi Elias,

Click the "Go Advanced" button at the bottom of the page, then click the paperclip button to attach your file to your message.

Alternatively, send it to me in a private message  :Smilie: 

AdLoki

----------


## EliasOz

Thanks for your patience my friend...
Is in spanish, Im from Paraguay, this is a form I use to put data from a survey,
as you can appreciate is too long, so if you can give me a hand or two, in  dividing the code from the form it will be great.
It's the first time I'm using multipage and forms this long. 
So i will really apreciate any help you can give about use of clases and modules.

----------


## AdLoki

No problem my friend, I will look at this tomorrow and have something back to you by lunch time in Asunción  :Wink:

----------


## EliasOz

jajjajajajaj thanks man... you're more than awesome

----------


## AdLoki

Hey Buddy,

See attached, I think the problem is resolved.

It is the same problem as outlined in the original post: all references to Userform controls must take the format 



```
Please Login or Register  to view this content.
```


In your code, some references were formatted in this way, others were not. Because of the different referencing styles used, it wasn't possible to use a piece of automated code to fix the problem, so I went through and made the adjustments manually.

Note that I recommend it as good practice to write all references in the above style to avoid this problem - it has proved a good habit to get into in my experience. It will also make your code more elegant and quicker to write if you make more use of "With-End With" statements. For instance, I replaced Subroutines like this:



```
Please Login or Register  to view this content.
```


with



```
Please Login or Register  to view this content.
```


Finally, I also updated your cell references using "With-End With" statements to avoid the need to select your worksheets every time you write to your file. This should make it more efficient; it will also mean the tool will work even if you have the worksheets hidden from view.

I hope that solves your problem - remember, stick to this style of control referencing and you should avoid these problems in the future.

All the best!
AdLoki

----------


## EliasOz

THANK YOU SO MUCH MR. AdLoki!!!! :Smilie: 


You're the best, by far!

hope to hear (or read) from you in the future.

----------


## EliasOz

[QUOTE=EliasOz;3617916]THANK YOU SO MUCH MR. AdLoki!!!! :Smilie: 

i don't know what is happening i'm hitting the dead line and the file doesn't work
it can't be saved
when I saved the file 
it gives the error
excel has stop working and so on.

don't know what to do...
 :Frown:  :Confused:  :Frown:

----------


## AdLoki

I can only suggest that you go back through all of the code to double check that every reference to userform controls is formatted as I described - I may have missed something. It was working when i tested it but i will look again. Sorry that it didn't work for you!

----------


## EliasOz

it's all right adloki

 i found that it works when you save as 97-2003.

thanks for the advice!

----------


## AdLoki

Glad you fixed your problem! Hope it works out  :Smilie:

----------


## EliasOz

Adloki so sorry to bother you again. 
But I can't find how to put a knew thread in the forum...

Could you please show me how.?

----------


## AdLoki

Click the Forum button at the top left of the page, choose the type of discussion (for example, Excel programming & VBA) and then simply click Start New Thread  :Wink:

----------


## EliasOz

thanks Ad! :Smilie:

----------


## millerdp070

WOW... what a life saver.  My excel 2013 .xlsm template appeared trashed.  9 hours of development work lost, due to the reoccurring file save/open excel crash.  Changed the file name, reopened without enabling VPA content.  Got the macro installed and was able recover the file, which otherwise showed as corrupt.  Thank you AdLoki  :Smilie:

----------


## AdLoki

Glad to be of assistance Miller!  :Smilie:

----------


## robbery525

I cannot run your magic code  :Frown: 
It tells me "Error 1004: Programmatic access to Visual Basic Project is not trusted."

My Userform is not extremely complex, but it still crashes when I use a button to open the form. Sometimes it will open fine, other times it crashes excel, this is both stepping through the initialization and just pressing a button coded "TimeCode.show 0"

I have included the two forms in question (TimeCode is the primary form, with FieldInstalls opens after clicking a button on TimeCode). 

Will reply /edit later thanks in advance!

----------


## AdLoki

Hi Rob, I've had a quick look at your forms and I don't think that your problem is related to the topic of this discussion.

However, if you want to try running the "magic code", you'll need to change your settings in Excel to avoid the 1004 error. In Excel 2007-2013, go to File > Excel Options > Trust Center > Trust Center Settings > Macro Settings > Developer Macro Settings  and tick the option "Trust access to the VBA Project object model".

I couldn't replicate your loading problem but I attach the forms embedded in a blank workbook and the forms seem to load fine: robbery525.xlsm

A quick glance at your code leads me to suggest that you declare your worksheet names when referencing ranges, but without seeing your original workbook, I can't say whether that will help.

Like I say, I don't think the problem is related to this thread but by all means give the code a go to see whether it helps. If you find that the attached workbook _doesn't_ crash Excel, the problem probably relates to something else in your file.

Hope that helps,
AdLoki

----------


## jquintana83

HOLY FREAKING BAGS OF FUNYUNS BATMAN!!!!
How on earth did you manage to even tackle that problem with code of your own?

I can only imagine
Adloki: "Microsoft seems to have made a stupid mistake...not a problem, I'll just sneeze out some code that will make the earth spin backwards!" 

You're code has fixed my problem! I have been struggling mightily for months dissecting every little line that I could think of that would be crashing this Titanic of a workbook I built. I this isn't too forward of me to ask but...Can you cure cancer too? 

Anyways thank you so so so so so very much. Now I can focus more on developing. You have saved me so much more stress! Thank you!

----------


## jaslake

What's a 



> FUNYUN



?????????

----------


## abousetta

Has anyone used CodeCleaner or decompressing/ compressing the vba code using the program by MVP Orlando? I'm not as familiar with the problem as others, but that would be my first mode of attack.

----------


## AdLoki

Jquintana: wow, thank you for the awesome praise! I, too, spent aaages trying to figure this out so I'm glad it continues to help people! We are working on a free toolbar add-on for Excel that will include this code and a bunch of other (hopefully) helpful tools  :Smilie:  we'll post it on our website at some point
Abousetta: yeah, CodeCleaner was one of my first ports of call also but unfortunately it didn't help  :Frown:  That's why I ended up posting the solution here, to hopefully help other people  :Smilie:

----------


## eyal7773

Thanks a lot!
This solution worked for me also!

 :Smilie:

----------


## Parul123

Wow...i am so so so so grateful to you Adloki...i joined the forum today, after seeing this thread. I was actually tearing my hair apart due to this weird behavior of excel and was actually thinking that all my effort at building my first Excel VBA application goes down the drain if this is not resolved!! You are a Saviour!! Thanks a ton!! My application has three userforms and three modules...main form has a multipage and all together about 500 controls in that form! Thank you.......... :Smilie:

----------


## wk138n

Worked beautifully! Thanks Adloki! saved me lots of time and stressing

----------


## insomniac53

Quite a while since this thread began, but I'm wondering if there's any further development? I'm asking because, having tried the control syntax method and failed, I'm lost for ideas what to do next. Situation is: I have a complex Excel 2013 package developed over 6 years, which now crashes on opening, or, if I manage to open it without execution, crashes on compiling. I've included and run the module with AdLoki's code and then gone through the few controls that weren't correctly altered. No luck. I've been through each control and checked the wording. I've also tried to check the log file which Excel says (before crashing out) will "explain the issue" - but it's empty. I tried opening the package without running a form, but it still fails. I can't place the moment these critical failures started so I've no idea, apart from control names, what might cause this? Any suggestions would be really welcome.
Thanks.

----------


## AdLoki

Hi Insomniac,

Difficult to know what your problem could be but try this updated code in the attached zip directory - just import the .bas file to your project and run the relevant sub. The code has been improved to reduce the incidence of false positives and to give you the opportunity to decline changes if desired. In my experience, even if just one uncorrected reference remains it can cause Excel to crash.

If that doesn't work, try posting your file here (or PM it to me) and I can take a look as its not always obvious what causes these (all-too-common) problems...

If that doesn't work, your best bet would be to start a new thread for your particular issue.

All the best,
AdLoki

----------


## insomniac53

Dear Adloki,

Thank you for the update and revised program. In the intervening days, a lot has happened, though I'm not sure if I'm much the wiser. The good news is that my code is compiling again. Why? This is a mystery. First, I ran your previous program to modify the control references then afterwards went through them one by one to check them. I think I amended all of them, though I wouldn't stake my life on it so, as you say, one might have slipped through. No luck. I also asked some specialist companies but they charge exorbitant amounts for unspecified work. From yet another forum, someone suggested replacing the lost Active-X modules, but I didn't know how to do this. I checked the Tools->References and varied the included modules, but nothing worked - the compiler kept crashing - every single time it ran. I also tried gradually removing the latest code and recompiling, but with the endless crashes, it was impossible to pinpoint the precise line where everything falls apart.

Someone from another forum suggested an Appspro Code Cleaner which loads as a COM add-on into Excel. I'm wary of these third party programs, but I had nothing to lose so I tried it. No luck again. However, I contacted the author and he, like you, offered to look at the file. He ran it on a Windows 7 system using Excel 2010, and it compiled perfectly! He sent the compiled program back to me and, magically it seemed, it compiled on my system. How can this be explained? It's beyond me. He made no alteration to the code at all. He said that many of his clients have had similar issues with Windows 8 (or 8.1) running Excel 2013 and said that there seems to be some deep instability between VBA 7.1 and Windows 8, which is a worry. My guess, and it is just a guess, is that a critical flag is set by the code, even if it is clean, and this flag keeps telling Excel to shut down. I know that many people used your program with more luck than me, but this may be because of their Windows/VBA configurations. 

I will download your program and try it on a test file but I am wary of doing anything to the current code now that it is working again, apart from necessary developments. I know this answer is a bit vague, but it's another option for anyone to try if they are pulling their hair out over endless fatal crashes.

All the best.
Ellis

----------


## Ali Atwi

I plan to use your code tomorrow morning. If this works you have no idea how much headache you have saved me. 
Is there anywhere we can donate to to help with your add-on development. 
That being said I ran a quick test from home on some file, and I noticed that it changes the references to start with    me.   instead of userformname. 
should I change the me. manually or not? or should it work just the same?

Update- I just tried it, it worked perfectly!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!, even if i changed all the advise I had before to normal. I wish I was gay right now so that I can date you or something lol. Truly man you have no idea how much hassle this has saved me. I am pasting your thread solution on almost every single forum or question I can find on the matter. You should advertise it, GLOBALLY lol. 

Thanks again, you will be in my prayers.

----------


## Nickleon

> Hi Insomniac,
> 
> Difficult to know what your problem could be but try this updated code in the attached zip directory - just import the .bas file to your project and run the relevant sub. The code has been improved to reduce the incidence of false positives and to give you the opportunity to decline changes if desired. In my experience, even if just one uncorrected reference remains it can cause Excel to crash.
> 
> If that doesn't work, try posting your file here (or PM it to me) and I can take a look as its not always obvious what causes these (all-too-common) problems...
> 
> If that doesn't work, your best bet would be to start a new thread for your particular issue.
> 
> All the best,
> AdLoki



Dear AdLoki, 

thank you so much, you did a great job with your code but it's not perfect because
1. it does not correct references to controls to other forms but only in the same form   ("If wb.VBProject.VBComponents(n).Name = ctlArray(1, i) Then")
for example: if in a form A there is a reference to a control from form B
             B.textbox1.value = me.textbox3.value  
becomes ->   B.textbox1.value = me.Controls.Item("textbox3").value
2. there are some cases the code can cause some problems, if the name of a control is part of the name of another control
for example: if in a form there is a control control with name "infolabel3" and another control with name label3 then
             me.infolabel3.caption = ""  
becomes ->   me.infoControls.Item("label3").caption = ""  
3. If you choose not to prompt for each control then it does not change the code at all even though it loops though all the forms ("If qPrompt = 6 Then"). 
4. In the code the if case "If m < 4 Or (m > 4 And (ctlArray(3, i) = "MultiPage" Or ctlArray(3, i) = "TabStrip")) Then" has a problem because m>4 never happens. I think you meant m=4 then the code becomes "If m < 4 Or (m = 4 And (ctlArray(3, i) = "MultiPage" Or ctlArray(3, i) = "TabStrip")) Then"

The solution is to add another case where the control belongs to another form to correct the first problem and also sort the array of controls by form_name_length and control_name_length (descending) in order to correct the second problem. Sorting (bubble sort when inserting each control to the array) takes some more time but I think it can solve many other problems. I also added the option to make the changes without the need to prompt.

I've attached the new code and I think now your code becomes a bit more "magic" than before.

Thanks again, 
Nick.

----------


## Ali Atwi

> Dear AdLoki, 
> 
> thank you so much, you did a great job with your code but it's not perfect because
> 1. it does not correct references to controls to other forms but only in the same form   ("If wb.VBProject.VBComponents(n).Name = ctlArray(1, i) Then")
> for example: if in a form A there is a reference to a control from form B
>              B.textbox1.value = me.textbox3.value  
> becomes ->   B.textbox1.value = me.Controls.Item("textbox3").value
> 2. there are some cases the code can cause some problems, if the name of a control is part of the name of another control
> for example: if in a form there is a control control with name "infolabel3" and another control with name label3 then
> ...



Dear All-

Important Update. 

In case you perform the code and change all the reference, the problem will still arise in the future. The only way I could try everything without crashing is to change the me. into userformname. 

That is the only way to prevent crashes.

----------


## Nickleon

> Dear All-
> 
> Important Update. 
> 
> In case you perform the code and change all the reference, the problem will still arise in the future. The only way I could try everything without crashing is to change the me. into userformname. 
> 
> That is the only way to prevent crashes.



Mmm... in the code a userform refers to other userforms with "<otheruserformname>." 
If you mean that each userform should refer to it's controls with it's whole name "<userformname>." instead of "me." then that's quite interesting. I haven't encountered a crash like this yet. But I can change the code to add "<userformname>." or replace "me." with "<userformname>." before "Controls.Item(" if that's what you mean.

----------


## Ali Atwi

disregard my last comment. The other crash was due to another error all together. Mainly it was because I was refreshing a pivot table while the data source was filtered. I thought it also had something to do with userforms because the crash was exactly the same. Anyway the code should work perfectly. sorry if i added to the confusion.

----------


## voltik

I had no idea that such naming convention needed to be used for controls.

Thank you so much Nickleon and AdLoki. I've been trying to figure out this for weeks and was going to move away from Excel and rebuild with something else. I initially thought this was some sort of pivot issue because everytime I did anything related to pivot tables either creating pivots or using the data model it would crash either on save or mostly when reopening files after saving. However, since yesterday any other changes besides pivots still caused the crash issue. I will need to test further to see if the pivot/data model issue was resolved or not.

This issue has been very frustrating for the end user and myself. I've lost days worth of work and weeks worth of time. Thank you once again!

----------

