none
Excel Crash with Large VBA Project When Password Protected and Saved in .xlsm Format RRS feed

  • Question

  • Hi,

    I have a large VBA application developed in Excel 2013, and delivered in a single workbook. Each user has a copy of the workbook to store their data. The application serialises its data to a number of worksheets on close, before saving.

    When the VBA project is password protected, and the workbook is saved in .xls format, everything works as expected; save, close and reopen of Excel all work correctly and the user's data is maintained.

    When the VBA project is not password protected, and the Workbook is saved in .xlsm format, everything works as expected; save, close and reopen of Excel all work correctly and the user's data is maintained.

    When the VBA project is password protected, and the Workbook is saved in .xlsm format, the workbook will save and close, however the next time the workbook is opened, it will crash Excel ("Microsoft Excel has stopped working").

    Can anyone offer an explanation, or work around for this specific issue related to saving an .xlsm workbook with a password protected VBA project?

    Thanks in advance,

    Jon

    Monday, July 17, 2017 3:43 PM

All replies

  • Hi Jon Farman,

    you had mentioned that,"When the VBA project is password protected, and the workbook is saved in .xls format, everything works as expected; save, close and reopen of Excel all work correctly and the user's data is maintained."

    do you mean you had store the VBA code in .XLS file? you can't store VBA code in .XLS file.

    other thing , are you running any code when you open the workbook?

    if yes then for testing purpose, try to comment that code and make a test.

    it is possible that your code causing some issue.

    I also suggest you to make a test on other machine which have Office 2013 installed.

    if any updates are available regarding Office then try to install the latest updates and try to make a test.

    it is better if you inform us the version of Excel you are using currently.

    we will try to make a test on our side.

    on my side, I try create a new workbook. I protected it with password then I save it as .xlsm file.

    then I close it and reopen it.

    it opened successfully without getting crashed.

    below version of Excel I am using to make a test.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 18, 2017 5:25 AM
    Moderator
  • Hi,

    Yes, in all cases described in my original post, the VBA code is stored in the Workbook. This is possible in '.xls (97-2003) Workbook' format, and '.xlsm Macro-Enabled Workbook' format.

    Yes, code runs in ThisWorkbook.Workbook_Open(). If I comment out that code, then Excel opens the (password-protected VBA project, .xlsm format) workbook without crashing. It will then crash when any code runs; either when I click on a cell (triggering ThisWorkbook.Workbook_SheetSelectionChange()), or when I click on a button which will call my initialise routine.

    The version I am using is "Microsoft Excel 2013 (15.0.4937.1000) MSO (15.0.4937.1000) 32-bit Part of Microsoft Office Professional Plus 2013".

    I have created a small test file on my system with a single code module and couple of worksheets with content, but it operates correctly (i.e. doesn't crash Excel). The issue appears somewhere in the scale of the workbook content.

    Thanks,

    Jon

    Tuesday, July 18, 2017 2:42 PM
  • Hi Jon Farman,

    from your description, it looks like it is crashing because of your huge amount of data.

    but I am not sure, because I don't know what's going there. what data you are having in the sheet and what code you are running.

    you had mentioned that your workbook format is 97-2003 format.

    try to update your format with latest one and try to test the issue.

    it is possible that old format not able to handle process on huge amount of data.

    also if possible then try to post your workbook here with dummy data in it.

    we will try to make a test on our side and check how can we solve the solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 19, 2017 3:22 AM
    Moderator
  • Deepak,

    When saved in .xls 97-2003 Workbook format, with the VBA project password protected, everything works as expected.

    When saved in .xlsm Macro-Enabled Workbook, with the VBA project not password protected, everything works as expected.

    When saved in .xlsm Macro-Enabled Workbook, with the VBA project password protected, Excel crashes.

    The change which introduces the instability to Excel is adding password protection to the VBA project of an .xlsm Macro-Enabled Workbook.

    Unfortunately I won't be able to provide an example of the workbook and VBA project. I can see if I can create a dummy myself which experiences the same issue.

    Thanks,

    Jon

    Wednesday, July 19, 2017 2:07 PM
  • Hi Jon Farman,

    you had mentioned that,"The change which introduces the instability to Excel is adding password protection to the VBA project of an .xlsm Macro-Enabled Workbook."

    did you try to pass password in your code to execute your code?

    it is possible that when you try to open the workbook at the same time code get executed but as it is a password protected workbook Excel don't allow it to execute and process any code and error get occurred.

    you can try to make a test with it.

    first you can try to open normally.

    then you can try to open the workbook using code without passing the password and then try to open the workbook from code with passing the password.

    also check whether you have any protection on worksheet or not.

    If you have any protection on worksheet then try to unlock it in code then try to execute code.

    also try to make a test with latest xlsm file created in Office 2013 to check whether it makes any difference or not.

    if you are able to reproduce the issue in your dummy workbook then you can share it with us so that  we can get exact idea about your code and issue and we can try to find a solution for it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 20, 2017 2:25 AM
    Moderator
  • Deepak,

    If I open the password protected .xlsm workbook with events disabled, unprotect the VBA project, and then run the code within, Excel crashes in the same way.

    There are password protected (UI only, password = vbNullString) worksheets in the workbook. I couldn't unlock these with code, as Excel crashes almost immediately as any code executes.

    In all cases, I have generated a new workbook and imported all VBA modules, classes and useforms; so the workbook is a brand new Office 2013 xlsm workbook.

    Thanks,

    Jon

    Monday, July 24, 2017 9:19 AM
  • Hi Jon Farman,

    we are not able to find the issue because we don't know what exact code you are trying to execute which makes excel crash.

    with the description only we can just assume the situation and try to provide you the suggestion based on that.

    but it is possible that actual issue is something else and this way the suggestion is not helpful to you to solve the issue.

    so it is better you provide some code to reproduce the issue so that we can make some test on it.

    so if we find the issue then we can try to solve it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 25, 2017 5:28 AM
    Moderator
  • I have the exact same issue. The file contains several xlveryhidden sheets and the book is password protected as well.

    There is just one sheet visible with a single command button on it.

    I started having the issue described where when first opening the file and clicking the command button, Excel would crash as described by Jon. It would not crush if I would first open VB editor and close it again and click on the command button only after completing this. As such I decided to execute a small code on workbook open that would show the VBE and hide it right afterwards:     Application.VBE.MainWindow.Visible = True then Application.VBE.MainWindow.Visible = False.

    This worked fine until I locked the VBA code for viewing with a passcode. I checked with msgboxes that the command to show and hide VBE runs, but as soon as a click on the command button, Excel crashes. If I remove the password protection on the VBA code, everything runs fine.

    Any help?? Marco D.

    Wednesday, March 28, 2018 4:03 PM
  • Hi Marco_D_D,

    You had mentioned that,"This worked fine until I locked the VBA code for viewing with a passcode. I checked with msgboxes that the command to show and hide VBE runs, but as soon as a click on the command button, Excel crashes. If I remove the password protection on the VBA code, everything runs fine."

    Looks like your code is trying to access or modify the data on sheets which are password protected.

    As Sheets are password protected , Code is not able to fetch the data cause the Excel to crash.

    I suggest you to programmatically unlock the sheet before accessing the data from it can solve your issue.

    after the code execution process get finished, you can again programmatically apply the password to protect it.

    Reference:

    Worksheet.Protect Method (Excel)

    Worksheet.Unprotect Method (Excel)

    Let me know if issue persist, try to provide a sample code. We will try to make a test with it to check the issue.

    Further, You can notice that this thread is very old and inactive.

    So on this thread, It is less chance to get suggestions from community members.

    For better response, You can try to create a new thread with detailed information regarding your issue.

    So that you can get much more suggestions from community members and MVP's.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 29, 2018 1:22 AM
    Moderator
  • Dear Deepak,

    the issue is not with password protected sheets. I am very familiar with the programmatical password protection of worksheets and workbooks.

    The issue I am facing is with the protection of the VBA project itself, the one that locks the project for viewing in VBE.

    Whenever I save the file without the password protection of the VBA project, I can open the file and run the macros without any issue.

    When I save the file with the password protection for viewing the VBA code, I can open the file, but as soon as I try to run a macro, Excel crashes! However, I noticed something weird. If I save the file with VBA code password protection (as before) and I open the file, I open the VBE and enter the VBA password and run the macros only then, then the file does not crash.. It is as if the macros can run with the "locked" VBA project.

    Can you provide help?

    Thanks.

    Marco

    Thursday, April 12, 2018 12:04 PM
  • Hi Marco_D_D,

    You had mentioned that,"When I save the file with the password protection for viewing the VBA code, I can open the file, but as soon as I try to run a macro, Excel crashes!"

    I try to do the same on multiple versions of Office.

    Based on my testing, I found that Excel not get crashed, When I try to run a VBA code.

    When I try to run the code, At that time it looks like code is executing but as sheet is protected there will be no effect of code execution on the sheet.

    I suggest you to repair your Office suite from control panel and install the latest update and again try to reproduce the issue with newly created Workbook.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 13, 2018 1:23 AM
    Moderator
  • The problem the user has is unrelated to anything other than an ongoing problem with Excel. I have had tis issue since 2013 through 2018 with multiple computers, versions of Excel since 2010, Once the VBA code reaches a certain size it is simply impossible to protect your project. My current project is over 18,000 lines and growing and I have just had to accept that it cannot be protected!!
    • Proposed as answer by Emilio_A Thursday, October 4, 2018 8:44 PM
    • Unproposed as answer by Emilio_A Thursday, October 4, 2018 8:44 PM
    Friday, April 20, 2018 4:41 AM
  • Hi absolute.ivor,

    I can see that issue can only occurred when your project size reached certain size.

    Currently, I am not available with this much big size of project to make a test.

    I suggest you to submit your feedback to Office developers via Outlook Insider.

    Outlook Insider has this feature to directly submit the issue to Office developers.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 7:06 AM
    Moderator
  • absolute.ivor, Marco_D_D,

    I doubt the issue is due to the project size, at least not directly. Password protection does not encrypt the project, and there are many protected projects considerably larger than 18,000 lines.

    Couple more suggestions (only guesses)

    If the password includes any non 32-127 ASCII characters try something simple like "abc".

    Rebuild the project. One way - save a copy of the file as an xlsx. Drag all normal, class & form modules from the xlam into the xlsx. Copy any code behind object modules (sheet & thisworkbook). Add any non standard references if/as required. Debug/compile. Add the password,  Saveas an xlsm/xlam, close & re-open, ?

    Friday, April 27, 2018 12:54 PM
    Moderator
  • Hi everyone,

    I am new in this community

    I am looking for a solution to this issue too.

    In the past, i never have a problem with excel crash, and some projects have hundreds of code lines.

    I recently experience same behavior in an Excel VBA project, just like Jon, Marco_D_D and absolute.ivor said. And I tried all tips Deepak an Peter gave, and problem persist.

    I try something that is like a way that I can work with the project without unprotect the code:
    When open the excel workbook, first click the "Save file" button, and the issue not appear.

    Althought is kind of bothering to the user, have to save the file once he open, is a way you can try to use the file without unprotect the code.

    But I prefer a real solution.
    Did you find a solution or an Idea to solve it?

    Please help.

    Thanks in advance
    Emilio


    • Proposed as answer by Emilio_A Thursday, October 4, 2018 9:44 PM
    • Unproposed as answer by Emilio_A Thursday, October 4, 2018 9:44 PM
    Thursday, October 4, 2018 9:32 PM
  • Hi again.

    I just try to put code to save the workbook in the Workbook_Open event, and it works for me, at least in the first test i did.


    • Proposed as answer by Emilio_A Thursday, October 4, 2018 9:49 PM
    Thursday, October 4, 2018 9:49 PM