VBA causes Excel to crash when re-opened after it has been saved

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/

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.

Yes !!! you found the right solution... 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!!!

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.

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

Happy coding!

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

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

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!

Couldnt be more grateful,
Mike

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.


All the best,
Henry

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

Best,
AdLoki

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

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

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

I don't know how to paste the workbook

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

AdLoki

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.

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

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

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

THANK YOU SO MUCH MR. AdLoki!!!!


You're the best, by far!

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

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

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

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!

it's all right adloki

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

thanks for the advice!

Glad you fixed your problem! Hope it works out

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

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

thanks Ad!

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

Glad to be of assistance Miller!

I cannot run your magic code
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!

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

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!

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 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 That's why I ended up posting the solution here, to hopefully help other people

Thanks a lot!
This solution worked for me also!

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

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

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.

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

Originally Posted by Nickleon

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.

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.

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.

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!