locked
Issues with VBA needing to be recompiled? RRS feed

  • Question

  • I have a .xlsm file that when I open it and hit enable content - Excel crashes immediately. If I take that same file open it, but instead of enabling content, open up VBA and recompile the code then save and close. If I open the file again and enable the content - everything is fine. I did nothing but recompile the code and save the workbook.

    I wanted to know what was the difference was between the non-working version and the working version. So I restored the previous version of the file and saved it as a copy. I used the Excel 2013 spreadsheet compare tool, to compare the working and non-working versions of that file. There was only one thing that was different. (Here is a link to the results exported as a Excel file, incase you can't see the picture.)

    Difference - VBA Module Changed

    Looking around online, it looks like this is the VBA Header. I'm not a huge expert on VBA code so I don't really know what that mumbo jumbo is saying that changed.

    How can I go about finding/troubleshooting this issue were I need to recompile to use the workbook? Why did the VBA code need to be recompiled for the workbook to function again? What really changed?

    I'm using Office 2013 64-bit, and update regularly. Other people use this file with Office 2010 64-bit and Office 2010 32-bit. Could there be issues when they save the file, then I try and open it? We are all using Win 7 x64 and Win 8.1 x64. Any help would be greatly appreciated.

    Thursday, April 2, 2015 4:32 PM

All replies

  • Hi dvsh88,

    >> I did nothing but recompile the code and save the workbook.

    What do you mean by "recompile the code". I was confused by the concept recompile, VBA is not a compiling language, and I do not think we can really recompile the VBA code.

    >> I'm using Office 2013 64-bit, and update regularly. Other people use this file with Office 2010 64-bit and Office 2010 32-bit.

    As far as I know, in previous versions of VBA, there was no specific pointer data type so the Long data type was used. And because the Long data type is always 32-bits, this breaks when used on a system with 64-bit memory because the upper 32-bits may be truncated or may overwrite other memory addresses. Either of these situations can result in unpredictable behavior or system crashes. More information you could refer the link below:

    # Compatibility Between the 32-bit and 64-bit Versions of Office 2010

    https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx

    >> Could there be issues when they save the file, then I try and open it?

    According to the currently information, we are not able to identify the root cause of Excel crash. A sample macro-enabled spreadsheet to reproduce this issue will be appreciated.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, April 3, 2015 9:58 AM
  • >> What do you mean by "recompile the code". I was confused by the concept recompile, VBA is not a compiling language, and I do not think we can really recompile the VBA code.

    Under Debug, it gives you the option to "Compile VBAProject." I don't really understand it either. 

     

    I'll look at getting a sample. I'll take a closer look at that article. I know our files use ActiveX.

    Friday, April 3, 2015 4:07 PM
  • Looking at the article mentioned - it states, "Native 64-bit processes in Office 2010 cannot load 32-bit binaries." How do I know if I'm tring to load a 32-bit binary? Is there some sort of test? Thanks for any help.

    https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx

    Friday, April 3, 2015 4:17 PM
  • Additional Information

    Doing some testing with a "sample file." Under Trust Center settings I have "Disable all macros with notification" set for all versions of Office.

    Office 2013 64-bit:

    If I open the file and hit Enable Content, Excel crashes immediately. 

    If I open the file, then go to VBA project and hit save and close the file. Then when I reopen the file and hit Enable Content, the file works.

    Office 2013 32-bit:

    If I open the file and hit Enable Content, the file works fine.

    If I open the file, then go to VBA project and hit save and close the file. Then open the file with Office 2013 64-bit and hit Enable Content, the file works fine.

    Office 2010 64-bit:

    If I open the file and hit Enable Content, the file works fine.

    If I open the file, then got to VBA project and hit save and close the file. Then open the file with Office 2013 64-bit and hit Enable Content, the file works fine.

    It looks like this is just an Excel 2013 64-bit issue.

    I'm working on getting a sample file. It's difficult because, I can't resave the file to remove information. As soon as I hit save, the issues disappear. I can't give you the file as is, because it has "sensitive" information in it.

    Friday, April 3, 2015 5:06 PM
  • You may have some macro set to run when the Workbook opens.

    To get round this, hold down the shift key just before(and during) when you Open the Workbook. This will stop the AutoOpen macro from running, and may let you save the workbook or check it for errors on your 64bit Excel 2013.

    Saturday, April 4, 2015 3:32 AM
  • That's what I thought as well. But looking through the "ThisWorkbook" subs like "Workbook_Open" it's completely blank. It's almost like when Excel goes to load the vbaproject.bin file in the xlsm, things to crash. Is there a way to tell if that bin file is 32 or 64-bit file? Maybe I'll break out some SysInternals and see if they can see what's happening. There's no way to put Excel in debug mode and step through the process of opening a file, is there? 
    Monday, April 6, 2015 5:06 PM
  • What is that ActiveX and where is it, in respective systems.  aX libraries are 32 bit and will not load in Office 64, apart from the built-in aX controls. They work OS64 with Office32, but should be installed and registered in the SysWOW64 folder.

    Depending on what your code does there can be reasons why it will fail in 2013, mainly due to the change from MDI to SDI.

    But you say all is OK in 2010-64 and 2013-32, only 2013-64 is the problem. Strange, are 2010-64 and 2013-64 in different systems, and if so anything different about that that aX and where it's located?

    Monday, April 6, 2015 7:40 PM
  • The 2010-64 and 2013-32 are different machines. The 2010-64 is a virtual machine running Win7-64 Enterprise. Everything is patched. The 2013-32 is a physical machine running Win7-32 Enterprise. Everything is patched. Maybe I should try testing on 2013-32 running Win7-64?

    The "bad" Excel file doesn't work on 2013-64 running on Win7x64 or Win8.1x64 with everything patched.

    The ActiveX that's being used are just some textboxes. 

    What are MDI and SDI that you're referring to?

    Thank you.

    Monday, April 6, 2015 9:21 PM
  • >Maybe I should try testing on 2013-32 running Win7-64?

    Why not, not that I'd expect any difference. However if tested in the same OS it would suggest the issue is specific to 2013-64, and not the system. Ensure the respective files are identical when starting both tests.

    >The ActiveX that's being used are just some textboxes. 

    You mean normal built-in MSForms textboxes in a userform and/or on a worksheet applied applied via the Developer tab?

    >What are MDI and SDI that you're referring to?

    Programming for the Single Document Interface in Excel 2013

    Although relevant code may error or fail to work as expected, I wouldn't expect anything related to cause Excel to crash as you described if no code was being executed, say in an open routine.

    Next step worth trying would be to rebuild the project from scratch, starting with the file with the project removed, ie as an xlsx. If going ahead with that don't export and import modules, paste the old code into similarly named modules as text (ie don't import the headers). Saveas a backup xlsm without compiling, then do debug/compile and go from there.

    I meant to ask last time, what module are the headers in your screenshot from, presumably a sheet or userform?

    Edit: If you've got a userform, in the original file export the userform, a pair of *.frm & *.frx files will be created, code in the frm and controls data in the frx.
    Rename the frm as say frm.txt.
    In the new file and setup, create a dummy userform with the same module name as the original and export it.
    Remove the dummy form.
    Discard the dummy frx,
    Name & pair the dummy empty (except for headers) frm with the original frx (if you had named the userform won't need to rename the frm).
    Import the now paired frm, this should import the form with the original controls but no code.
    Paste original code into the new form module.

    Point of all this is to ensure there's no corruption in the project, including the headers.

    Monday, April 6, 2015 10:18 PM